Entity Framework (TPT): Part II

Table Inheritance in Entity Framework

 

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

 

Entity Framework supports three different 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 the base class or adding a new subclass on modifying or adding a new table.

 

Inheritance using TPT

 

Step 1: Create an Entity Model from the 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 the next step.

 

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

 

 

Update EDMX Manually

1. Storage Model
    
   In the storage Model, Define Entity type, Key, and properties that are the same as the database. There is no definition for derived in the 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 the 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.