In this article, we will discuss the recently added Table Per Concrete Type (TPC) inheritance mapping strategy in EF Core 7.

In .NET applications, Entity Framework Core (EF Core) has transformed how we communicate with databases by linking object-oriented domain models with relational database structures. One of the features that EF Core provides is the various inheritance strategies for our entities, including “Table Per Hierarchy” (TPH), “Table Per Type” (TPT), and “Table per Concrete Type” (TPC).

Let’s begin.

To download the source code for this article, you can visit our GitHub repository.

What is Table Per Concrete Type (TPC)?

The TPC inheritance mapping strategy involves creating a separate database table for each non-abstract or concrete type. This approach differs from other inheritance strategies, such as TPT, which references the primary table for derived entities. As for TPH, it employs a discriminator column to distinguish between different types of records.

TPC’s primary benefit is that it eliminates the need for extra columns or tables to store hierarchy-related information, as each table is self-contained.

Support Code Maze on Patreon to get rid of ads and get the best discounts on our products!
Become a patron at Patreon!

Now, to move forward we need to install the necessary NuGet packages:

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design

To support EF Core and a PostgreSQL database, we need to install the first two packages, while the third one is for code-first migrations. With these packages installed, we can continue to explore the domain model.

The Domain Model

We will keep our domain model simple, catering to the webshop (E-commerce) domain.

We have a base class Item containing generic properties related to an Item in a WebShop:

public abstract class Item
{
    public required Guid Id { get; set; }
    public required string Description { get; set; }
    public required decimal Price { get; set; }
}

Then we define ClothingItem, which derives from Item and has its own specialized properties:

public class ClothingItem : Item
{
    public required string Size { get; set; }
    public required string Color { get; set; }
    public required string Material { get; set; }
}

Next, following a similar approach, we add another type of Item, the ElectronicItem:

public class ElectronicItem : Item
{
    public required string Model { get; set; }
    public required string Manufacturer { get; set; }
}

The next step is to configure these entities to support the TPC strategy.

The Entities Configuration

This is the most important step to configure the domain model to support TPC inheritance mapping strategy. Based on this configuration, the actual tables will be created in the database:

public class ItemConfig : IEntityTypeConfiguration
{
    public void Configure(EntityTypeBuilder builder)
    {
        builder.UseTpcMappingStrategy();
    }
}

We implement the Configure() method from the IEntityTypeConfiguration generic interface to set up the entity’s settings or configuration.

Then, we apply the TPC inheritance mapping strategy to the abstract Item entity, employing the UseTpcMappingStrategy() method. We abstain from using the ToTable() method in the Item entity configuration as we want to prevent table creation for this entity in the database. This distinguishes the TPC strategy from other inheritance hierarchy strategies.

Following, we configure ClothingItem with fundamental data type and column size settings, and we also seed them with sample data for querying purposes:

public class ClothingItemConfig : IEntityTypeConfiguration
{
    public void Configure(EntityTypeBuilder builder)
    {
        builder.ToTable("ClothingItems");
        builder.Property(x => x.Description).HasMaxLength(100).IsRequired();
        builder.Property(x => x.Price).HasColumnType("decimal(18,2)").IsRequired();
        builder.Property(x => x.Size).HasMaxLength(100).IsRequired();
        builder.Property(x => x.Color).HasMaxLength(100).IsRequired();
        builder.Property(x => x.Material).HasMaxLength(100).IsRequired();

        builder.HasData(new List()
        {
            new()
            {
                Id = Guid.NewGuid(),
                Description = "Nike Air Max",
                Price = 8000,
                Size = "M",
                Color = "Black",
                Material = "Leather"
            },
            // additional items not shown for brevity
        });
    }
}

Next, we do the same for ElectronicItem:

public class ElectronicItemConfig : IEntityTypeConfiguration
{
    public void Configure(EntityTypeBuilder builder)
    {
        builder.ToTable("ElectronicItems");
        builder.Property(x => x.Description).HasMaxLength(100).IsRequired();
        builder.Property(x => x.Price).HasColumnType("decimal(18,2)").IsRequired();
        builder.Property(x => x.Model).HasMaxLength(100).IsRequired();
        builder.Property(x => x.Manufacturer).HasMaxLength(100).IsRequired();

        builder.HasData(new List
        {
            new()
            {
                Id = Guid.NewGuid(),
                Description = "Samsung Galaxy S21",
                Price = 8000,
                Model = "S21",
                Manufacturer = "Samsung"
            },
            // additional items not shown for brevity
        });
    }
}

Now, let’s apply this configuration to the DbContext class:

public class WebShopDbContext : DbContext
{
    public DbSet Items => Set<Item>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        const string connectionString = "Your connection string here";
        optionsBuilder.UseNpgsql(connectionString)
            .LogTo(Console.WriteLine, LogLevel.Information);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.ApplyConfiguration(new ItemConfig());
        modelBuilder.ApplyConfiguration(new ClothingItemConfig());
        modelBuilder.ApplyConfiguration(new ElectronicItemConfig());
    }
}

To work with the Item Entity, we create a DbSet property and apply entity configuration in the OnModelCreating() method. However, we may wonder how to access the ElectronicItem and ClothingItem data when they have their own tables in the database, and we only have a DbSet for Item in the WebShopDbContext class. The answer lies in using LINQ’s OfType() method, as we’ll see in the next section.

Once we execute the migration, EF Core will generate the following tables:

CREATE TABLE "ElectronicItems" (
         "Id" uuid NOT NULL,
         "Description" character varying(100) NOT NULL,
         "Price" numeric(18,2) NOT NULL,
         "Model" character varying(100) NOT NULL,
         "Manufacturer" character varying(100) NOT NULL,
         CONSTRAINT "PK_ElectronicItems" PRIMARY KEY ("Id")

CREATE TABLE "ClothingItems" (
         "Id" uuid NOT NULL,
         "Description" character varying(100) NOT NULL,
         "Price" numeric(18,2) NOT NULL,
         "Size" character varying(100) NOT NULL,
         "Color" character varying(100) NOT NULL,
         "Material" character varying(100) NOT NULL,
         CONSTRAINT "PK_ClothingItems" PRIMARY KEY ("Id")
     );

Two separate tables, ElectronicItems and ClothingItems, will be created without including the __EFMigrationsHistory table. Let’s proceed to querying the data.

Querying Data With TPC

As we only have the DbSet<Item> property on the WebShopDbContext class, we have to query the data using it. To query all the Items, including Electronics and Clothing:

public async Task<List<Item>> GetAllItems()
{
    await using var context = new WebShopDbContext();
    return await context.Items.ToListAsync();
}

We use the ToListAsync() method to retrieve data from both ElectronicItems and ClothingItems tables. EF Core generates an SQL query using the UNION ALL statement to query data from both tables.

To retrieve data from specific ElectronicItems and ClothingItems tables:

public async Task<List<ElectronicItem>> GetAllElectronicItems()
{
     await using var context = new WebShopDbContext();
     return await context.Items.OfType<ElectronicItem>().ToListAsync();
}

public async Task<List<ClothingItem>> GetAllClothingItems()
{
     await using var context = new WebShopDbContext();
     return await context.Items.OfType<ClothingItem>().ToListAsync();
}

We use the OfType() generic method to get the data from the respective table. As developers, we must know a few things to watch out for in the TPC. Let’s take a look at them next.

TPC Considerations

When designing a model, it’s important to consider how the primary key values will be generated. In our example, we use Guid as the primary key, but sometimes it’s necessary to use an integer-based identity column instead. In these cases, EF Core helps by automatically creating an identity sequence that generates the next value for the Id column when a new record is added to any of the related tables.

For more complex scenarios, we may require custom sequences. For example, all ClothingItems IDs should start with 1000, and all ElectronicItems IDs should start with 2000. 

It’s important to note that, in the TPT strategy, a dedicated Item table maintains the primary key, which we can then use as a single foreign key constraint in other tables. This is different in the TPC strategy, where each table has its own primary key.

With TPC, defining multiple foreign key constraints for each table can become problematic as the number of tables in the hierarchy grows. Let’s understand with the example: imagine we need to expand our domain model and add an OrderItems entity (the table that stores OrderLineItems). The ItemId column in this table would require a foreign key constraint referencing ElectronicItems and ClothingItems individual primary keys. However, this is not mandatory from a TPC strategy perspective.

Remember, although we define foreign keys on the ItemId column, PostgreSQL won’t enforce referential integrity. We must adopt the TPT strategy with an exclusive Items table to implement this behavior.

Which Strategy to Use

As with most other things, deciding which strategy to choose depends on our use case.

TPT looks ideal from the object-oriented perspective as every entity is mapped to its own table. But when querying the data, EF Core will create joins between those tables.

TPH will have only one single table with many columns based on the hierarchy mapping tables involved in the strategy. But most of these columns will hold null values. This might easily mean that we’ll end up with a mapping table that looks rather messy.

TPC stores the data in a denormalized way; hence, we see a lot of redundant columns in the hierarchy tables. It uses UNION ALL to query the multiple tables involved in the hierarchy inheritance.

Conclusion

TPC is a unique inheritance strategy in EF Core that has its place in specific scenarios. By understanding how it works and its advantages and disadvantages, developers can make informed decisions on which inheritance strategy to employ in their applications. As with all architectural decisions, we should consider the specific requirements of our application and the implications of each approach before settling on one.

Liked it? Take a second to support Code Maze on Patreon and get the ad free reading experience!
Become a patron at Patreon!