Entity Framework Core – Inheritance – Table-Per-Type (TPT) Is Not Supported, Is It? (Part 1 – Code First)

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 diesem Artikel:

pg
Pawel Gerr ist Architekt und Consultant bei Thinktecture. Er hat sich auf .NET Core Backends spezialisiert und kennt Entity Framework von vorne bis hinten.
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.

Kostenloser
Newsletter

Aktuelle Artikel, Screencasts, Webinare und Interviews unserer Experten für Sie

Verpassen Sie keine Inhalte zu Angular, .NET Core, Blazor, Azure und Kubernetes und melden Sie sich zu unserem kostenlosen monatlichen Dev-Newsletter an.

Newsletter Anmeldung
Diese Artikel könnten Sie interessieren
Database Access with Sessions
.NET
kp_300x300

Data Access in .NET Native AOT with Sessions

.NET 8 brings Native AOT to ASP.NET Core, but many frameworks and libraries rely on unbound reflection internally and thus cannot support this scenario yet. This is true for ORMs, too: EF Core and Dapper will only bring full support for Native AOT in later releases. In this post, we will implement a database access layer with Sessions using the Humble Object pattern to get a similar developer experience. We will use Npgsql as a plain ADO.NET provider targeting PostgreSQL.
15.11.2023
Old computer with native code
.NET
kp_300x300

Native AOT with ASP.NET Core – Overview

Originally introduced in .NET 7, Native AOT can be used with ASP.NET Core in the upcoming .NET 8 release. In this post, we look at the benefits and drawbacks from a general perspective and perform measurements to quantify the improvements on different platforms.
02.11.2023
.NET
kp_300x300

Optimize ASP.NET Core memory with DATAS

.NET 8 introduces a new Garbage Collector feature called DATAS for Server GC mode - let's make some benchmarks and check how it fits into the big picture.
09.10.2023
.NET CORE
pg

Incremental Roslyn Source Generators: High-Level API – ForAttributeWithMetadataName – Part 8

With the version 4.3.1 of Microsoft.CodeAnalysis.* Roslyn provides a new high-level API - the method "ForAttributeWithMetadataName". Although it is just 1 method, still, it addresses one of the biggest performance issue with Source Generators.
16.05.2023
AI
favicon

Integrating AI Power into Your .NET Applications with the Semantic Kernel Toolkit – an Early View

With the rise of powerful AI models and services, questions come up on how to integrate those into our applications and make reasonable use of them. While other languages like Python already have popular and feature-rich libraries like LangChain, we are missing these in .NET and C#. But there is a new kid on the block that might change this situation. Welcome Semantic Kernel by Microsoft!
03.05.2023
.NET
sg

.NET 7 Performance: Regular Expressions – Part 2

There is this popular quote by Jamie Zawinski: Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems."

In this second article of our short performance series, we want to look at the latter one of those problems.
25.04.2023