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:

Entity Framework Core – Inheritance – Table-Per-Type (TPT) Is Not Supported, Is It? (Part 1 – Code First)
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.

Diese Artikel könnten Sie interessieren
.NET
Incremental Roslyn Source Generators in .NET 6: Adapt Code Generation Based on Project Dependencies – Part 5

Incremental Roslyn Source Generators in .NET 6: Adapt Code Generation Based on Project Dependencies – Part 5

The Roslyn Source Generator, implemented in the previous articles of the series, emits some C# code without looking at the dependencies of the current .NET (Core) project. In this article our DemoSourceGenerator should implement a JsonConverter, but only if the corresponding library (e.g. Newtonsoft.Json) is referenced by the project.
08.07.2022
Unterschiede
.NET
Blazor WebAssembly vs. Blazor Server – Welche Unterschiede gibt es und wann wähle ich was?

Blazor WebAssembly vs. Blazor Server – Welche Unterschiede gibt es und wann wähle ich was?

Das Blazor Framework von Microsoft gibt es inzwischen in drei "Geschmacksrichtungen". Die erste ist Blazor WebAssembly, die zweite Blazor Server, und zu guter Letzt gibt es noch Blazor Hybrid. In diesem Artikel wollen wir uns die zwei "echten", also Browser-basierten, Web-Anwendungs-Szenarien WebAssembly und Server anschauen.
04.07.2022
Three different textured walls
.NET
Dependency Injection Scopes in Blazor

Dependency Injection Scopes in Blazor

The dependency injection system is a big part of how modern ASP.NET Core works internally: It provides a flexible solution for developers to structure their projects, decouple their dependencies, and control the lifetimes of the components within an application. In Blazor - a new part of ASP.NET Core - however, the DI system feels a bit odd, and things seem to work a bit differently than expected. This article will explain why this is not only a feeling but indeed the case in the first place and how to handle the differences in order to not run into problems later on.
31.05.2022
.NET
Asynchrone Operationen: Blazor WebAssembly für Angular-Entwickler – Teil 5 [Screencast]

Asynchrone Operationen: Blazor WebAssembly für Angular-Entwickler – Teil 5 [Screencast]

Eine Webanwendung will natürlich auch mit Daten gefüttert werden. Doch diese müssen irgendwo her kommen. Nichts liegt näher als diese von einer Web API zu laden. Dieser Screencast zeigt, wie asynchrone Operationen in Blazor funktionieren und welche gravierenden Unterschiede es zu Angular gibt.
26.05.2022
.NET
Typings: Blazor WebAssembly für Angular-Entwickler – Teil 4 [Screencast]

Typings: Blazor WebAssembly für Angular-Entwickler – Teil 4 [Screencast]

C# und TypeScript entstammen der Feder der selben Person. Doch sind sie deshalb auch gleich? In diesem Teil der Screencast-Serie erfahren Sie, wie mit Typen in den beiden Programmiersprachen verfahren wird und welche Unterschiede es gibt.
19.05.2022
.NET
Bindings: Blazor WebAssembly für Angular-Entwickler – Teil 3 [Screencast]

Bindings: Blazor WebAssembly für Angular-Entwickler – Teil 3 [Screencast]

Wer Komponenten einsetzt, steht früher oder später vor der Fragestellung, wie man Daten an die Komponente übergibt oder auf Ereignisse einer Komponente reagiert. In diesem Screencast wird gezeigt wie Bindings bei Komponenten funktionieren, also wie eine Komponente Daten von außerhalb benutzen und Rückmeldung bei Aktionen geben kann.
12.05.2022