Operators supported on QueryView

3. Eager and Lazy Loading entities and Navigation properties Navigation properties

3.4 Relationship Span

4.2.1 Operators supported on QueryView

4.2.2 Mapping Foreign Key column to Multiple Associations Using DefiningQuery

Problem: Figure below shows the database diagram for a Gun and its promoters.

A gun show can only have a single promoter identified by PromoterId.

However the PromoterId could be the Id column that represents ClubId in GunClubs table or ShootingRangeId in ShootingRange table. To identity the promoterId belongs to which table, there is a PromoterType field added on GunShow table that defines if the Id belongs to GunClubs or ShootingRange.

You want to import the above model with GunShow having two different associations. One association would expose an entity ref GunClub and other association would expose a ShootingRange.

Solution: If we look at the above database model, we will realize that we are mapping PromoterId as a foreign key to multiple tables. But for a single GunShow it is either mapped to GunClub or ShootingRange not both. Entity Framework does not allow a single column to be mapped to multiple

associations because that could corrupt the model. Although in our case it is

genuine mapping because only 1 mapping is valid at a given point. To get around this limitation we need to create a view that exposes PromoterId column on GunShow as two columns ShootingRangeId and ClubId. If the GunShow’s promoter is GunClub , then ClubId will have a value but ShootingRangeId would be null. Similarly if GunShow’s promoter is

ShootingRange, then ShootingRangeId would have a value and ClubId would be null. After creating the view, import the GunShow view, GunClub and ShootingRange table. Create an association between GunShow and GunClub where a GunShow can have a single GunClub as a promoter. Similarly create an association between GunShow and ShootingRange where a GunShow can have a single ShootingRange as a promoter. Ensure that mapping between GunShow and GunClub uses ClubId column on GunShow view and mapping for ShootingRange and GunShow uses ShootingRangeid column on

GunShow view. Since views are not updatable, we have to create stored

procedures for GunShows, ShootingRange and GunClubs to insert record into the database. So stored procedures needs to be declared inside store model and then mapped to entities on the msl section.

Discussion: As we discussed EF does not allow mapping a single column to multiple associations because that would corrupt the model and would cause data loss to happen. For instance if it allowed mapping multiple associations to a single foreign key column, then you can set PromoterId to have a ClubId and ShootingRangeId both which is not possible because PromoterId column can have only one value either it be ClubId or ShootingRangeId. Although the scenario is perfectly correct because at a given point either GunClub or

ShootingRange can be a promoter but not both. Both currently in Ef there is no way to represent optional association. To get around the limitation, we can create a view that exposes promoterId as two columns ClubId and

ShootingRangeId on GunShow view. Code below shows the view for GunShow.

create view [dbo].[vwGunShow]

select s.ShowId,s.ShowName,s.VendorsRegistered,r.ShootingRangeId,null ClubId from tpt.GunShows s

join tpt.ShootingRange r on s.PromoterId = r.ShootingRangeId where s.PromoterType = 'SR'

union

select s.ShowId,s.ShowName,s.VendorsRegistered,null ShootingRangeId,g.ClubId

from tpt.GunShows s

join tpt.GunClubs g on s.PromoterId = g.ClubId and s.PromoterType = 'GC' GO

The view above consists of two unions. The first union is a join between GunShow and ShootingRange where the PromoterType is SR. Since the join is against shootingRange table, I am setting null for ClubId column. Similarly the second join is against GunShow and GunClub where PromoterType is GC. Now that we have exposed two columns as foreign key to both GunClub and ShootingRange, we do not need to expose promotertype from our view.

When we import the view into our model, EF creates a DifiningQuery which basically selects from the view created on the database.

Rest of the steps below will outline the process of importing the view, GunClub and ShootingRange into our entity data model.

1. Import vwGunShow, GunClub and ShootingRange table into EDM using entity model wizard. Figure below shows the model after the wizard has completed.

2. When we imported the view, EF does not know what the primary key for the view is; therefore it marks all properties as participating in

primary key. To remove primary key declaration, open the edmx in xml and go to vwGunShow entity in ssdl and make sure that only ShowId is marked as entity key. Code below shows the correct entity key for the view.

<Key>

</Key>

<Property Name="ShowName" Type="varchar" Nullable="false"

MaxLength="100" />

</EntityType>

Also make sure that GunShow entity on the conceptual model also has ShowId as the entity key. Remove any extra columns from entity key.

3. Since ClubId and ShootingRangeId column will be used in association, it cannot be mapped to properties. So remove ShootingRangeId and ClubId property from GunShow entity.

4. Create association between GunShow and ShootingRange where GunShow has a multiplicity of Many and ShootingRange has a multiplicity of 1. Figure below shows the association dialog with correct mappings.

5. Create association between GunClubs and GunShow where GunShow has a multiplicity of Many and GunClub has a multiplicity of 1. Figure below shows the association dialog to configure GunClub and

GunShow.

After completing the association, the completed entity model should look like below.

Next step is to configure the mapping for the association we created between GunShow, ShootingRange and GunClub.

6. Select the association line between GunShow and ShootingRange and open up mapping window. On the mapping window, select GunShow table. The designer would map the ShowId column ShowId entity key on GunShow entity and map ShootingRangeId column to

ShootingRangeId property on ShootingRange entity. Figure below shows the mapping between GunShow and ShootingRange.

7. Select the association line between GunShow and GunClub and open up mapping window. On the mapping window select GunShow and the designer would auto map the columns to properties. Figure below shows the correct mapping for the association.

8. Since we used a view for GunShow entity, entity framework cannot insert, update and delete GunShow entity. We have to create stored procedures and map them using the designer. Another constraint EF enforces is if an entity participates in using stored procedures to perform crud, then all the related entities must also be saved using stored procedures.

