Inheritance Mapping in EF Core: TPH, TPT and When to Choose Which

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First: How Many Tables for Three Kinds of Book?

Our bookstore has grown. We no longer sell only printed books; we have e-books and audiobooks too. All three share a title and a price, but a printed book has a page count, an e-book has a file size, and an audiobook has a duration and a narrator. On the C# side, the most natural way to model this is inheritance:

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
}

public class PrintedBook : Book
{
    public int PageCount { get; set; }
}

public class EBook : Book
{
    public double FileSizeMb { get; set; }
    public string Format { get; set; } = "";
}

public class AudioBook : Book
{
    public int DurationMinutes { get; set; }
    public string Narrator { get; set; } = "";
}

Now guess: if you hand these four classes to a DbContext and call EnsureCreated, how many tables end up in the database? Four, three, or one? Before you answer, keep this in mind: a relational database has no concept of inheritance. A table is a table; it does not inherit from anything. So EF Core has to pick a strategy for turning a class hierarchy into tables. In this post we compare the two strategies EF Core 6 offers, TPH and TPT, using real SQL output.

The Default Strategy: TPH and the Discriminator Column

Let's write the context. We expose a DbSet for every type and turn on command logging so we can see the SQL that runs:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

public class BookstoreContext : DbContext
{
    public DbSet<Book> Books => Set<Book>();
    public DbSet<PrintedBook> PrintedBooks => Set<PrintedBook>();
    public DbSet<EBook> EBooks => Set<EBook>();
    public DbSet<AudioBook> AudioBooks => Set<AudioBook>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=bookstore.db")
                  .LogTo(Console.WriteLine, new[] { RelationalEventId.CommandExecuted });
}

You can also see the DDL that EnsureCreated runs with db.Database.GenerateCreateScript(). It is identical on EF Core 6 and 10:

CREATE TABLE "Books" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY AUTOINCREMENT,
    "Title" TEXT NOT NULL,
    "Price" TEXT NOT NULL,
    "Discriminator" TEXT NOT NULL,
    "DurationMinutes" INTEGER NULL,
    "Narrator" TEXT NULL,
    "FileSizeMb" REAL NULL,
    "Format" TEXT NULL,
    "PageCount" INTEGER NULL
);

The answer: one table. This strategy is called TPH, table-per-hierarchy. The whole hierarchy goes into a single table, and the properties of every subclass become columns of that table. The Discriminator column, which EF adds on its own, tells you which type a row belongs to. Your class has no property for it; EF keeps it as a shadow property. Don't be surprised that Price is TEXT: SQLite has no decimal type, so EF stores it as text.

Think of TPH as the shared wardrobe in a student dorm. Everyone's things are in the same wardrobe, and each shelf has its owner's name on it. Opening the wardrobe is a single move, but some shelf is always half empty.

Saving and Polymorphic Queries

Let's add three different books with a single AddRange and read them back:

using var db = new BookstoreContext();
db.Database.EnsureDeleted();
db.Database.EnsureCreated();

db.Books.AddRange(
    new PrintedBook { Title = "Dune", Price = 18.50m, PageCount = 612 },
    new EBook { Title = "Clean Code", Price = 9.99m, FileSizeMb = 4.2, Format = "EPUB" },
    new AudioBook { Title = "Sapiens", Price = 14.00m, DurationMinutes = 935, Narrator = "Derek Perkins" });
db.SaveChanges();
db.ChangeTracker.Clear();

foreach (var book in db.Books.ToList())
    Console.WriteLine($"{book.GetType().Name,-12} {book.Title}");

The log shows one INSERT per book. Here is the audiobook's on EF Core 10:

INSERT INTO "Books" ("Discriminator", "DurationMinutes", "Narrator", "Price", "Title")
VALUES (@p0, @p1, @p2, @p3, @p4)
RETURNING "Id";

EF Core 6 follows the same INSERT with SELECT "Id" FROM "Books" WHERE changes() = 1 AND "rowid" = last_insert_rowid(); instead of RETURNING; the result is the same. You never set the discriminator value; EF wrote it from the class name. The console output:

AudioBook    Sapiens
EBook        Clean Code
PrintedBook  Dune

