Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Conferences
Free Learning
Arrow right icon

Entity Framework DB First – Inheritance Relationships between Entities

Save for later
  • 19 min read
  • 02 Mar 2015

article-image

This article is written by Rahul Rajat Singh, the author of Mastering Entity Framework. So far, we have seen how we can use various approaches of Entity Framework, how we can manage database table relationships, and how to perform model validations using Entity Framework. In this article, we will see how we can implement the inheritance relationship between the entities. We will see how we can change the generated conceptual model to implement the inheritance relationship, and how it will benefit us in using the entities in an object-oriented manner and the database tables in a relational manner.

(For more resources related to this topic, see here.)

Domain modeling using inheritance in Entity Framework

One of the major challenges while using a relational database is to manage the domain logic in an object-oriented manner when the database itself is implemented in a relational manner. ORMs like Entity Framework provide the strongly typed objects, that is, entities for the relational tables. However, it might be possible that the entities generated for the database tables are logically related to each other, and they can be better modeled using inheritance relationships rather than having independent entities.

Entity Framework lets us create inheritance relationships between the entities, so that we can work with the entities in an object-oriented manner, and internally, the data will get persisted in the respective tables. Entity Framework provides us three ways of object relational domain modeling using the inheritance relationship:

  • The Table per Type (TPT) inheritance
  • The Table per Class Hierarchy (TPH) inheritance
  • The Table per Concrete Class (TPC) inheritance

Let's now take a look at the scenarios where the generated entities are not logically related, and how we can use these inheritance relationships to create a better domain model by implementing inheritance relationships between entities using the Entity Framework Database First approach.

The Table per Type inheritance

The Table per Type (TPT) inheritance is useful when our database has tables that are related to each other using a one-to-one relationship. This relation is being maintained in the database by a shared primary key. To illustrate this, let's take a look at an example scenario.

Let's assume a scenario where an organization maintains a database of all the people who work in a department. Some of them are employees getting a fixed salary, and some of them are vendors who are hired at an hourly rate. This is modeled in the database by having all the common data in a table called Person, and there are separate tables for the data that is specific to the employees and vendors. Let's visualize this scenario by looking at the database schema:entity-framework-db-first-inheritance-relationships-between-entities-img-0

The database schema showing the TPT inheritance database schema

The ID column for the People table can be an auto-increment identity column, but it should not be an auto-increment identity column for the Employee and Vendors tables.

In the preceding figure, the People table contains all the data common to both type of worker. The Employee table contains the data specific to the employees and the Vendors table contains the data specific to the vendors. These tables have a shared primary key and thus, there is a one-to-one relationship between the tables.

To implement the TPT inheritance, we need to perform the following steps in our application:

  1. Generate the default Entity Data Model.
  2. Delete the default relationships.
  3. Add the inheritance relationship between the entities.
  4. Use the entities via the DBContext object.

Generating the default Entity Data Model

Let's add a new ADO.NET Entity Data Model to our application, and generate the conceptual Entity Model for these tables. The default generated Entity Model will look like this:entity-framework-db-first-inheritance-relationships-between-entities-img-1

The generated Entity Data Model where the TPT inheritance could be used

Looking at the preceding conceptual model, we can see that Entity Framework is able to figure out the one-to-one relationship between the tables and creates the entities with the same relationship.

However, if we take a look at the generated entities from our application domain perspective, it is fairly evident that these entities can be better managed if they have an inheritance relationship between them. So, let's see how we can modify the generated conceptual model to implement the inheritance relationship, and Entity Framework will take care of updating the data in the respective tables.

Deleting default relationships

The first thing we need to do to create the inheritance relationship is to delete the existing relationship from the Entity Model. This can be done by right-clicking on the relationship and selecting Delete from Model as follows:entity-framework-db-first-inheritance-relationships-between-entities-img-2

Deleting an existing relationship from the Entity Model

Adding inheritance relationships between entities

Once the relationships are deleted, we can add the new inheritance relationships in our Entity Model as follows:entity-framework-db-first-inheritance-relationships-between-entities-img-3

