Using DefiningQuery to map multiple associations to foreign key foreign key

Problem: Figure below shows the database diagram for the relationship between Contact and their addresses.

Although in the table relationship, a contact can have many addresses and each address is either a billing or shipping identified by AddressType column.

On the entity data model, you want to make sure that a Contact cannot have more than two addresses and there could be only 1 billing and 1 shipping address. To ensure the above scenario, you want to expose two entity references from Contact, a BillingAddress and ShippingAddress. The end entity data model should look as follows

Solution: To accomplish the above solution, we have to create a

DefiningQuery and instead of exposing ContactId we need to expose to two columns BillingContactId and ShippingContactId where BillingContactId would not be null when AddressType is B and ShippingContactId won’t be null when AddressType is S. The reason we have to expose two columns is because entity framework does not allow mapping multiple associations to a foreign key column as it would invalidate the model and cause data loss. Map two associations between Contact and Address and map billingAddress

association to BilingContactId column and map the ShippingAddress association to ShippingContactId column.

Discussion: To create multiple associations between Contact and Address entity, we need to create DefiningQuery for AddressEntity. In the sql query we need to expose ContactId foreign key column multiple times, first as BillingContactId and second as ShippingContactId. When AddressType is billing, then BillingContactId should be the ContactId and when AddressType is Shipping, ShippingContactId should be the ContactId.

Steps below outline the process of exposing two entity reference

BillingAddress and ShippingAddress for a relationship that is defined as 1 to Many in the database.

1. Import Contact table using EDM wizard.

2. Create Address entity on the designer. Add AddressId(Int32) and FullAddress(string) scalar properties to Address entity. Make AddressId as entity key.

3. Create association between Contact and Address where Contact has multiplicity of 1 and Address has a multiplicity of 0-1. The reason Address will have a multiplicity of 0-1 is because a Contact may only have zero or more addresses. Call this association as BillingAddress.

Figure below shows the association values.

4. Create a similar association like in step 3 but call it ShippingAddress.

Figure below shows the association values for ShippingAssociation.

5. Since we have not configured how Addresses entityset, will gets its data from database, we need to define DefiningQuery and create a similar entity Address on the store model. Since neither options are supported by the designer, open the edmx file in xml format and create the DefiningQuery as follows.

<EntitySet Name="Addresses"

EntityType="EcommerceModel.Store.Addresses">

<DefiningQuery>

select AddressId ,ContactId BillingContactId,null ShippingContactId,FullAddress

from QueryView.Addresses billing where AddressType = 'B'

union

select AddressId,null

BillingContactId,ContactId ShippingContactId,FullAddress from QueryView.Addresses billing where AddressType = 'S'

</DefiningQuery>

</EntitySet>

The above DefiningQuery does a union of two select statements where the first select gives a view where AddressType is Billing. Notice that we have assigned the ContactId to BillingContactId and ShippingContactId is passed a default value of Null. Similarly when AddressType is shipping BillingContactId is assigned a value of Null. The DefiningQuery created is mapped to Addresses entity which does not exist. So create Addresses entity on the store model as follows.

<EntityType Name="Addresses">

<Key>

<PropertyRef Name="AddressId" />

</Key>

<Property Name="AddressId" Type="int" Nullable="false"

/>

<Property Name="FullAddress" Type="varchar"

Nullable="false" MaxLength="100" />

<Property Name="BillingContactId" Type="int"

Nullable="true" />

<Property Name="ShippingContactId" Type="int"

Nullable="true" />

</EntityType>

The addresses entity type simply matches the columns returned from above DefiningQuery. Now that we have created Addresses entityset, we can go back to the designer and map the Billing and Shipping associations to EntitySet defined on the store model.

6. Map Billing association to Address table where ContactId is mapped BillingContactId as shown below.

7. Map Shipping association to Address table where ContactId is mapped to ShippingContactId as shown below.

8. Map Address entity to address table. The designer should map the AddressId to AddressId column and FullAddress to FullAddress column in Address table.

9. To test the above model, we can retrieve a Contact, its Billing and shipping Address. On the code below I am using Include to retrieve Billing and Shipping Address for contact and printing the result to the console window.

var db = new MultipleAssoEntities();

var contact = db.Contacts

.Include("BillingAddress").Include("ShippingAddress") .First(c => c.ContactName ==

"Zeeshan");

Console.WriteLine("Name {0}",contact.ContactName);

Console.WriteLine("Billing {0}",contact.BillingAddress.FullAddress);

Console.WriteLine("Shipping {0}",contact.ShippingAddress.FullAddress);

5. Inheritance

Basics of Inheritance

Entity framework supports 3 different models of inheritance.

1. Table Per Hierarchy (Single Table Inheritance) 2. Table Per Type

3. Table Per Concrete Class

Of all the supported inheritance models, the most simplest and easiest to implement is Table Per Hierarchy (Single Table Inheritance). To implement this inheritance, you store all concrete types in one table. In Entity framework to identity a row as a specific concrete type, you define a discriminator

column which identities which concrete type a specific row gets mapped to.

From a usability point, I have found Single table model to be very easy to get started. However from the database perspective, the model doesn't seem to favor a clean approach. The reason is you are storing all different concrete types in a single table. Some concrete types would need certain columns where as others won't. To accomplish flexibility at the table level, you have to mark all columns that are specific to their concrete implementation as allow nulls. Some database developers may find this approach not a good solution because it does not make efficient use of disk space. On the other hand table per hierarchy offers good performance because to find a concrete type, you

don’t have to apply joins to another table which can be costly if the table is too big. Since all the types are stored in one table, you can apply index on the discriminator column to allow faster searches based on concrete type you are looking for. To map this structure as table per hierarchy in entity data model, you have to define the column which entity framework can use to identity each type, basically a discriminator column. Next you need to move specific field for each type from the base class to its own entity.

In Table per Type model, you define a base table that contains fields common across all types. Then you define a table for each type which contains fields that are specific to that type. In addition the primary key column defined on the derived table is also the foreign key for the base table. To map this form of table structure into table per type entity model, each individual type needs to inherit from the base type where the base type is mapped to the base table defined on the database. Each derived type needs to be mapped to its specific table in the database. Additionally, you have to delete the primary key

property on the derived entity generated by the designer and map the primary key column on the derived entity to the entity key defined on the base class.

In table Per Concrete Type, each table represents the entire entity. It is not required that two tables participating in Table Per Type have same number of columns. The columns that are specific to a table that is not in another table participating in table per type, would end up as a property on the derived entity.

Rest of the columns would be placed as properties on the base entity. Table Per Concrete Type is not fully supported on the designer so you start with importing the model and create your conceptual model but for modeling table per concrete type, you have to manually edit the xml file. One of the reasons you create table per concrete type is to portray data coming from multiple tables as being a single entity retrieving data from a single table. This means that primary key or entity key on the conceptual model cannot be duplicated. You cannot have primary key of 1 on table1 and primary key of 1 on table2 as well because this would cause entity framework to throw primary key violation constraint.