Two things stand out. First, the single query over db.Books gave us objects of the right type: not Book, but AudioBook, EBook and PrintedBook. This is called a polymorphic query; EF looks at each row's discriminator value and decides which class to materialize. Second, EF sent the INSERTs for the same table in an order of its own choosing. Don't rely on the Id order matching the AddRange order.

When you ask for only one subtype, EF adds the discriminator condition to the WHERE clause for you:

var ebooks = db.EBooks.ToList();
var bigEbooks = db.Books.OfType<EBook>().Where(e => e.FileSizeMb > 1).ToList();
var longAudio = db.Books
    .Where(b => b is AudioBook && ((AudioBook)b).DurationMinutes > 600)
    .ToList();

The SQL for the three queries via ToQueryString() (EF Core 10):

SELECT "b"."Id", "b"."Discriminator", "b"."Price", "b"."Title", "b"."FileSizeMb", "b"."Format"
FROM "Books" AS "b"
WHERE "b"."Discriminator" = 'EBook'

SELECT "b"."Id", "b"."Discriminator", "b"."Price", "b"."Title", "b"."FileSizeMb", "b"."Format"
FROM "Books" AS "b"
WHERE "b"."Discriminator" = 'EBook' AND "b"."FileSizeMb" > 1.0

SELECT "b"."Id", "b"."Discriminator", "b"."Price", "b"."Title", "b"."DurationMinutes", "b"."Narrator", "b"."FileSizeMb", "b"."Format", "b"."PageCount"
FROM "Books" AS "b"
WHERE "b"."Discriminator" = 'AudioBook' AND "b"."DurationMinutes" > 600

db.EBooks and OfType<EBook>() produce the same SQL; a separate DbSet for a subtype is only a convenience. The C# is operator is translated into a discriminator comparison too. The difference: when you filter with is, the query's type is still Book, so the SELECT list brings back every column. EF Core 6 writes the same SQL with the conditions wrapped in parentheses, for example WHERE ("b"."Discriminator" = 'EBook') AND ("b"."FileSizeMb" > 1.0).

Since the discriminator is a shadow property, you can read it in a query when you need to:

var types = db.Books
    .Select(b => new { b.Title, Type = EF.Property<string>(b, "Discriminator") })
    .ToList();

Take Control of the Discriminator

The default values are class names, which is handy but fragile. You can set the column's name, type and values yourself with the Fluent API. This time let's bind the discriminator to a real property and make Book abstract, because the store has no product that is "just a book":

public enum BookType
{
    Printed,
    Digital,
    Audio
}

public abstract class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
    public BookType Type { get; set; }
}

// PrintedBook and EBook are unchanged; AudioBook gets a Format too
public class AudioBook : Book
{
    public int DurationMinutes { get; set; }
    public string Narrator { get; set; } = "";
    public string Format { get; set; } = "";
}

public class BookstoreContext : DbContext
{
    public DbSet<Book> Books => Set<Book>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite("Data Source=bookstore.db");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Book>()
            .HasDiscriminator(b => b.Type)
            .HasValue<PrintedBook>(BookType.Printed)
            .HasValue<EBook>(BookType.Digital)
            .HasValue<AudioBook>(BookType.Audio);

        modelBuilder.Entity<Book>()
            .Property(b => b.Type)
            .HasConversion<string>()
            .HasMaxLength(16);

        // let two sibling types share one column
        modelBuilder.Entity<EBook>().Property(e => e.Format).HasColumnName("Format");
        modelBuilder.Entity<AudioBook>().Property(a => a.Format).HasColumnName("Format");
    }
}

Three things changed. HasDiscriminator(b => b.Type) uses the Type property as the discriminator, and the HasValue calls say which value each type is written with. HasConversion<string>() keeps the enum as readable text in the database; we will look at value converters in detail in the next post. The last two lines map the Format properties of EBook and AudioBook to the same column. Also, this time there is only DbSet<Book>; the subtypes entered the model through the HasValue calls. The resulting table:

CREATE TABLE "Books" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY AUTOINCREMENT,
    "Title" TEXT NOT NULL,
    "Price" TEXT NOT NULL,
    "Type" TEXT NOT NULL,
    "DurationMinutes" INTEGER NULL,
    "Narrator" TEXT NULL,
    "Format" TEXT NULL,
    "FileSizeMb" REAL NULL,
    "PageCount" INTEGER NULL
);

