Table Per Concrete Type Inheritance in Entity Framework

Table Per Concrete Type Inheritance in Entity Framework

The last inheritance
mapping that I’m
going to write about
is the Table Per Concrete
Type inheritance (TPC). This
inheritance type is
very rare but you should
be aware of how to create it when it is needed.
You can read about TPT and TPH from here and from here.

Table Per Concrete Type Definition

The TPC inheritance occurs when we have two tables with
overlapping fields in the database. Such a thing can occur
in situations that we create an history table that all historical
data is transferred from the main table to it.
In the example I’m going to use the following database schema:
 
As you can notice I have a Department table and a DepartmentHistory
table. They both have overlapping fields.

TPC Example

The following steps will help you to understand how you can create
a TPC inheritance mapping. I’m going to use the exactly database
from the first figure I showed.

Step 1
The first step is to create the Entity Data Model from the database.
Here is the EDM I’m going to use in the example:

Step 2
Create an inheritance from OldDepartment (which is mapped to
DepartmentHistory table) to Department entity. In order to do that
in the designer surface click on the right mouse button and use the
Add –> Inheritance menu item.

In the Add Inheritance dialog make the Department the base entity
and the OldDepartment derived entity.

After you do this the model will look like:

Step 3
Since we have overlapping tables remove all the properties of OldDepartment.
These properties are going to be taken from the Department entity.
The model should look like:
 

Step 4
Now it is time for the mapping. In the Mapping Details View you
will notice that the only available property for mapping is the
DepartmentID property. Map it in the OldDeprtment.

All the other mapping details will be needed to be edit manually
in the Xml editor.

Step 5
Open the model in Xml editor and seek for the mapping of OldDepartment
entity in the MSL part. You will see that the Departments set is mapped
as:

<EntitySetMapping Name="Departments">

  <EntityTypeMapping TypeName="IsTypeOf(SchoolModel.Department)">

    <MappingFragment StoreEntitySet="Department">

      <ScalarProperty Name="DepartmentID" ColumnName="DepartmentID" />

      <ScalarProperty Name="Administrator" ColumnName="Administrator" />

      <ScalarProperty Name="StartDate" ColumnName="StartDate" />

      <ScalarProperty Name="Budget" ColumnName="Budget" />

      <ScalarProperty Name="Name" ColumnName="Name" /></MappingFragment></EntityTypeMapping>

  <EntityTypeMapping TypeName="IsTypeOf(SchoolModel.OldDepartment)">

    <MappingFragment StoreEntitySet="DepartmentHistory">

      <ScalarProperty Name="DepartmentID" ColumnName="DepartmentID" />

    </MappingFragment>

  </EntityTypeMapping>

</EntitySetMapping>

Copy all the property mappings from Department to OldDepartment
mapping and that is all. The code will look like:

<EntitySetMapping Name="Departments">

            <EntityTypeMapping TypeName="IsTypeOf(SchoolModel.Department)">

              <MappingFragment StoreEntitySet="Department">

                <ScalarProperty Name="DepartmentID" ColumnName="DepartmentID" />

                <ScalarProperty Name="Administrator" ColumnName="Administrator" />

                <ScalarProperty Name="StartDate" ColumnName="StartDate" />

                <ScalarProperty Name="Budget" ColumnName="Budget" />

                <ScalarProperty Name="Name" ColumnName="Name" /></MappingFragment></EntityTypeMapping>

            <EntityTypeMapping TypeName="IsTypeOf(SchoolModel.OldDepartment)">

              <MappingFragment StoreEntitySet="DepartmentHistory">

                <ScalarProperty Name="DepartmentID" ColumnName="DepartmentID" />

                <ScalarProperty Name="Administrator" ColumnName="Administrator" />

                <ScalarProperty Name="StartDate" ColumnName="StartDate" />

                <ScalarProperty Name="Budget" ColumnName="Budget" />

                <ScalarProperty Name="Name" ColumnName="Name" />

              </MappingFragment>

            </EntityTypeMapping>

          </EntitySetMapping>

Step 6
Test the created inheritance. The following code will print out
all the departments and then all the old departments in the
database.

using (var context = new SchoolEntities())

{

    var departments = context.Departments.OfType<Department>();                

    foreach (var department in departments)

    {

        Console.WriteLine(department.Name);

    }

    var oldDepartments = context.Departments.OfType<OldDepartment>();

    foreach (var department in oldDepartments)

    {

        Console.WriteLine("old: {0}", department.Name);

    }

}

Console.ReadLine();

Summary

Lets sum up, in the last post about inheritance types I explained
what is Table Per Concrete Type Inheritance and showed how
to map it in EF. There are more ways to customize the conceptual model
that we have in EF like using Complex Types, DefiningQuery and etc.
Even though, inheritance mapping is very useful in order to express our
conceptual model as we see it in Object Oriented.