Entity Framework (TPT): Part II

Table Inheritance in Entity Framework

In OOP, inheritance is used to reduce unnecessary code. We can also acheive inheritance using the Entity Framework.

Entity Framework supports three difference types of inheritance.

1. THP - Table Per Hierarchy
2. TPT - Table Per Type
3. TPC - Table Per Concrete Class

Table per Type

Table-per-type inheritance uses a separate table in the database to maintain data and uses a single entity type in the Entity Framework. In other words, there is a single entity set in the Entity Framework for many database tables.

The main advantage of Table per Type is that the SQL schema is normalized as you want. In addition, model enhancement is very straightforward i.e. only modifying base class or adding new sub class on modifying or adding new table.

Inheritance using TPT

Step 1: Create Entity Model from database.

Step 2: Delete Entity Relation.

Step 3: Add inheritances

Step 4: Delete derived entity key property.

Delete derived entities (customer and Employee) key property (in this case CustomerID and EmployeeId respectively). These key properties are mapped with PersonId in next step.

Step 5: Map Key property of derived type with key property of Base Type.

Update EDMX Manually.

1. Storage Model
   
   In storage Model, Define Entity type, Key and properties which are same as database. There is no definition for derived in storage model.
   
   <EntityContainer Name="AdventureWorksModelStoreContainer">
             <EntitySet Name="Customer" EntityType="AdventureWorksModel.Store.Customer" store:Type="Tables" Schema="dbo" />
             <EntitySet Name="Employees" EntityType="AdventureWorksModel.Store.Employees" store:Type="Tables" Schema="dbo" />
             <EntitySet Name="Person" EntityType="AdventureWorksModel.Store.Person" store:Type="Tables" Schema="dbo" />
             <AssociationSet Name="FK_Customer_Person" Association="AdventureWorksModel.Store.FK_Customer_Person">
               <End Role="Person" EntitySet="Person" />
               <End Role="Customer" EntitySet="Customer" />
             </AssociationSet>
             <AssociationSet Name="FK_Employees_Person" Association="AdventureWorksModel.Store.FK_Employees_Person">
               <End Role="Person" EntitySet="Person" />
               <End Role="Employees" EntitySet="Employees" />
             </AssociationSet>
   </EntityContainer>

   <EntityType Name="Customer">
             <Key>
               <PropertyRef Name="CustomerID" />
             </Key>
             <Property Name="CustomerID" Type="int" Nullable="false" />
             <Property Name="CustomerCode" Type="varchar" Nullable="false" MaxLength="50" />
             <Property Name="CustomerName" Type="varchar" Nullable="false" MaxLength="50" />
             <Property Name="IsActive" Type="bit" Nullable="false" />
   </EntityType>

   <EntityType Name="Employees">
             <Key>
               <PropertyRef Name="EmployeeId" />
             </Key>
             <Property Name="EmployeeId" Type="int" Nullable="false" />
             <Property Name="EmployeeCode" Type="varchar" Nullable="false" MaxLength="25" />
             <Property Name="Name" Type="varchar" Nullable="false" MaxLength="150" />
             <Property Name="DepartmentId" Type="int" Nullable="false" />
             <Property Name="Isactive" Type="bit" Nullable="false" />
           </EntityType>
           <EntityType Name="Person">
             <Key>
               <PropertyRef Name="PersonId" />
             </Key>
             <Property Name="PersonId" Type="int" Nullable="false" />
             <Property Name="EmailAddress" Type="varchar" MaxLength="100" />
   </EntityType>

   <Association Name="FK_Customer_Person">
             <End Role="Person" Type="AdventureWorksModel.Store.Person" Multiplicity="1" />
             <End Role="Customer" Type="AdventureWorksModel.Store.Customer" Multiplicity="0..1" />
             <ReferentialConstraint>
               <Principal Role="Person">
                 <PropertyRef Name="PersonId" />
               </Principal>
               <Dependent Role="Customer">
                 <PropertyRef Name="CustomerID" />
               </Dependent>
             </ReferentialConstraint>
   </Association>

   <Association Name="FK_Employees_Person">
             <End Role="Person" Type="AdventureWorksModel.Store.Person" Multiplicity="1" />
             <End Role="Employees" Type="AdventureWorksModel.Store.Employees" Multiplicity="0..1" />
             <ReferentialConstraint>
               <Principal Role="Person">
                 <PropertyRef Name="PersonId" />
               </Principal>
               <Dependent Role="Employees">
                 <PropertyRef Name="EmployeeId" />
               </Dependent>
             </ReferentialConstraint>
   </Association>
    