SSDL below shows the stored procedure required to insert GunClub.

<Function Name="InsertGunClub" BuiltIn="false"

IsComposable="false">

insert into

tpt.GunClubs(ClubName,President) values (@ClubName,@President) select SCOPE_IDENTITY() as ClubId

</CommandText>

<Parameter Name="ClubName" Mode="In"

Type="varchar" />

<Parameter Name="President" Mode="In"

Type="varchar" />

</Function>

<Function Name="UpdateGunClub" BuiltIn="false"

IsComposable="false">

update tpt.GunClubs set ClubName =

@ClubName,President =@President where ClubId =@ClubId </CommandText>

<Parameter Name="ClubId" Mode="In" Type="int"

<Parameter Name="ClubName" Mode="In"

Type="varchar" />

<Parameter Name="President" Mode="In"

Type="varchar" />

</Function>

<Function Name="DeleteGunClub" BuiltIn="false"

IsComposable="false">

delete tpt.GunClubs where ClubId = @ClubId </CommandText>

<Parameter Name="ClubId" Mode="In" Type="int"

</Function>

SSDL below shows the stored procedures to perform crud on ShootingRange entity.

select SCOPE_IDENTITY() as ShootingRangeId </CommandText>

<Parameter Name="RangeName" Mode="In"

Type="varchar" />

</Function>

<Function Name="UpdateShootingRange" BuiltIn="false"

IsComposable="false">

update tpt.ShootingRange set RangeName =

@RangeName,Fees =@Fees where ShootingRangeId =@ShootingRangeId </CommandText>

<Parameter Name="ShootingRangeId" Mode="In"

Type="int" />

<Parameter Name="RangeName" Mode="In"

Type="varchar" />

</Function>

<Function Name="DeleteShootingRange" BuiltIn="false"

IsComposable="false">

delete tpt.ShootingRange where ShootingRangeId = @ShootingRangeId

</CommandText>

<Parameter Name="ShootingRangeId" Mode="In"

Type="int" />

</Function>

SSDL below shows the stored procedures to perform crud activity on GunShow entity. Notice that insert stored procedure checks to see if ShootingRangeId is not null then it assigns promoterType to be SR otherwise the promoterType get set to GC for GunClub. The delete stored procedure for GunShow takes both ShootingRangeId and ClubId in addition to ShowId. Although to delete a gun show from GunShow table, we only need ShowId, however entity framework requires all associationMappings be mapped to stored procedure regardless if they will be used. This restriction will be removed in the next version of EF.

<Function Name="InsertGunShow" BuiltIn="false"

if @ShootingRangeId is not null begin

set @promoterid = @ShootingRangeId set @promotertype = 'SR' select SCOPE_IDENTITY() as ShowId </CommandText>

<Parameter Name="ShowName" Mode="In"

Type="varchar" />

<Parameter Name="VendorsRegistered" Mode="In"

Type="int" />

<Parameter Name="ShootingRangeId" Mode="In"

Type="int" />

<Parameter Name="ClubId" Mode="In" Type="int"

</Function>

<Function Name="UpdateGunShow" BuiltIn="false"

IsComposable="false">

update tpt.GunShows set ShowName

=@ShowName,VendorsRegistered =@VendorsRegistered where showid =@ShowId

</CommandText>

<Parameter Name="ShowName" Mode="In"

Type="varchar" />

<Parameter Name="VendorsRegistered" Mode="In"

Type="int" />

<Parameter Name="ShootingRangeId" Mode="In"

Type="int" />

<Parameter Name="ClubId" Mode="In" Type="int"

<Parameter Name="ShowId" Type="int" Mode="In"

</Function>

<Function Name="DeleteGunShow" BuiltIn="false"

IsComposable="false">

delete tpt.GunShows where showid =@ShowId </CommandText>

<Parameter Name="ShowId" Mode="In" Type="int"

<Parameter Name="ShootingRangeId" Mode="In"

Type="int" />

<Parameter Name="ClubId" Mode="In" Type="int"

</Function>

Above examples are actually not stored procedures but inline sql defined on the model directly. The reason is convience for the user to see everything in one place. In real production application, it is

recommended that you define actual stored procedures on the database.

9. After defining the stored procedures on the ssdl, we need to go into the designer and map the stored procedures to entities. To map GunClub entity to stored procedure, right click the entity and choose stored procedure mapping. For Insert stored procedure, choose

InsertGunClub, for Update choose UpdateGunClub and for Delete choose DeletegunClub. After selecting the stored procedures the

designer would auto map properties to column. Figure below shows the stored procedure mapping.

10. To map ShootingRange entity, open stored procedure mapping and select InsertShootingRange, UpdateShootingRange and

DeleteShootingRange entity. Figure below shows the stored procedure mapping for ShootingRange entity.

11. For GunShow entity, select InsertGunShow, UpdateGunShow and DeleteGun stored procedure. To Map ShootingRangeId and ClubId, we have to select the navigation property ShootingRange and GunClub to access its ShootingRangeId and ClubId entity key. For delete stored procedure we have to not only map ShowId but also ShootingRangeId and ClubId because these are the associations of GunShow entity and EF requires all associations be mapped. Figure below shows the stored procedure mapping.

To test the model, we can create an instance of GunShow entity and assign a GunGlub and save the GunShow entity to the database. Then using the second data context, we can retrieve the gun show along with the gun club and print the results to the console window. Code below accomplishes that task

var db = new DQForeignEntities();

On the above code, I am creating an instance of GunClub and assigning it to GunClub property of the newly created GunShow entity. Using the second datacontext, I am printing the ShowName and the GunClub name to the console window.

4.2.3 Creating Dummy Defining Query to map stored

In document Entity Framework Learning Guide (Page 157-171)