EF Core – Inheritance mapping

by

There are two ways to do inheritance mapping in EF Core:

  • Table-per-Hierarchy (TPH) = There’s a single table for all classes in the hierarchy.
  • Table-per-Type (TPT) = There’s one table per class in the hierarchy. This is available in EF Core 5 and above.

Let’s say we have a database with employees. All employees have an id and a name. There are currently two types of employees: programmers and drivers. Programmers have a language (ex: C#), and drivers have a car (ex: Honda). We can model this with the following class hierarchy:

Class hierarchy diagram showing Employee and two subclass - Programmer and Driver.

With TPH mapping, we have a single table that contains columns for all classes in hierarchy + a discriminator column:

TPH mapped Employees table. Has sample data for the two concrete subclasses - Programmer and Driver.

With TPT mapping, we have one table for each class in the hierarchy:

TPH mapped employees hierarchy. There are three tables (one for each class): Employees, Programmers, and Drivers. The subclass tables are linked to the base class table with a foreign key

This difference in table structure has performance and validation implications, which I’ll explain below. After that, I’ll show how to configure TPH and TPT mapping.

Key differences between TPH and TPT

There are two keys differences between TPT and TPH:

1 – TPH has potentially better query performance

With TPH, the data is all in one table. With TPT, the data is split into multiple tables, which requires you to do joins. In theory, having to join multiple tables will have worse performance than selecting from a single table.

When EF Core generates queries for TPH, it adds the discriminator column in the WHERE clause. By default, the discriminator column is not included in an index. If a column is not in an index, it has the potential to degrade performance. I’d suggest testing performance to determine if you’d benefit from adding the column to the index.

2 – TPT allows you to make subclass columns required

With TPT, each subclass has their own table, so you can make columns required (by adding the [Required] attribute). In other words, you can make them NOT NULL.

On the other hand with TPH, all of the subclass columns are in the same table. This means they have to be nullable. When you’re inserting a record for one subclass (ex: Programmer), it won’t have a value for the columns belonging to the other subclass (ex: Driver). So it makes sense that these columns cannot be required. Even if you add the [Required] attribute, it will be ignored when generating the migration and the column will be set to nullable. If you force the column to be NOT NULL, you’ll run into problems when inserting records, so avoid doing that.

Configuring inheritance mapping

In this section, I’ll show how to configure both types of inheritance mapping (TPH and TPT) for the Employee class hierarchy shown at the beginning of this article. This will be a full example showing how to use EF Core to create tables, inserting sample data, and executing queries (to see the SQL generated by EF Core).

Note: Configuration will be done through a custom DbContext class.

Model classes

First, let’s define the model classes for the Employee hierarchy:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public abstract class EmployeeBase
{
	[Key]
	[DatabaseGenerated(DatabaseGeneratedOption.None)]
	public int Id { get; set; }
	public string Name { get; set; }
}

public class Driver : EmployeeBase
{
	public string Car { get; set; }
}

public class Programmer : EmployeeBase
{
	public string Language { get; set; }
}
Code language: C# (cs)

These model classes will be the same for both TPH and TPT mapping.

TPH mapping

1 – Add DbSet’s for all classes in the hierarchy

Add DbSet properties to the context for all of the classes (including the base class):

using Microsoft.EntityFrameworkCore;

public class CustomContext : DbContext
{
	//rest of class

	public DbSet<EmployeeBase> Employees { get; set; }
	public DbSet<Programmer> Programmers { get; set; }
	public DbSet<Driver> Drivers { get; set; }
}
Code language: C# (cs)

At a bare minimum, that’s all you have to do to enable TPH mapping.

2 – Configure discriminator

The default discriminator column is called “Discriminator” and the discriminator values are the names of the subclasses (Programmer, Driver).

You can customize the discriminator column name and the discriminator values for each subclass. For example, let’s say you want the discriminator to be called “Type” and use ‘P’ for Programmer and ‘D’ for Driver. Here’s how to customize the discriminator:

using Microsoft.EntityFrameworkCore;

public class CustomContext : DbContext
{
	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<EmployeeBase>()
			.HasDiscriminator<char>("Type")
			.HasValue<Programmer>('P')
			.HasValue<Driver>('D');
	}

	//rest of class
}
Code language: C# (cs)