2. Conceptual Model
   
   Define base type entity and derived type entity set in the conceptual model.
   
   <EntityType Name="Customer" BaseType="AdventureWorksModel.Person">
             <Property Type="String" Name="CustomerCode" Nullable="false" MaxLength="50" FixedLength="false" Unicode="false" />
             <Property Type="String" Name="CustomerName" Nullable="false" MaxLength="50" FixedLength="false" Unicode="false" />
             <Property Type="Boolean" Name="IsActive" Nullable="false" />
   </EntityType>

   <EntityType Name="Employee" BaseType="AdventureWorksModel.Person">
             <Property Type="String" Name="EmployeeCode" Nullable="false" MaxLength="25" FixedLength="false" Unicode="false" />
             <Property Type="String" Name="Name" Nullable="false" MaxLength="150" FixedLength="false" Unicode="false" />
             <Property Type="Int32" Name="DepartmentId" Nullable="false" />
             <Property Type="Boolean" Name="Isactive" Nullable="false" />
             <NavigationProperty Name="Department" Relationship="AdventureWorksModel.FK_Employees_Department" FromRole="Employee" ToRole="Department" />
   </EntityType>

   <EntityType Name="Person">
             <Key>
               <PropertyRef Name="PersonId" />
             </Key>
             <Property Type="Int32" Name="PersonId" Nullable="false" />
             <Property Type="String" Name="EmailAddress" MaxLength="100" FixedLength="false" Unicode="false" />
   </EntityType>
    

3. Mapping Model
   
   Map the base entity type and derived types in a same EntitySetMapping element in this section. Map inherited properties to the table.
   
   Use the IsTypeOf syntax when setting the value of the TypeName attribute
   
   <EntitySetMapping Name="People">
               <EntityTypeMapping TypeName="IsTypeOf(AdventureWorksModel.Person)">
                 <MappingFragment StoreEntitySet="Person">
                   <ScalarProperty Name="PersonId" ColumnName="PersonId" />
                   <ScalarProperty Name="EmailAddress" ColumnName="EmailAddress" />
                 </MappingFragment>
               </EntityTypeMapping>
               <EntityTypeMapping TypeName="IsTypeOf(AdventureWorksModel.Customer)">
                 <MappingFragment StoreEntitySet="Customer">
                   <ScalarProperty Name="PersonId" ColumnName="CustomerID" />
                   <ScalarProperty Name="CustomerCode" ColumnName="CustomerCode" />
                   <ScalarProperty Name="CustomerName" ColumnName="CustomerName" />
                   <ScalarProperty Name="IsActive" ColumnName="IsActive" />
                 </MappingFragment>
                 </EntityTypeMapping>
               <EntityTypeMapping TypeName="IsTypeOf(AdventureWorksModel.Employee)">
                 <MappingFragment StoreEntitySet="Employees">
                   <ScalarProperty Name="PersonId" ColumnName="EmployeeId" />
                   <ScalarProperty Name="EmployeeCode" ColumnName="EmployeeCode" />
                   <ScalarProperty Name="Name" ColumnName="Name" />
                   <ScalarProperty Name="DepartmentId" ColumnName="DepartmentId" />
                   <ScalarProperty Name="Isactive" ColumnName="Isactive" />
                 </MappingFragment>
                 </EntityTypeMapping>
   </EntitySetMapping>

In the next article we will learn about TPC (Table per Concrete Class) and how to add it in EDMX.