Table-per-type (TPT) mapping

Table-per-type (TPT) mapping

Table-per-type inheritance uses a separate table in the database to maintain data for non-inherited properties and key properties for each type in the inheritance hierarchy.
  • Table per Type is about representing inheritance relationships as relational foreign key associations.
  • Every class and subclass including abstract classes has its own table.
  • The table for subclasses contains columns only for each non-inherited property along with a primary key that is also a foreign key of the base class table.
By default, EF Core maps an inheritance hierarchy of .NET types to a single database table. This is known as table-per-hierarchy (TPH) mapping. EF Core 5.0 also allows mapping each .NET type in an inheritance hierarchy to a different database table; known as table-per-type (TPT) mapping.
Let's consider the following simple model with a mapped hierarchy.
1
public class Person
2
{
3
public int Id { get; set; }
4
public string FullName { get; set; }
5
}
6
​
7
public class Student : Person
8
{
9
public DateTime EnrollmentDate { get; set; }
10
}
11
​
12
public class Teacher : Person
13
{
14
public DateTime HireDate { get; set; }
15
}
Copied!
Here is the context class implementation without any additional configuration.
1
public class EntityContext : DbContext
2
{
3
protected override void OnConfiguring(DbContextOptionsBuilder opBuilder)
4
{
5
opBuilder.UseSqlServer("Data Source=(localdb)\\ProjectsV13;Initial Catalog=PeopleContextDb;");
6
}
7
public DbSet<Person> People { get; set; }
8
public DbSet<Student> Students { get; set; }
9
public DbSet<Teacher> Teachers { get; set; }
10
}
Copied!
By default, EF Core will map this to a single table.
1
CREATE TABLE [dbo].[People] (
2
[Id] INT IDENTITY (1, 1) NOT NULL,
3
[FullName] NVARCHAR (MAX) NULL,
4
[Discriminator] NVARCHAR (MAX) NOT NULL,
5
[EnrollmentDate] DATETIME2 (7) NULL,
6
[HireDate] DATETIME2 (7) NULL,
7
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED ([Id] ASC)
8
);
Copied!
In the TPT mapping pattern, all the types are mapped to individual tables. Properties that belong solely to a base type or derived type are stored in a table that maps to that type.
Entity types can be mapped to different tables using mapping attributes.
1
[Table("People")]
2
public class Person
3
{
4
public int Id { get; set; }
5
public string FullName { get; set; }
6
}
7
​
8
[Table("Students")]
9
public class Student : Person
10
{
11
public DateTime EnrollmentDate { get; set; }
12
}
13
​
14
[Table("Teachers")]
15
public class Teacher : Person
16
{
17
public DateTime HireDate { get; set; }
18
}
Copied!
However, mapping each entity type to a different table will instead result in one table per type.
1
CREATE TABLE [dbo].[People] (
2
[Id] INT IDENTITY (1, 1) NOT NULL,
3
[FullName] NVARCHAR (MAX) NULL,
4
CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED ([Id] ASC)
5
);
6
​
7
CREATE TABLE [dbo].[Students] (
8
[Id] INT NOT NULL,
9
[EnrollmentDate] DATETIME2 (7) NOT NULL,
10
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([Id] ASC),
11
CONSTRAINT [FK_Students_People_Id] FOREIGN KEY ([Id]) REFERENCES [dbo].[People] ([Id])
12
);
13
​
14
CREATE TABLE [dbo].[Teachers] (
15
[Id] INT NOT NULL,
16
[HireDate] DATETIME2 (7) NOT NULL,
17
CONSTRAINT [PK_Teachers] PRIMARY KEY CLUSTERED ([Id] ASC),
18
CONSTRAINT [FK_Teachers_People_Id] FOREIGN KEY ([Id]) REFERENCES [dbo].[People] ([Id])
19
);
Copied!
Tables that map to derived types also store a foreign key that joins the derived table with the base table.