EF Core - Inheritance Mapping

• Table-per-Hierarchy (TPH): All the classes in the hierarchy are stored in a single table.
• Table-per-Type (TPT): Each class in the hierarchy has its own table. This option is available starting from EF Core 5.

Example Scenario:

Imagine we have a database managing different kinds of products. All products have a common ID and name. There are two types of products: electronics and clothing. Electronics have a brand (e.g., Samsung), and clothing has a size (e.g., Medium).

Class Hierarchy:

• Product (Base class)
• Electronics (Subclass)
• Clothing (Subclass)

Table-per-Hierarchy (TPH):

In TPH, there is one table that includes all the columns for both subclasses and a discriminator column to distinguish between the two types:

TPH Mapped Example:

• Products Table: It includes columns for ID, Name, Brand (for electronics), Size (for clothing), and a discriminator column (e.g., "ProductType") that stores the type of product (either 'E' for Electronics or 'C' for Clothing).

Table Structure:

• Products Table (with discriminator column):
  • ID
  • Name
  • ProductType (discriminator)
  • Brand (nullable for clothing)
  • Size (nullable for electronics)

Table-per-Type (TPT):

In TPT, we have a separate table for each class in the hierarchy:

• Products Table: Contains common columns for all products (ID, Name).
• Electronics Table: Contains product-specific columns (Brand) and references the Products table via a foreign key.
• Clothing Table: Contains product-specific columns (Size) and also references the Products table via a foreign key.

Table Structure:

• Products Table: Contains only ID and Name.
• Electronics Table: Contains ID (Foreign Key), Brand.
• Clothing Table: Contains ID (Foreign Key), Size.

Key Differences between TPH and TPT:

1. Query Performance:

• TPH: Since all data is stored in one table, queries are usually faster because there are no joins. The disadvantage is that the table can get large, and the presence of nullable columns (for properties not relevant to all types) can complicate data retrieval.

• TPT: Requires joining multiple tables (Products, Electronics, and Clothing) to retrieve the complete record.

2. Data Integrity and Validation:

• TPH: All subclass-specific columns must be nullable, since each row in the table could be for any subclass, and not all fields are applicable to every subclass.

• TPT: Columns for each subclass can be marked as required (using attributes like [Required]) because each subclass has its own table. This helps enforce stricter data integrity.

Configuring Inheritance Mapping in EF Core

Below are the steps to configure both TPH and TPT inheritance mapping using EF Core.

Model Classes:

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

public class Electronics : ProductBase
{
    public string Brand { get; set; }
}

public class Clothing : ProductBase
{
    public string Size { get; set; }
}

TPH Mapping:

DbSet Configuration: Add DbSet properties for each class in your DbContext:

public class ProductContext : DbContext
{
    public DbSet<ProductBase> Products { get; set; }
    public DbSet<Electronics> Electronics { get; set; }
    public DbSet<Clothing> Clothing { get; set; }
}

Discriminator Configuration: Customize the discriminator column if needed:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProductBase>()
        .HasDiscriminator<string>("ProductType")
        .HasValue<Electronics>("E")
        .HasValue<Clothing>("C");
}

Generate Migration and Apply:

dotnet ef migrations add InitialTPH
dotnet ef database update

Insert Sample Data:

context.Add(new Electronics { Id = 1, Name = "Laptop", Brand = "Dell" });
context.Add(new Clothing { Id = 2, Name = "T-shirt", Size = "M" });
context.SaveChanges();

Execute a Query:

var electronics = context.Electronics.ToList();

TPT Mapping:

DbSet Configuration (same as TPH):

public class ProductContext : DbContext
{
    public DbSet<ProductBase> Products { get; set; }
    public DbSet<Electronics> Electronics { get; set; }
    public DbSet<Clothing> Clothing { get; set; }
}

Map Each Class to a Table:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProductBase>().ToTable("Products");
    modelBuilder.Entity<Electronics>().ToTable("Electronics");
    modelBuilder.Entity<Clothing>().ToTable("Clothing");
}

Generate Migration and Apply:

dotnet ef migrations add InitialTPT
dotnet ef database update

Insert Sample Data:

context.Add(new Electronics { Id = 1, Name = "Smartphone", Brand = "Samsung" });
context.Add(new Clothing { Id = 2, Name = "Jeans", Size = "L" });
context.SaveChanges();

Execute a Query:

var electronics = context.Electronics.ToList();

Choosing between TPH and TPT depends on your application's needs:

• TPH is simpler and potentially faster for querying, but can result in sparse and large tables.
• TPT provides better data integrity and validation options by enforcing non-null constraints, but requires more complex queries due to table joins.

Both approaches have trade-offs in terms of performance and data structure complexity, so testing and profiling are crucial when deciding which method best fits your application's requirements.