Posted on: 2013-12-10

Business logic can have model classes that inherit an other class. The data of the instanced class and the inherited class might be saved in the database when the Object Context is saved. Entity framework lets you choose the strategy you desire to save them into the database.

Three strategies are available : TPH, TPT and TPC inheritance.

Table per hierarchy (TPH) Inheritance Mapping Strategy

This is the simplest strategy. It takes all properties of the base class and all properties of the inherited class and merge them into a single table. This mean if 10 classes inherit a single class, all properties of these 10 classes plus the one of the inheritance one will be merged in 1 table.

For example you have 3 classes. Two sub-classes that inherit a single base class.

public class Book { public string ISBN { get; set; } public int PageCount { get; set; } }

public class Magazine : Book { public string MagazineProperty { get; set; } }

public class Encyclopedia:Book { public string EncyclopediaProperty { get; set; } } 

The class diagram of these 3 classes is the following:

This will result to a single table with all property nullable for those who are from subclass.

To create a short test, simply add these three classes to a .Net project. Then, you need to setup the database context.

public class TestContext:DbContext { 
  public TestContext() : base("TestContext") {
  } 
  public DbSet<Magazine> Magazines { get; set; } 
  public DbSet<Encyclopedia> Encyclopedia { get; set; }

  protected override void OnModelCreating(DbModelBuilder modelBuilder) { 
    modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); 
    modelBuilder.Entity<Book>().HasKey(d => d.ISBN);
  } 
} 

Do no forget to add Entity Framework with Nuget and setup the web.config with a good connection string. To get entity framework you can do a search inside Nuget manager or in the package console.

 Menu Project>Manage NuGet Packages Select the Online tab Select the EntityFramework package Click Install 

or

 PM> install-package entityframework 

The configuration file should have a config section (which are added from Nuget) and the connection string to be setup to the Sql instance you have and the database you want to use.

 <configuration> <configSections> <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" /> </configSections> <connectionStrings> <add name="TestContext" connectionString="Data Source=PATRICK-I7\\SQLEXPRESS;Initial Catalog=TestContextDatabase;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/> </connectionStrings> ... 

This is the most basic setup possible, we simply set the primary key to the base class. We do not set anything for sub-classes . Then, we need to let Entity Framework create the database. For that, just instanciate the dbcontext and do something to the query. We could also generate the database with the migration tool but for simplicity, let's just add a new Encyclopedia in the main method.

using (var context = new DataAccessLayer.TestContext()) { 
  context.Encyclopedia.Add(new Models.Encyclopedia { ISBN = "123" }); 
  context.SaveChanges(); 
} 

Here is how the table looks:

In fact, the table has an additional value which is a discriminator. This column is used by Entity Framework to know which sub class is required to be instanced. It's possible to edit the string, but by default it's the class name.

This mapping strategy is good for performance and simplicity. It's also the default used by Entity Framework.

Table per type (TPT) Inheritance Mapping Strategy

All types are mapped to its own table. This produce a normalized database, while TPH doesn't. For the same example as above, with TPH, the database would have 3 tables.

Entity Framework performs more inner join to be able to know which table to use for subclass when information are gathered from the database. This result to be less performing than the previous approach. Nevertheless, tables are more clean because they doesn't have a huge amount of nullable of property.

To configure table per type (TPT) it requires to set the table for the subclass.

public class TestContext : DbContext { 
  public TestContext() : base("TestContext") { }

  public DbSet<Book> Book { get; set; } 
  public DbSet<Magazine> Magazines { get; set; } 
  public DbSet<Encyclopedia> Encyclopedia { get; set; }

  protected override void OnModelCreating(DbModelBuilder modelBuilder) { 
    modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); 
    modelBuilder.Entity<Magazine>().ToTable("Magazine"); 
    modelBuilder.Entity<Encyclopedia>().ToTable("Encyclopedia"); 
    modelBuilder.Entity<Book>().ToTable("Book").HasKey(d => d.ISBN); 
  } 
} 

If we delete the database and run again the code, the database is created with two tables.

Table per concrete type (TPC) Inheritance Mapping Strategy

Tablet per concrete type, also know as TPC, create a single class per subclass. Every properties of the inherited class are set into each of the subclass table. This lead to have duplicated structure. If you have 2 classes that inherit one class, the inherited field will be in inside the two tables.

As you can see in the example above, the ISBN and PageCount are repeated in both of the sub classes' table. You can also notice that they are no table for the base class. The reason is that all fields of the base are already in all sub classes.

The configuration is more complex.

public class TestContext:DbContext { 
  public TestContext() : base("TestContext") {
  } 

  public DbSet<Magazine> Magazines { get; set; } 
  public DbSet<Encyclopedia> Encyclopedia { get; set; }

  protected override void OnModelCreating(DbModelBuilder modelBuilder) { 
    modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); 
    modelBuilder.Entity<Magazine>().Map(m => { m.MapInheritedProperties(); m.ToTable("Magazine"); 
    });

    modelBuilder.Entity<Encyclopedia>().Map(m => { 
      m.MapInheritedProperties(); 
      m.ToTable("Encyclopedia"); 
    });
    
    modelBuilder.Entity<Magazine>().HasKey(d => d.ISBN); 
    modelBuilder.Entity<Encyclopedia>().HasKey(d => d.ISBN); 
  } 
} 

The code generated in SQL is complex. It has sub query with union to know from which table to take its data. This result to have bad performance for heavy inheritance model. TPC is good for deep inheritance, TPH is good for small entity with not a lot of property and TPT is good for small inheritance level with many properties. Personally, I lean for TPT most of the time. Having a normalize database is important in most enterprise and performance is not that bad if the inheritance is only one level.