In this Article

With O/R mappers there are a few patterns how a class hierarchy can be mapped to a relational database. The most popular ones are the Table-Per-Hierarchy (TPH) and the Table-Per-Type (TPT) patterns. The Entity Framework Core 2.x (EF Core) officially supports the Table-per-Hierarchy pattern only. The support of Table-per-Type is in the backlog of the Entity Framework team, i.e. it is not (officially) supported yet. Nevertheless, you can use TPT with the current version of EF Core. The usability is not ideal but acceptable. Especially, if you have an existing database using TPT then this short blog post series may give you an idea how to migrate to EF Core.

In the 1st part we will set up 2 EF Core models incl. database migrations for TPH and TPT using code first approach. In the 2nd part we are going to use the database first approach.

Remarks: this blog post is not about what approach is the best for your solution :)

All demos are on Github.

Business data model

In both cases we are going to use the following business data model. For our outward-facing interface, we are using DTOs. We have a PersonDto with 3 fields and 2 derived classes CustomerDto and EmployeeDto, both having 1 additional field.

public class PersonDto
{
  public Guid Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
}

public class CustomerDto : PersonDto
{
  public DateTime DateOfBirth { get; set; }
}

public class EmployeeDto : PersonDto
{
  public decimal Turnover { get; set; }
}

Table-Per-Hierarchy (TPH)

Now, let's look at the solution to have internal entities based on TPH. At first, we need to define the entity classes. Thanks to the native support of TPH and the very simple data model the entities are identical to the DTOs.

public class PersonTph
{
  public Guid Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
}

public class CustomerTph : PersonTph
{
  public DateTime DateOfBirth { get; set; }
}

public class EmployeeTph : PersonTph
{
  public decimal Turnover { get; set; }
}

We can implement the database context to be able to access customers and employees like this:

public class TphDbContext : DbContext
{
  public DbSet<PersonTph> People { get; set; }
  public DbSet<CustomerTph> Customers { get; set; }
  public DbSet<EmployeeTph> Employees { get; set; }

  public TphDbContext(DbContextOptions<TphDbContext> options)
    : base(options)
  {
  }
}

And for the sake of completion we will be using Entity Framework Core Migrations to create and update the database schema. For that we execute the following command:

dotnet ef migrations add Initial_TPH_Migration -p ./../../EntityFramework.Demo.csproj -s ./../../EntityFramework.Demo.csproj -c TphDbContext -o ./TphModel/CodeFirst/Migrations

As expected we have 1 table with all fields from person, customer and employee and 1 additional column Descriminator, so EF Core is able to differentiate customers from employees.

public partial class Initial_TPH_Migration : Migration
{
  protected override void Up(MigrationBuilder migrationBuilder)
  {
    migrationBuilder.CreateTable("People",
                        table => new
                              {
                                Id = table.Column<Guid>(nullable: false),
                                FirstName = table.Column<string>(nullable: true),
                                LastName = table.Column<string>(nullable: true),
                                DateOfBirth = table.Column<DateTime>(nullable: true),
                                Turnover = table.Column<decimal>(nullable: true),
                                Discriminator = table.Column<string>(nullable: false)
                              },
                        constraints: table => table.PrimaryKey("PK_People", x => x.Id));
  }

  protected override void Down(MigrationBuilder migrationBuilder)
  {
    migrationBuilder.DropTable("People");
  }
}

The usage of TPH is nothing special, we just use the appropriate property on the TphDbContext.

TphDbContext ctx = ...

// Create a customer
ctx.Customers.Add(new CustomerTph()
          {
            Id = Guid.NewGuid(),
            FirstName = "John",
            LastName = "Foo",
            DateOfBirth = new DateTime(1980, 1, 1)
          });

// Fetch all customers
var customers = ctx.Customers
     .Select(c => new CustomerDto()
     {
         Id = c.Id,
         FirstName = c.FirstName,
         LastName = c.LastName,
         DateOfBirth = c.DateOfBirth
     })
     .ToList();

Table-Per-Type (TPT) 

Ok, that was easy. Now, how can a solution for TPT look like? With the absence of native support for TPT the entities do not derive from each other but reference each other. The field Id of customer and employee is the primary key and a foreign key pointing to person. The structure of the entities is very similar to the database schema of the TPT pattern.

public class PersonTpt
{
  public Guid Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
}

public class CustomerTpt
{
  [ForeignKey(nameof(Person))]
  public Guid Id { get; set; } // PK and FK pointing to PersonTpt
  public PersonTpt Person { get; set; }

  public DateTime DateOfBirth { get; set; }
}

public class EmployeeTpt
{
  [ForeignKey(nameof(Person))]
  public Guid Id { get; set; } // PK and FK pointing to PersonTpt
  public PersonTpt Person { get; set; }

  public decimal Turnover { get; set; }
}

The database context of TPT is identical to the one of TPH.

