Table per Type Walkthrough - Relationship Span

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

3.4 Relationship Span

5.1.1 Table per Type Walkthrough

Problem: You have created 3 table Media, Video and Articles in the

database. Media table contains common fields for both Articles and Videos.

Fields specific to Video and Articles are stored in their respective table. You want to map this structure to entity data model using Table per Type

inheritance.

Solution:

Discussion:

Media table in the database consists of MediaId, Title and Description where MediaId represents the primary key of the table. Video table has VideoId as the primary key which is also the foreign key for Media table. Video table contains an additional column ResourcePath which is the location on the network where the media resides. Articles table has ArticleId as the primary key which is also the foreign key to Media table. ArticleContent contains the content of the article. Figure below shows the database diagram for Media.

The next step is to import the database model using import database wizard.

Screen shot below shows the model created by edm when we import the raw tables.

When we import the tables, entity framework maps one to one relationship between Media, Article and Video as 1 to 0-1 associations. What we want is an inheritance hierarchy. So the first step is to delete the association and add inheritance with Media as the base entity.

To delete the association, select both associations and click delete. To add inheritance right click Media entity and choose inheritance. On the

inheritance window popup, select Media as the base entity and Video as the derived entity. Figure below shows the correct entity selected for inheritance between Media and Video.

To create inheritance for article, same process can be followed. After setting inheritance for both entities, Articles and Videos, final figure should look like the one below.

If you try to validate the model, you will get validation errors. To fix the validation errors remove the VideoId and ArticleId from Video and Article

entity. For video entity change the table mapping where VideoId is mapped to Mediaid as shown below.

Video Mapping

To map Article entity, map ArticleId to MediaId as shown below.

Since we are only using Media as a base and will not instantiate on its own it is important that we mark Media entity as Abstract. Therefore we can only instantiate concrete implementations of Media such as Article and Videos.

Article Mapping

Although table per type mapping is fully supported by the designer, it is essential to understand the mapping written by the designer to persist the derived types to the database. Sample below shows the mapping for Articles and Videos with Media as the base type.

<EntityTypeMapping

TypeName="IsTypeOf(SimpleTPTInheritance.Media)">

<ScalarProperty Name="Description" ColumnName="Description"

</MappingFragment>

</EntityTypeMapping>

<ScalarProperty Name="ResourcePath" ColumnName="ResourcePath"

In the above sample, Media base entity is mapped to Medias table in the database. Notice TypeName uses IsTypeof of Media which means this mapping applies to any entity that derives from Media entity. Next two

entities Articles and Videos map to their respective table with type video and Article. The IsTypeOf is also used in derived types for the case if there are other entities that derive from Articles and Videos. However in our case we can simply type in the class name for derived types without using IsTypeOf.

<ScalarProperty Name="Description" ColumnName="Description"

</MappingFragment>

</EntityTypeMapping>

<ScalarProperty Name="ResourcePath" ColumnName="ResourcePath"

</EntitySetMapping>

To test the model, we can create an instance of the object context, add article and video to the media collection and call SaveChanges on the objectContext.

In the example below, I am adding article and video entity to AddToMedias method generated by the designer. AddToMedias can take any entity derived from media class. You don’t get separate methods to add Media and Articles.

Similarly to read Article and Video there is so no separate property exposed on the object to directly access Articles and Videos. Instead ObjectContext exposes the base class reference Articles and you have to use OfType

operator to only return Articles of a certain derived type. The entities returned are returned as derived class references which means you do not have to perform explicit cast to get a derived type instance. In the example below, to get videos, I am using a where linq operator and filtering to only return Media of Type Video. Since where operator returns a base class reference, I am additionally using Cast operator to cast the Media base class reference to Video derived type to access properties on the Video entity.

var db = new STPTInh();

{

Console.WriteLine("Title {0} Resource {1}", vid.Title, vid.ResourcePath);

}

Screen shot below shows the output of the above code on Console window.

In document Entity Framework Learning Guide (Page 187-193)