A new feature that was added on Entity Framework Core 5, it’s the possibility to create a Table-per-Type (TPT) mapping. In this article, I explain the difference between the Table-per-Hierarchy (TPH) mapping and the Table-per-Type (TPT) mapping.
By default, EF Core maps an inheritance hierarchy of .NET types to a single table in the database, to store the data for all types in the hierarchy, and a discriminator column is used to identify which type each row represents, this is known as Table-per-Hierarchy (TPH) mapping.
With EF Core 5, it’s also possible to map each .NET type in an inheritance hierarchy to a different table, and this is known as Table-per-Type (TPT) mapping. With this new functionality, EF Core creates a base table in the database and create a specific table for each derived table.
To show the differences between the TPH and the TPT mapping, I’ve created a console application using .NET Core 5.0, and I’ve installed the packages:
Microsoft.EntityFrameworkCore.Design 5.0.3
Microsoft.EntityFrameworkCore.SqlServer 5.0.3
Microsoft.EntityFrameworkCore.Tools 5.0.3
On the Startup class, in the method OnConfiguring, I enabled the option to Log into the console all the queries generated by EF Core:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
const string strConnection ="Data source=(localdb)\\mssqllocaldb;
Initial Catalog=EntityFrameworkCore5Examples;
Integrated Security=true;
pooling=true;";
optionsBuilder
.UseSqlServer(strConnection)
.EnableSensitiveDataLogging()
.LogTo(Console.WriteLine, LogLevel.Information);
}Table-per-Hierarchy (TPH) Mapping
For demonstration purpose, I’ve created three entities, and I will use them to explain the Table-per-Hierarchy (TPH) mapping and the Table-per-Type (TPT) mapping. These are the classes:
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
}
public class User : Person
{
public string UserName { get; set; }
}
public class Client : Person
{
public string Email { get; set; }
}The class “Person” is the base class
The class “User” inherits from Person
The class “Client” also inherits from Person
I’ve also created the DbSets in the Startup class:
public class ApplicationContext : DbContext
{
public DbSet<Person> People { get; set; }
public DbSet<Client> Clients { get; set; }
public DbSet<User> Users { get; set; }
}When the application run, this is the SQL Script that will be generated by EF Core:
CREATE TABLE [People] (
[Id] int NOT NULL IDENTITY,
[Name] nvarchar(max) NULL,
[Discriminator] nvarchar(max) NOT NULL,
[Email] nvarchar(max) NULL,
[UserName] nvarchar(max) NULL,
CONSTRAINT [PK_People] PRIMARY KEY ([Id])
);A single table will be created, containing a field named “Discriminator”, and by this column, it is possible to discriminate between different types:
Now let’s create a LINQ query to return all the users:
static void SelectUsers()
{
using var db = new ApplicationContext();
var users = db.Users.ToList();
foreach (var user in users)
{
Console.WriteLine($"User: {user.Name} - Username:
{user.UserName}");
}
}The SQL query that will be generated for this query will be like this:
SELECT [p].[Id], [p].[Discriminator], [p].[Name], [p].[UserName]
FROM [People] AS [p]
WHERE [p].[Discriminator] =N'User'Note that in the SQL query, there is a filter to return all People where the Discriminator field is equal to “User”. This is the result of this query:
TPH Doesn’t Allow Null Values on Properties
Note that with this approach (TPH), it is not possible to have a required property in the derived entities (Client or User), because this is a shared table, and for this reason, all the properties in the derived entities need to allow null values. For example, if we set a configuration using DataAnnotations to the property Email in the Client class to be required, when the application is creating the database, an exception will be thrown:
public class Client : Person{
[Required]
public string Email { get; set; }
}System.InvalidOperationException: 'Cannot use table 'People' for entity type 'Person' since it is being used for entity type 'Person' and potentially other entity types, but there is no linking relationship. Add a foreign key to 'Person' on the primary key properties and pointing to the primary key on another entity typed mapped to 'People'.'Table-per-Type (TPT) Mapping
Now let’s see an example using table-per-type mapping. For this example, we will use the same three entities: Person (base class), User and Client (derived classes).
There are two ways to create a TPT mapping, and with this configuration, EF Core will create one table for each entity. One way of doing it is using the DataAnnotation in the name of the classes, for example:
[Table("People")]
public class Person
{
public int Id { get; set; }
public string Name { get; set; }
}
[Table("Users")]
public class User : Person
{
public string UserName { get; set; }
}
[Table("Clients")]
public class Client : Person
{
public string Email { get; set; }
}Another way is configuring the tables using Fluent API:
public class PersonConfiguration : IEntityTypeConfiguration<Person>
{
public void Configure(EntityTypeBuilder<Person> builder)
{
builder.ToTable("People");
}
}
public class ClientConfiguration : IEntityTypeConfiguration<Client>
{
public void Configure(EntityTypeBuilder<Client> builder)
{
builder.ToTable("Clients");
}
}
public class UserConfiguration : IEntityTypeConfiguration<User>
{
public void Configure(EntityTypeBuilder<User> builder)
{
builder.ToTable("Users");
}
}When the configuration is made by Fluent API, it’s also necessary to add the configuration on the Startup class, for EF Core to apply the configuration from all IEntityTypeConfiguration<TEntity>. This configuration can be done using the “ApplyConfigurationsFromAssembly”, this way EF Core will do the discovery for all configurations which are implementing the IEntityTypeConfiguration:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfigurationsFromAssembly(typeof(ApplicationContext).Assembly);
}When the application run, this is the SQL Script that will be generated by EF Core:
CREATE TABLE [People] (
[Id] int NOT NULL IDENTITY,
[Name] nvarchar(max) NULL,
CONSTRAINT [PK_People] PRIMARY KEY ([Id])
);
CREATE TABLE [Clients] (
[Id] int NOT NULL,
[Email] nvarchar(max) NULL,
CONSTRAINT [PK_Clients] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Clients_People_Id] FOREIGN KEY ([Id]) REFERENCES
[People] ([Id]) ON DELETE NO ACTION
);
CREATE TABLE [Users] (
[Id] int NOT NULL,
[UserName] nvarchar(max) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Users_People_Id] FOREIGN KEY ([Id]) REFERENCES
[People] ([Id]) ON DELETE NO ACTION
);Note that now we have three tables, each one for each entity. Each table has its own properties, and there is a foreign key on the Clients and Users table, with reference to the People table.
Now let’s use the same LINQ query that was created before, to return all the users:
static void SelectUsers()
{
using var db = new ApplicationContext();
var users = db.Users.ToList();
foreach (var user in users)
{
Console.WriteLine($"User: {user.Name} - Username:
{user.UserName}");
}
}The SQL query that will be generated for this query will be like this:
SELECT [p].[Id], [p].[Name], [u].[UserName]
FROM [People] AS [p]
INNER JOIN [Users] AS [u] ON [p].[Id] = [u].[Id]Note that now in this SQL query, the searching is being made from the People table, with an INNER JOIN with the Users table. This is the result of this query:
TPT Allows Null Values on Properties
With TPT it’s possible to configure the properties in the derived class, in this example Client or Users, to be required, since now these entities will have their own tables in the database (it will be created different tables for each derived entity) and they don’t share the same table. So it’s now possible, for example, to set a configuration using DataAnnotations or configuring using Fluent API, to the property Email in the Client class to be required:
public class ClientConfiguration : IEntityTypeConfiguration<Client>
{
public void Configure(EntityTypeBuilder<Client> builder)
{
builder.ToTable("Clients")
.Property(c=>c.Email)
.IsRequired();
}
}The table will be successfully created in the database, with the column “Email” configured to be required (“not null”):
Conclusion
With the Table-per-Hierarchy (TPH), EF Core will generate a single table, with a column to distinguish between the derived tables. With Table-per-Type (TPT) mapping, EF Core will generate different tables for each derived tables. An advantage of having a specific table for each devired entity, beyond having a better organization for the tables in the database, it’s the possibility to configure some property in the derived class to be required. This is something that is not possible when using TPH mapping, because with this approach the properties need to allow null values, once the base table is a shared table.
Source: Medium
The Tech Platform