public class TptDbContext : DbContext
{
  public DbSet<PersonTpt> People { get; set; }
  public DbSet<CustomerTpt> Customers { get; set; }
  public DbSet<EmployeeTpt> Employees { get; set; }

  public TptDbContext(DbContextOptions<TptDbContext> options)
    : base(options)
  {
  }
}

Next, we will create an EF Core migration with the following command

dotnet ef migrations add Initial_TPT_Migration -p ./../../EntityFramework.Demo.csproj -s ./../../EntityFramework.Demo.csproj -c TptDbContext -o ./TptModel/CodeFirst/Migrations

The migration creates 3 tables with correct columns, primary keys and foreign keys.

public partial class Initial_TPT_Migration : Migration
{
  protected override void Up(MigrationBuilder migrationBuilder)
  {
    migrationBuilder.CreateTable("People", 
                        table => new
                                 {
                                    Id = table.Column<Guid>(nullable: false),
                                    FirstName = table.Column<string>(nullable: true),
                                    LastName = table.Column<string>(nullable: true)
                                  },
                        constraints: table => table.PrimaryKey("PK_People", x => x.Id));

    migrationBuilder.CreateTable("Customers",
                        table => new
                              {
                                Id = table.Column<Guid>(nullable: false),
                                DateOfBirth = table.Column<DateTime>(nullable: false)
                              },
                        constraints: table =>
                                {
                                  table.PrimaryKey("PK_Customers", x => x.Id);
                                  table.ForeignKey("FK_Customers_People_Id",
                                                    x => x.Id,
                                                    "People",
                                                    "Id",
                                                    onDelete: ReferentialAction.Cascade);
                                });

    migrationBuilder.CreateTable("Employees",
                        table => new
                              {
                                Id = table.Column<Guid>(nullable: false),
                                Turnover = table.Column<decimal>(nullable: false)
                              },
                        constraints: table =>
                                {
                                  table.PrimaryKey("PK_Employees", x => x.Id);
                                  table.ForeignKey("FK_Employees_People_Id",
                                                    x => x.Id,
                                                    "People",
                                                    "Id",
                                                    onDelete: ReferentialAction.Cascade);
                                });
  }

  protected override void Down(MigrationBuilder migrationBuilder)
  {
    migrationBuilder.DropTable("Customers");
    migrationBuilder.DropTable("Employees");
    migrationBuilder.DropTable("People");
  }
}

The biggest difference - compared to TPH - is in the usage of the entities. To get to the fields of the person (i.e. the base type) we have to use the navigational property Person. This may seem cumbersome at first, but it is not a hindrance in practice.

TptDbContext ctx = ...

// Fetch all customers
var customers = ctx.Customers
              .Select(c => new CustomerDto()
                      {
                        Id = c.Id,
                        FirstName = c.Person.FirstName,
                        LastName = c.Person.LastName,
                        DateOfBirth = c.DateOfBirth
                      })
              .ToList();

// Create a customer
ctx.Customers.Add(new CustomerTpt()
                  {
                    Person = new PersonTpt()
                             {
                                Id = Guid.NewGuid(),
                                FirstName = "John",
                                LastName = "Foo"
                             },
            DateOfBirth = new DateTime(1980, 1, 1)
          });

 Voila!

Conclusion

With Entity Framework Core we can use both the Table-Per-Hierarchy and Table-Per-Type patterns. At least with a code first approach. Whether and how the patterns are applicable using the database first approach we will see in the next blog post.

Stay tuned.

Related Articles

entity framework core
Do Not Waste Performance by Not Using Temp Tables With Entity Framework Core
It has been a while since I released my article about the usage of temp tables in Entity Framework (v6). Meanwhile, Microsoft has released a completely rewritten version of its O/R mapper so my old approach is no longer applicable. But before we learn about a new one, let us…
Pawel Gerr
entity framework core
Better Entity Framework Core Performance by Reading Execution Plans
Both a LINQ query and an SQL statement are descriptions that state which data should be fetched, but not how.. Sure, when reading LINQ or SQL, we can make assumptions about the performance but not in every case. Some queries are either too fancy or too big to grasp, so our…
Pawel Gerr
entity framework core
Unnecessary Fuzzy Searches may hurt your Entity Framework Core Performance
After talking about performance issues like N+1 Queries and the Cartesian Explosion that made its comeback in Entity Framework Core 3, we will today look at a performance issue that is not tied to any Entity Framework version but is rather a general one. What do I mean by…
Pawel Gerr
entity framework core
The performance issue "Cartesian Explosion" made its comeback in Entity Framework Core 3
In Entity Framework Core 3.0/3.1 the SQL statement generation underwent significant changes. As we have seen in the previous post these changes removed both the implicit client-side evaluation and the N+1 Query Problem (which is good!). Unfortunately, these changes (re)introduced…
Pawel Gerr