3 – Generate a migration and apply it

Execute the following to generate a migration:

dotnet ef migrations add InitTPH
Code language: PowerShell (powershell)

This will generate the following migration code in /Migrations/<timestamp>_InitTPH.cs:

protected override void Up(MigrationBuilder migrationBuilder)
{
	migrationBuilder.CreateTable(
		name: "Employees",
		columns: table => new
		{
			Id = table.Column<int>(type: "int", nullable: false),
			Name = table.Column<string>(type: "nvarchar(max)", nullable: true),
			Type = table.Column<string>(type: "nvarchar(1)", nullable: false),
			Car = table.Column<string>(type: "nvarchar(max)", nullable: true),
			Language = table.Column<string>(type: "nvarchar(max)", nullable: true)
		},
		constraints: table =>
		{
			table.PrimaryKey("PK_Employees", x => x.Id);
		});
}

Code language: C# (cs)

Now apply the migration programmatically or use the dotnet ef tool:

dotnet ef database update
Code language: PowerShell (powershell)

This will create the Employees table using the table definition shown in the migration code above.

4 – Insert sample data

To see how EF Core handles insertions when using TPH mapping, insert some sample data:

using (var context = new CustomContext(connectionString))
{
	context.Add(new Programmer()
	{
		Id = 1,
		Name = "Bob",
		Language = "C#"
	});

	context.Add(new Driver()
	{
		Id = 2,
		Name = "Alice",
		Car = "Honda"
	});

	context.SaveChanges();
}
Code language: C# (cs)

It generates the following insertion queries for the code above:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Employees] ([Id], [Language], [Name], [Type])
VALUES (@p0, @p1, @p2, @p3);
',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(1)',@p0=1,@p1=N'C#',@p2=N'Bob',@p3=N'P'

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Employees] ([Id], [Car], [Name], [Type])
VALUES (@p0, @p1, @p2, @p3);
',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(1)',@p0=2,@p1=N'Honda',@p2=N'Alice',@p3=N'D'
Code language: plaintext (plaintext)

The Employees table in the database will look like this:

TPH mapped Employees table showing the sample data with the custom discriminator column and values

5 – Execute a SELECT query

To see what EF Core generates for SELECT queries when using TPH mapping, get some data:

using (var context = new CustomContext(connectionString))
{
	foreach(var programmer in context.Programmers)
	{
		Console.WriteLine($"{programmer.Name} uses {programmer.Language}");
	}
}
Code language: C# (cs)

It generates the following SELECT query:

SELECT [e].[Id], [e].[Name], [e].[Type], [e].[Language]
FROM [Employees] AS [e]
WHERE [e].[Type] = N'P'
Code language: plaintext (plaintext)

Notice that it added WHERE Type=’P’ so that it only selects Programmer rows.

6 – Add the discriminator to an index

By default, the discriminator column is not added to an index. Because the discriminator column is automatically added to every query, this has the potential to degrade performance. Be sure to do your own performance testing to determine if this is really a problem for you.

If you decide you want to add the discriminator column, you can add the index just like you would for any other column. The only thing special about the discriminator column is it has a default name (“Discriminator”). Be sure to use the right name. Here’s an example of adding an index with the default discriminator column name:

using Microsoft.EntityFrameworkCore;

public class CustomContext : DbContext
{
	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<EmployeeBase>()
			.HasIndex("Discriminator");
	}
	
	//rest of class
}
Code language: C# (cs)

TPT mapping

This is available in EF Core 5 and above.

1 – Add DbSet’s for all classes in the hierarchy

Add DbSet properties to the context for all of the classes (including the base class): 

using Microsoft.EntityFrameworkCore;

public class CustomContext : DbContext
{
	//rest of class

	public DbSet<EmployeeBase> Employees { get; set; }
	public DbSet<Programmer> Programmers { get; set; }
	public DbSet<Driver> Drivers { get; set; }
}
Code language: C# (cs)

Note: This is the same first step you do for TPH.

2 – Map each class to a table

In OnModelCreating(), call .ToTable() for each class in the Employee hierarchy:

using Microsoft.EntityFrameworkCore;


