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

In the previous post we have created 2 Entity Framework Core (EF Core) models with a code first approach. One model was using the Table-per-Hierarchy (TPH) pattern and the other one Table-per-Type (TPT). In this post we want to approach a more common scenario we see in customer projects: we are using the database first approach now. All demos are on Github.

In diesem Artikel:

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

Business data model

The business data model is the same as in the previous post. We have 3 DTOs: Person, Customer and Employee.

				
					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)

We start with the Table-per-Hierarchy pattern. Given is a table People containing all columns from all DTOs incl. 1 column Discriminator to be able to distinguish the customers from employees.

Remark: we are using nvarchar(max) for the sake of simplicity.

				
					TABLE People
(
    Id uniqueidentifier NOT NULL PRIMARY KEY,
    FirstName nvarchar(max) NULL,
    LastName nvarchar(max) NULL,
    DateOfBirth datetime2(7) NULL,
    Turnover decimal(18, 2) NULL,
    Discriminator nvarchar(max) NOT NULL
)
				
			

With the following command we let EF Core scaffold the entities (or rather the entity) and the database context:

				
					dotnet ef dbcontext scaffold "Server=(local);Database=TphDemo;Trusted_Connection=True" Microsoft.EntityFrameworkCore.SqlServer -f -c ScaffoldedTphDbContext --context-dir ./TphModel/DatabaseFirst -o ./TphModel/DatabaseFirst -p ./../../EntityFramework.Demo.csproj -s ./../../EntityFramework.Demo.csproj
				
			

The result is not the one we might have expected but is pretty reasonable. The scaffolding creates just 1 entity People with all fields in it because there is no way for EF Core to guess that the table contains 3 entities and not just 1.

				
					public class People
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public decimal? Turnover { get; set; }
    public string Discriminator { get; set; }
}

				
			

First, let’s fix the name of the entity because the name should be Person not People.

For that we create a class that does the pluralization/singularization and register it with the so-called IDesignTimeServices. The implementation of IDesignTimeServices doesn’t need any kind of registration, EF Core will find it automatically. The actual pluralization/singularization will be done by the 3rd party-library Inflector.

				
					public class Pluralizer : IPluralizer
{
    public string Pluralize(string identifier)
    {
        // Inflector needs some help with "People" otherwise we get "Peoples"
        if (identifier == "People")
            return identifier;

        return Inflector.Inflector.Pluralize(identifier);
  }

    public string Singularize(string identifier)
    {
        return Inflector.Inflector.Singularize(identifier);
    }
}

public class DesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        services.AddSingleton<IPluralizer, Pluralizer>();
    }
}
				
			

Now, the generated entity gets the name Person – but to make the model right we have to split the class in 3, manually. After manual adjustments we have 2 options: switch to code first approach or adjust the classes manually after every scaffolding to apply the changes from database. The adjusted code is virtually identical to the one of code first approach but this time the Descriminator is defined explicitly.

Remark: I’ve renamed Person to PersonTph so the names are the same as in the previous blog post.

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

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

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

The generated database context needs some adjustments as well because DbSets for customers and employees are missing and the field Discriminator has to be defined as one.

				
					public partial class ScaffoldedTphDbContext : DbContext
{
    public virtual DbSet<Person> People { get; set; }

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>(entity =>
                      {
                        entity.Property(e => e.Id).ValueGeneratedNever();
                        entity.Property(e => e.Discriminator).IsRequired();
                      });
    }
}
				
			

As with the entities, the only change – compared to code first approach – is the explicit definition of the Discriminator.

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

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

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<PersonTph>(entity => entity.Property(e => e.Id).ValueGeneratedNever());
    modelBuilder.Entity<PersonTph>()
            .HasDiscriminator(person => person.Discriminator)
            .HasValue<PersonTph>(nameof(PersonTph))
            .HasValue<CustomerTph>(nameof(CustomerTph))
            .HasValue<EmployeeTph>(nameof(EmployeeTph));
  }
}
				
			

Table-per-Type (TPT)

Having a database using the TPT pattern we start off 3 with tables:

				
					TABLE People