Format is a single column. The db.Books.OfType<AudioBook>() query now filters with WHERE "b"."Type" = 'Audio'. The Type property is populated on the objects you read, so you can write switch (book.Type) in your code. Still, in most places is or pattern matching reads better; you will find the enum most useful for reporting and for handing data to the outside world.

The Price of TPH: Columns That Have to Be NULL

Look at the DDL again. AudioBook.Narrator is a non-nullable string in C#, yet the column is Narrator TEXT NULL. PageCount is an int, and its column also accepts NULL. The reason is simple: the table is shared by every type. An e-book row cannot have a PageCount, so the column has to allow NULL.

The practical consequence: the database can no longer enforce the rule "an audiobook must have a narrator". The rule lives only in your C# code and in EF's model. If another application or a hand-written SQL statement touches the same table, saving an audiobook without a narrator is entirely possible. This is the main reason DBAs keep their distance from TPH.

The other price is width. As the hierarchy grows, so does the table, and most rows carry NULL in most columns. Five subtypes with eight properties each means at least thirty-two empty columns in every row. In return, every query goes to a single table with no JOINs at all. For read performance, TPH is hard to beat.

TPT: A Table per Type

The second strategy, which arrived in EF Core 5, is TPT, table-per-type. We keep the model as it is and map each type to its own table:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Book>().ToTable("Books");
    modelBuilder.Entity<PrintedBook>().ToTable("PrintedBooks");
    modelBuilder.Entity<EBook>().ToTable("EBooks");
    modelBuilder.Entity<AudioBook>().ToTable("AudioBooks");
}

Two of the tables from the DDL that EF Core 10 generates:

CREATE TABLE "Books" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Books" PRIMARY KEY,
    "Title" TEXT NOT NULL,
    "Price" TEXT NOT NULL
);

CREATE TABLE "EBooks" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_EBooks" PRIMARY KEY,
    "FileSizeMb" REAL NOT NULL,
    "Format" TEXT NOT NULL,
    CONSTRAINT "FK_EBooks_Books_Id" FOREIGN KEY ("Id") REFERENCES "Books" ("Id") ON DELETE CASCADE
);

Shared columns live in Books, and each subtype's own columns live in its own table. The primary key of a subtype table is also a foreign key to Books. There is no discriminator column, and the columns are now NOT NULL. EF Core 6 also writes AUTOINCREMENT on the Id columns here; since the value comes from Books anyway, it has no practical effect.

Adding an e-book sends two INSERTs (EF Core 10 log):

INSERT INTO "Books" ("Price", "Title")
VALUES (@p0, @p1)
RETURNING "Id";

INSERT INTO "EBooks" ("Id", "FileSizeMb", "Format")
VALUES (@p2, @p3, @p4);

Deleting sends two DELETEs as well, first from EBooks, then from Books. The real difference is in queries. db.Books.ToList() now produces SQL like this:

SELECT "b"."Id", "b"."Price", "b"."Title", "a"."DurationMinutes", "a"."Narrator", "e"."FileSizeMb", "e"."Format", "p"."PageCount", CASE
    WHEN "p"."Id" IS NOT NULL THEN 'PrintedBook'
    WHEN "e"."Id" IS NOT NULL THEN 'EBook'
    WHEN "a"."Id" IS NOT NULL THEN 'AudioBook'
END AS "Discriminator"
FROM "Books" AS "b"
LEFT JOIN "AudioBooks" AS "a" ON "b"."Id" = "a"."Id"
LEFT JOIN "EBooks" AS "e" ON "b"."Id" = "e"."Id"
LEFT JOIN "PrintedBooks" AS "p" ON "b"."Id" = "p"."Id"

There is no discriminator column, but EF still needs the type information. It builds it itself with CASE, by LEFT JOINing every subtype table and checking which one has a matching row. Three subtypes means three JOINs; ten subtypes means ten. When you ask for a single subtype such as db.EBooks, one INNER JOIN "EBooks" is enough. Both strategies produce the same SQL on EF Core 6 and 10.