Adding inheritance relationships in the Entity Model

When we add an inheritance relationship, the Visual Entity Designer will ask for the base class and derived class as follows:entity-framework-db-first-inheritance-relationships-between-entities-img-4

Selecting the base class and derived class participating in the inheritance relationship

Once the inheritance relationship is created, the Entity Model will look like this:entity-framework-db-first-inheritance-relationships-between-entities-img-5

Inheritance relationship in the Entity Model

After creating the inheritance relationship, we will get a compile error that the ID property is defined in all the entities. To resolve this problem, we need to delete the ID column from the derived classes. This will still keep the ID column that maps the derived classes as it is.

So, from the application perspective, the ID column is defined in the base class but from the mapping perspective, it is mapped in both the base class and derived class, so that the data will get inserted into tables mapped in both the base and derived entities.

With this inheritance relationship in place, the entities can be used in an object-oriented manner, and Entity Framework will take care of updating the respective tables for each entity.

Using the entities via the DBContext object

As we know, DbContext is the primary class that should be used to perform various operations on entities. Let's try to use our SampleDbContext class to create an Employee and a Vendor using this Entity Model and see how the data gets updated in the database:

using (SampleDbEntities db = new SampleDbEntities())
{
    Employee employee = new Employee();
    employee.FirstName = "Employee 1";
    employee.LastName = "Employee 1";
    employee.PhoneNumber = "1234567";
    employee.Salary = 50000;
    employee.EmailID = "employee1@test.com";
    Vendor vendor = new Vendor();
    vendor.FirstName = "vendor 1";
    vendor.LastName = "vendor 1";
    vendor.PhoneNumber = "1234567";
    vendor.HourlyRate = 100;
    vendor.EmailID = "vendor1@test.com";

    db.Workers.Add(employee);
    db.Workers.Add(vendor);
    db.SaveChanges();
}

In the preceding code, what we are doing is creating an object of the Employee and Vendor type, and then adding them to People using the DbContext object. What Entity Framework will do internally is that it will look at the mappings of the base entity and the derived entities, and then push the respective data into the respective tables. So, if we take a look at the data inserted in the database, it will look like the following:entity-framework-db-first-inheritance-relationships-between-entities-img-6

A database snapshot of the inserted data

It is clearly visible from the preceding database snapshot that Entity Framework looks at our inheritance relationship and pushes the data into the Person, Employee, and Vendor tables.

The Table per Class Hierarchy inheritance

The Table per Class Hierarchy (TPH) inheritance is modeled by having a single database table for all the entity classes in the inheritance hierarchy. The TPH inheritance is useful in cases where all the information about the related entities is stored in a single table. For example, using the earlier scenario, let's try to model the database in such a way that it will only contain a single table called Workers to store the Employee and Vendor details. Let's try to visualize this table:entity-framework-db-first-inheritance-relationships-between-entities-img-7

A database schema showing the TPH inheritance database schema

Now what will happen in this case is that the common fields will be populated whenever we create a type of worker. Salary will only contain a value if the worker is of type Employee. The HourlyRate field will be null in this case. If the worker is of type Vendor, then the HourlyRate field will have a value, and Salary will be null.

This pattern is not very elegant from a database perspective. Since we are trying to keep unrelated data in a single table, our table is not normalized. There will always be some redundant columns that contain null values if we use this approach. We should try not to use this pattern unless it is absolutely needed.

To implement the TPH inheritance relationship using the preceding table structure, we need to perform the following activities:

  1. Generate the default Entity Data Model.
  2. Add concrete classes to the Entity Data Model.
  3. Map the concrete class properties to their respective tables and columns.
  4. Make the base class entity abstract.
  5. Use the entities via the DBContext object.

Let's discuss this in detail.

Generating the default Entity Data Model

Let's now generate the Entity Data Model for this table. The Entity Framework will create a single entity, Worker, for this table:entity-framework-db-first-inheritance-relationships-between-entities-img-8

The generated model for the table created for implementing the TPH inheritance

Adding concrete classes to the Entity Data Model

