Entity Framework (EF) Core is a powerful Object-Relational Mapping (ORM) framework for .NET applications. Among its inheritance mapping strategies is the Table Per Concrete Type (TPC), which can be particularly useful for certain database designs. This blog post will explore how to implement TPC in Entity Framework Core with C# and SQL.

What is Table Per Concrete Type (TPC)?

TPC is an inheritance mapping strategy where each concrete class in an inheritance hierarchy is mapped to a separate table in the database. Unlike Table Per Hierarchy (TPH), where a single table holds all fields of the inheritance hierarchy, TPC creates a distinct table for each concrete subclass, including the fields of its parent classes.

Benefits of TPC

• Normalization: Reduces duplication of data and maintains consistency.

• Performance: Queries involving concrete types are faster as they avoid joins.

• Flexibility: Each table can evolve independently in terms of schema changes.

Implementing TPC in Entity Framework Core

Let's implement TPC using a simple example of a base class Vehicle and two derived classes Car and Motorcycle.

Step 1: Define the Model Classes

public abstract class Vehicle
{
    public int VehicleId { get; set; }
    public string Manufacturer { get; set; }
    public string Model { get; set; }
}

public class Car : Vehicle
{
    public int Doors { get; set; }
}

public class Motorcycle : Vehicle
{
    public bool HasSidecar { get; set; }
}

Step 2: Configuring the DbContext

using Microsoft.EntityFrameworkCore;

public class VehicleDbContext : DbContext
{
    public DbSet<Car> Cars { get; set; }
    public DbSet<Motorcycle> Motorcycles { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Car>().ToTable("Cars");
        modelBuilder.Entity<Motorcycle>().ToTable("Motorcycles");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=VehicleDb;Trusted_Connection=True;");
    }
}

This configuration specifies that Car and Motorcycle are stored in separate tables, namely Cars and Motorcycles.

Step 3: Creating the Database

The SQL to create this structure typically looks something like this:

CREATE TABLE Cars (
    VehicleId INT PRIMARY KEY,
    Manufacturer VARCHAR(100),
    Model VARCHAR(100),
    Doors INT
);

CREATE TABLE Motorcycles (
    VehicleId INT PRIMARY KEY,
    Manufacturer VARCHAR(100),
    Model VARCHAR(100),
    HasSidecar BIT
);

Step 4: Working with Data

Here's how you might add and query entities:

using (var context = new VehicleDbContext())
{
    var car = new Car { Manufacturer = "Toyota", Model = "Corolla", Doors = 4 };
    var motorcycle = new Motorcycle { Manufacturer = "Harley-Davidson", Model = "Street 750", HasSidecar = false };

    context.Cars.Add(car);
    context.Motorcycles.Add(motorcycle);
    context.SaveChanges();

    var cars = context.Cars.ToList();
    var motorcycles = context.Motorcycles.ToList();
}

Conclusion

The TPC strategy in Entity Framework Core provides a clean and efficient way to manage database schemas when dealing with inheritance in your domain models. It allows for more tailored queries and can improve the performance of your database operations by reducing the need for joins and complex queries. While it can increase the number of tables and possibly lead to data redundancy in certain scenarios, TPC is a powerful tool in the right contexts.

This guide should help you get started with the TPC inheritance mapping strategy in Entity Framework Core, laying a solid foundation for your applications that require sophisticated ORM capabilities.