(
    Id uniqueidentifier NOT NULL PRIMARY KEY,
    FirstName nvarchar(max) NULL,
    LastName nvarchar(max) NULL
)

TABLE Customers
(
    Id uniqueidentifier NOT NULL
        PRIMARY KEY
        FOREIGN KEY REFERENCES People (Id),
    DateOfBirth datetime2(7) NOT NULL
)

TABLE Employees
(
    Id uniqueidentifier NOT NULL
        PRIMARY KEY
        FOREIGN KEY REFERENCES People (Id),
    Turnover [decimal](18, 2) NOT NULL
)
				
			

With the following command we create the entities and the database context:

				
					dotnet ef dbcontext scaffold "Server=(local);Database=TptDemo;Trusted_Connection=True" Microsoft.EntityFrameworkCore.SqlServer -f -c ScaffoldedTptDbContext --context-dir ./TptModel/DatabaseFirst -o ./TptModel/DatabaseFirst -p ./../../EntityFramework.Demo.csproj -s ./../../EntityFramework.Demo.csproj
				
			

The scaffolder generates 3 entities that are almost correct. The only flaw is the name of the navigational property IdNavigation pointing to the base class Person.

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

    public Customer Customer { get; set; }
    public Employee Employee { get; set; }
}

public partial class Employee
{
    public Guid Id { get; set; }
    public decimal Turnover { get; set; }

    public Person IdNavigation { get; set; }
}

public partial class Customer
{
    public Guid Id { get; set; }
    public DateTime DateOfBirth { get; set; }

    public Person IdNavigation { get; set; }
}
				
			

Luckily, this issue is very easy to fix by implementing ICandidateNamingService and registering it with IDesignTimeServices.

				
					public class CustomCandidateNamingService : CandidateNamingService
{
    public override string GetDependentEndCandidateNavigationPropertyName(IForeignKey foreignKey)
    {
        if(foreignKey.PrincipalKey.IsPrimaryKey())
            return foreignKey.PrincipalEntityType.ShortName();

        return base.GetDependentEndCandidateNavigationPropertyName(foreignKey);
  }
}

public class DesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        services.AddSingleton<IPluralizer, Pluralizer>()
            .AddSingleton<ICandidateNamingService, CustomCandidateNamingService>();
    }
}

				
			

After re-running the scaffolder, we get the expected results:

				
					public class Customer
{
    public Guid Id { get; set; }
    public DateTime DateOfBirth { get; set; }

    public Person Person { get; set; }
}
				
			
				
					public partial class Employee
{
    public Guid Id { get; set; }
    public decimal Turnover { get; set; }

    public Person Person { get; set; }
}
				
			

The last part is the database context. Fortunately, we don’t have to change anything.

				
					public partial class ScaffoldedTptDbContext : DbContext
{
  public virtual DbSet<Customer> Customers { get; set; }
  public virtual DbSet<Employee> Employees { get; set; }
  public virtual DbSet<Person> People { get; set; }

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

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Customer>(entity =>
                        {
                          entity.Property(e => e.Id).ValueGeneratedNever();

                          entity.HasOne(d => d.Person)
                              .WithOne(p => p.Customer)
                              .HasForeignKey<Customer>(d => d.Id);
                        });

    modelBuilder.Entity<Employee>(entity =>
                        {
                          entity.Property(e => e.Id).ValueGeneratedNever();

                          entity.HasOne(d => d.Person)
                              .WithOne(p => p.Employee)
                              .HasForeignKey<Employee>(d => d.Id);
                        });

    modelBuilder.Entity<Person>(entity => 
                        {
                          entity.Property(e => e.Id).ValueGeneratedNever());
                        });
  }
}
				
			

With TPT we can but don’t have to switch to code first approach because we can regenerate the entities and the database context at any time.

Conclusion

Database first approach works best with TPT, with TPH not so much because a relational database knows nothing about any inheritance. With TPT there is just one minor issue but thanks to the great job of the Entity Framework team we can adjust the code generation as we want without the need to copy all the code of Entity Framework Core.

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