From the application perspective, it would be a much better solution if we have classes such as Employee and Vendor, which are derived from the Worker entity. The Worker class will contain all the common properties, and Employee and Vendor will contain their respective properties. So, let's add new entities for Employee and Vendor. While creating the entity, we can specify the base class entity as Worker, which is as follows:entity-framework-db-first-inheritance-relationships-between-entities-img-9

Adding a new entity in the Entity Data Model using a base class type

Similarly, we will add the Vendor entity to our Entity Data Model, and specify the Worker entity as its base class entity.

Once the entities are generated, our conceptual model will look like this:entity-framework-db-first-inheritance-relationships-between-entities-img-10

The Entity Data Model after adding the derived entities

Next, we have to remove the Salary and HourlyRate properties from the Worker entity, and put them in the Employee and the Vendor entities respectively. So, once the properties are put into the respective entities, our final Entity Data model will look like this:entity-framework-db-first-inheritance-relationships-between-entities-img-11

The Entity Data Model after moving the respective properties into the derived entities

Mapping the concrete class properties to the respective tables and columns

After this, we have to define the column mappings in the derived classes to let the derived classes know which table and column should be used to put the data. We also need to specify the mapping condition.

The Employee entity should save the Salary property's value in the Salary column of the Workers table when the Salary property is Not Null and HourlyRate is Null:entity-framework-db-first-inheritance-relationships-between-entities-img-12

Unlock access to the largest independent learning library in Tech for FREE!
Get unlimited access to 7500+ expert-authored eBooks and video courses covering every tech area you can think of.
Renews at £16.99/month. Cancel anytime

Table mapping and conditions to map the Employee entity to the respective tables

Once this mapping is done, we have to mark the Salary property as Nullable=false in the entity property window. This will let Entity Framework know that if someone is creating an object of the Employee type, then the Salary field is mandatory:entity-framework-db-first-inheritance-relationships-between-entities-img-13

Setting the Employee entity properties as Nullable

Similarly, the Vendor entity should save the HourlyRate property's value in the HourlyRate column of the Workers table when Salary is Null and HourlyRate is Not Null:entity-framework-db-first-inheritance-relationships-between-entities-img-14

Table mapping and conditions to map the Vendor entity to the respective tables

And similar to the Employee class, we also have to mark the HourlyRate property as Nullable=false in the Entity Property window. This will help Entity Framework know that if someone is creating an object of the Vendor type, then the HourlyRate field is mandatory:entity-framework-db-first-inheritance-relationships-between-entities-img-15

Setting the Vendor entity properties to Nullable

Making the base class entity abstract

There is one last change needed to be able to use these models. To be able to use these models, we need to mark the base class as abstract, so that Entity Framework is able to resolve the object of Employee and Vendors to the Workers table.entity-framework-db-first-inheritance-relationships-between-entities-img-16

Making the base class Workers as abstract

This will also be a better model from the application perspective because the Worker entity itself has no meaning from the application domain perspective.

Using the entities via the DBContext object

Now we have our Entity Data Model configured to use the TPH inheritance. Let's try to create an Employee object and a Vendor object, and add them to the database using the TPH inheritance hierarchy:

using (SampleDbEntities db = new SampleDbEntities())
{
Employee employee = new Employee();
employee.FirstName = "Employee 1";
employee.LastName = "Employee 1";
employee.PhoneNumber = "1234567";
employee.Salary = 50000;
employee.EmailID = "employee1@test.com";
Vendor vendor = new Vendor();
vendor.FirstName = "vendor 1";
vendor.LastName = "vendor 1";
vendor.PhoneNumber = "1234567";
vendor.HourlyRate = 100;
vendor.EmailID = "vendor1@test.com";
db.Workers.Add(employee);
db.Workers.Add(vendor);
db.SaveChanges();
}

In the preceding code, we created objects of the Employee and Vendor types, and then added them to the Workers collection using the DbContext object. Entity Framework will look at the mappings of the base entity and the derived entities, will check the mapping conditions and the actual values of the properties, and then push the data to the respective tables. So, let's take a look at the data inserted in the Workers table:entity-framework-db-first-inheritance-relationships-between-entities-img-17