Which One, and When?

Short answer: start with TPH by default. Based on measurements, the EF documentation also says TPT is in most cases the inferior technique for performance; in its benchmark, loading every row of a seven-type hierarchy takes 149 ms with TPH and 313 ms with TPT. Queries go to a single table, polymorphic queries need no JOINs, and inserts and deletes are single commands.

Consider TPT in these cases. When the database schema has to make sense on its own, for example when a reporting team reads the tables directly and the NOT NULL constraints must live in the database. Or when you are connecting to an existing database that already has a table per type. Choose it knowing the cost: every query over the base type carries as many JOINs as there are subtypes, and that gets noticeably slower as the hierarchy grows.

Keep this question in your head when deciding: does your code mostly query through the base type, or does it always want a specific subtype? If it's the former, TPH wins by a wide margin. For the latter there is a third strategy: TPC, table-per-concrete-type. Every concrete type gets its own table with all of its columns, and there is no base table at all. TPC and the UseTpcMappingStrategy() method that selects it, as well as an explicit UseTptMappingStrategy() method for TPT, arrived in EF Core 7; we will see them in the version posts.

One last note: the strategy is a schema decision. Moving a live project from TPH to TPT means a migration that changes the table structure and moves data. Make the decision before your first migration.

Common Pitfalls

The first pitfall is a subclass you forgot to add to the model. You wrote a new type for children's books but neither exposed a DbSet nor mentioned it in OnModelCreating:

public class KidsBook : Book
{
    public int MinAge { get; set; }
}

db.Books.Add(new KidsBook { Title = "Matilda", MinAge = 8 });
db.SaveChanges();
db.ChangeTracker.Clear();

var matilda = db.Books.Single(b => b.Title == "Matilda");
Console.WriteLine($"{matilda.GetType().Name} / {db.Entry(matilda).Property("Discriminator").CurrentValue}");

You expect an exception, right? On EF Core 6 and 10 there is no error at all. EF tracks the object as the closest type in the model, Book, and the INSERT has no MinAge column: INSERT INTO "Books" ("Discriminator", "Price", "Title"). The console prints Book / Book. MinAge is silently gone. When you add a new subtype, make sure it enters the model through a DbSet or modelBuilder.Entity<KidsBook>(), then generate a migration and look at the diff.

The second pitfall is renaming a class. Because the default discriminator value is the class name, if you rename PrintedBook to PaperBook, the old rows in the database still say 'PrintedBook'. Let's simulate it the other way around:

db.Database.ExecuteSqlRaw("UPDATE Books SET Discriminator = 'PaperBook' WHERE Title = 'Dune'");
db.ChangeTracker.Clear();
var all = db.Books.ToList();

System.InvalidOperationException: Unable to materialize entity instance of type 'Book'. No discriminators matched the discriminator value 'PaperBook'.

One row makes the whole list unreadable. That is why pinning discriminator values with HasValue is a good habit; even if the class name changes, the value in the database does not.

The third pitfall: if Book is not abstract, a "plain book" row can exist too. With the default setup, rows whose discriminator value is 'Book' are valid. If your business has no such product, make the base class abstract. Finally, don't forget that in TPH there is no database-level guarantee for a subtype's required properties; do the validation in your own code.

A Small Experiment

★☆☆ Add a Magazine : Book with a string? Publisher property to the TPH model and bring it into the model with modelBuilder.Entity<Magazine>() without exposing a DbSet. Find the new column in the GenerateCreateScript() output.

★★☆ Run the same four classes first with TPH, then with TPT. In both, compare the logs of db.Books.ToList(), db.EBooks.ToList() and deleting an e-book. Write down in a table how many commands each operation sends.

★★★ In the TPH model, write an audiobook with an empty narrator straight into the table with ExecuteSqlRaw (Narrator NULL). Then read it with db.AudioBooks.ToList() and see what happens on EF Core 6 and 10. What does the result tell you about required properties living in the model rather than in the database?

In this post we handled a book carrying different columns depending on its subtype. But what about values like an order's shipping address, which have no identity of their own yet span several columns? Or an enum that should sit in the database as text, or an Id wrapped in its own type? In the next post we look at owned types and value converters.