public class CustomContext : DbContext
{
	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<EmployeeBase>().ToTable("Employees");
		modelBuilder.Entity<Programmer>().ToTable("Programmers");
		modelBuilder.Entity<Driver>().ToTable("Drivers");
	}
	
	//rest of class
}
Code language: C# (cs)

This + step 1 are the bare minimum you have to do to enable TPT mapping.

3 – Generate a migration and apply it

Execute the following to generate a migration:

dotnet ef migrations add InitTPT
Code language: PowerShell (powershell)

This will generate the following migration code in /Migrations/<timestamp>_InitTPT.cs:

protected override void Up(MigrationBuilder migrationBuilder)
{
	migrationBuilder.CreateTable(
		name: "Employees",
		columns: table => new
		{
			Id = table.Column<int>(type: "int", nullable: false),
			Name = table.Column<string>(type: "nvarchar(max)", nullable: true)
		},
		constraints: table =>
		{
			table.PrimaryKey("PK_Employees", x => x.Id);
		});

	migrationBuilder.CreateTable(
		name: "Drivers",
		columns: table => new
		{
			Id = table.Column<int>(type: "int", nullable: false),
			Car = table.Column<string>(type: "nvarchar(max)", nullable: true)
		},
		constraints: table =>
		{
			table.PrimaryKey("PK_Drivers", x => x.Id);
			table.ForeignKey(
				name: "FK_Drivers_Employees_Id",
				column: x => x.Id,
				principalTable: "Employees",
				principalColumn: "Id",
				onDelete: ReferentialAction.Restrict);
		});

	migrationBuilder.CreateTable(
		name: "Programmers",
		columns: table => new
		{
			Id = table.Column<int>(type: "int", nullable: false),
			Language = table.Column<string>(type: "nvarchar(max)", nullable: true)
		},
		constraints: table =>
		{
			table.PrimaryKey("PK_Programmers", x => x.Id);
			table.ForeignKey(
				name: "FK_Programmers_Employees_Id",
				column: x => x.Id,
				principalTable: "Employees",
				principalColumn: "Id",
				onDelete: ReferentialAction.Restrict);
		});
}
Code language: C# (cs)

Execute the following to apply the migration:

dotnet ef database update
Code language: PowerShell (powershell)

This will create the Employees, Programmers, and Drivers tables. It will link the Programmers/Drivers tables to the Employees table with a foreign key (id).

4 – Insert sample data

To see how EF Core handles insertions when using TPT mapping, insert some sample data:

using (var context = new CustomContext(connectionString))
{
	context.Add(new Programmer()
	{
		Id = 1,
		Name = "Jane",
		Language = "Java"
	});

	context.Add(new Driver()
	{
		Id = 2,
		Name = "Frank",
		Car = "Ford"
	});

	context.SaveChanges();
}
Code language: C# (cs)

It generates the following insertion queries for the code above:

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Employees] ([Id], [Name])
VALUES (@p0, @p1);
',N'@p0 int,@p1 nvarchar(4000)',@p0=1,@p1=N'Jane'

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Employees] ([Id], [Name])
VALUES (@p0, @p1);
',N'@p0 int,@p1 nvarchar(4000)',@p0=2,@p1=N'Frank'

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Drivers] ([Id], [Car])
VALUES (@p0, @p1);
',N'@p0 int,@p1 nvarchar(4000)',@p0=2,@p1=N'Ford'

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Programmers] ([Id], [Language])
VALUES (@p0, @p1);
',N'@p0 int,@p1 nvarchar(4000)',@p0=1,@p1=N'Java'
Code language: plaintext (plaintext)

The three tables in the database will look like this:

TPT mapping showing three tables - Employees, Programmers, and Drivers, and sample sample data that was just inserted

5 – Execute a SELECT query

Let’s see what SQL query EF Core generates when selecting data:

using (var context = new CustomContext(connectionString))
{
	foreach (var driver in context.Drivers)
	{
		Console.WriteLine($"{driver.Name} drives {driver.Car}");
	}
} 
Code language: C# (cs)

It generates the following query with a join:

SELECT [e].[Id], [e].[Name], [d].[Car]
FROM [Employees] AS [e]
INNER JOIN [Drivers] AS [d] ON [e].[Id] = [d].[Id]
Code language: plaintext (plaintext)

It always has to join the tables to get the full Driver/Programmer records.

Related Articles