A database snapshot after inserting the data using the Employee and Vendor entities

So, we can see that for our Employee and Vendor models, the actual data is being kept in the same table using Entity Framework's TPH inheritance.

The Table per Concrete Class inheritance

The Table per Concrete Class (TPC) inheritance can be used when the database contains separate tables for all the logical entities, and these tables have some common fields. In our existing example, if there are two separate tables of Employee and Vendor, then the database schema would look like the following:entity-framework-db-first-inheritance-relationships-between-entities-img-18

The database schema showing the TPC inheritance database schema

One of the major problems in such a database design is the duplication of columns in the tables, which is not recommended from the database normalization perspective.

To implement the TPC inheritance, we need to perform the following tasks:

  1. Generate the default Entity Data Model.
  2. Create the abstract class.
  3. Modify the CDSL to cater to the change.
  4. Specify the mapping to implement the TPT inheritance.
  5. Use the entities via the DBContext object.

Generating the default Entity Data Model

Let's now take a look at the generated entities for this database schema:entity-framework-db-first-inheritance-relationships-between-entities-img-19

The default generated entities for the TPC inheritance database schema

Entity Framework has given us separate entities for these two tables. From our application domain perspective, we can use these entities in a better way if all the common properties are moved to a common abstract class. The Employee and Vendor entities will contain the properties specific to them and inherit from this abstract class to use all the common properties.

Creating the abstract class

Let's add a new entity called Worker to our conceptual model and move the common properties into this entity:entity-framework-db-first-inheritance-relationships-between-entities-img-20

Adding a base class for all the common properties

Next, we have to mark this class as abstract from the properties window:entity-framework-db-first-inheritance-relationships-between-entities-img-21

Marking the base class as abstract class

Modifying the CDSL to cater to the change

Next, we have to specify the mapping for these tables. Unfortunately, the Visual Entity Designer has no support for this type of mapping, so we need to perform this mapping ourselves in the EDMX XML file.

The conceptual schema definition language (CSDL) part of the EDMX file is all set since we have already moved the common properties into the abstract class. So, now we should be able to use these properties with an abstract class handle. The problem will come in the storage schema definition language (SSDL) and mapping specification language (MSL).

The first thing that we need to do is to change the SSDL to let Entity Framework know that the abstract class Worker is capable of saving the data in two tables. This can be done by setting the EntitySet name in the EntityContainer tags as follows:

<EntityContainer Name="todoDbModelStoreContainer">
   <EntitySet Name="Employee" EntityType="Self.Employee" Schema="dbo" store_Type="Tables" />
   <EntitySet Name="Vendor" EntityType="Self.Vendor" Schema="dbo" store_Type="Tables" />
</EntityContainer>

Specifying the mapping to implement the TPT inheritance

Next, we need to change the MSL to properly map the properties to the respective tables based on the actual type of object. For this, we have to specify EntitySetMapping. The EntitySetMapping should look like the following:

<EntityContainerMapping StorageEntityContainer="todoDbModelStoreContainer" CdmEntityContainer="SampleDbEntities">

   <EntitySetMapping Name="Workers">
   <EntityTypeMapping TypeName="IsTypeOf(SampleDbModel.Vendor)">
       <MappingFragment StoreEntitySet="Vendor">
       <ScalarProperty Name="HourlyRate" ColumnName="HourlyRate" />
       <ScalarProperty Name="EMailId" ColumnName="EMailId" />
       <ScalarProperty Name="PhoneNumber" ColumnName="PhoneNumber" />
       <ScalarProperty Name="LastName" ColumnName="LastName" />
       <ScalarProperty Name="FirstName" ColumnName="FirstName" />
       <ScalarProperty Name="ID" ColumnName="ID" />
       </MappingFragment>
   </EntityTypeMapping>  

   <EntityTypeMapping TypeName="IsTypeOf(SampleDbModel.Employee)">
       <MappingFragment StoreEntitySet="Employee">
       <ScalarProperty Name="ID" ColumnName="ID" />
       <ScalarProperty Name="Salary" ColumnName="Salary" />
       <ScalarProperty Name="EMailId" ColumnName="EMailId" />
       <ScalarProperty Name="PhoneNumber" ColumnName="PhoneNumber" />
       <ScalarProperty Name="LastName" ColumnName="LastName" />
       <ScalarProperty Name="FirstName" ColumnName="FirstName" />
       </MappingFragment>
   </EntityTypeMapping>
   </EntitySetMapping>
</EntityContainerMapping>

In the preceding code, we specified that if the actual type of object is Vendor, then the properties should map to the columns in the Vendor table, and if the actual type of entity is Employee, the properties should map to the Employee table, as shown in the following screenshot:entity-framework-db-first-inheritance-relationships-between-entities-img-22

After EDMX modifications, the mapping are visible in Visual Entity Designer

If we now open the EDMX file again, we can see the properties being mapped to the respective tables in the respective entities. Doing this mapping from Visual Entity Designer is not possible, unfortunately.

Using the entities via the DBContext object

Let's use these "entities from our code:

using (SampleDbEntities db = new SampleDbEntities())
{
    Employee employee = new Employee();
    employee.FirstName = "Employee 1";
    employee.LastName = "Employee 1";
    employee.PhoneNumber = "1234567";
    employee.Salary = 50000;
    employee.EMailId = "employee1@test.com";

    Vendor vendor = new Vendor();
    vendor.FirstName = "vendor 1";
    vendor.LastName = "vendor 1";
    vendor.PhoneNumber = "1234567";
    vendor.HourlyRate = 100;
    vendor.EMailId = "vendor1@test.com";

    db.Workers.Add(employee);
    db.Workers.Add(vendor);
    db.SaveChanges();
}

In the preceding code, we created objects of the Employee and Vendor types and saved them using the Workers entity set, which is actually an abstract class. If we take a look at the inserted database, we will see the following:entity-framework-db-first-inheritance-relationships-between-entities-img-23

Database snapshot of the inserted data using TPC inheritance

From the preceding screenshot, it is clear that the data is being pushed to the respective tables.

The insert operation we saw in the previous code is successful but there will be an exception in the application. This exception is because when Entity Framework tries to access the values that are in the abstract class, it finds two records with same ID, and since the ID column is specified as a primary key, two records with the same value is a problem in this scenario. This exception clearly shows that the store/database generated identity columns will not work with the TPC inheritance.

If we want to use the TPC inheritance, then we either need to use GUID based IDs, or pass the ID from the application, or perhaps use some database mechanism that can maintain the uniqueness of auto-generated columns across multiple tables.

Choosing the inheritance strategy

Now that we know about all the inheritance strategies supported by Entity Framework, let's try to analyze these approaches. The most important thing is that there is no single strategy that will work for all the scenarios. Especially if we have a legacy database. The best option would be to analyze the application requirements and then look at the existing table structure to see which approach is best suited.

The Table per Class Hierarchy inheritance tends to give us denormalized tables and have redundant columns. We should only use it when the number of properties in the derived classes is very less, so that the number of redundant columns is also less, and this denormalized structure will not create problems over a period of time.

Contrary to TPH, if we have a lot of properties specific to derived classes and only a few common properties, we can use the Table per Concrete Class inheritance. However, in this approach, we will end up with some properties being repeated in all the tables. Also, this approach imposes some limitations such as we cannot use auto-increment identity columns in the database.

If we have a lot of common properties that could go into a base class and a lot of properties specific to derived classes, then perhaps Table per Type is the best option to go with.

In any case, complex inheritance relationships that become unmanageable in the long run should be avoided. One alternative could be to have separate domain models to implement the application logic in an object-oriented manner, and then use mappers to map these domain models to Entity Framework's generated entity models.

Summary

In this article, we looked at the various types of inheritance relationship using Entity Framework. We saw how these inheritance relationships can be implemented, and some guidelines on which should be used in which scenario.

Resources for Article:


Further resources on this subject: