TPT Inheritance: Join Costs, Schema Clarity, and Workload Tradeoffs

Learning outcomes

In the ServiceHub mapping lab, TPH put the entire hierarchy in one table. A common reaction is “normalize it”: put base properties in one table and subtype properties in separate tables. EF Core calls that table-per-type (TPT). The schema can look elegant, but polymorphic reads need relational joins to reconstruct complete objects. This lesson makes those joins visible before anyone labels TPT “cleaner.”

1. The same CLR hierarchy, a different relational decomposition

public abstract class ServiceTarget{    protected ServiceTarget(Guid id, string targetCode, string displayName)    {        Id = id;        TargetCode = targetCode;        DisplayName = displayName;    }    protected ServiceTarget() { }    public Guid Id { get; private set; }    public string TargetCode { get; private set; } = string.Empty;    public string DisplayName { get; private set; } = string.Empty;    public DateTime RegisteredUtc { get; private set; }    public bool IsActive { get; private set; } = true;}public sealed class EquipmentTarget : ServiceTarget{    private EquipmentTarget() { }    public EquipmentTarget(Guid id, string code, string name,        string manufacturer, string serialNumber)        : base(id, code, name)    {        Manufacturer = manufacturer;        SerialNumber = serialNumber;    }    public string Manufacturer { get; private set; } = string.Empty;    public string SerialNumber { get; private set; } = string.Empty;}public sealed class FacilityTarget : ServiceTarget{    private FacilityTarget() { }    public FacilityTarget(Guid id, string code, string name,        string siteCode, string? floor)        : base(id, code, name)    {        SiteCode = siteCode;        Floor = floor;    }    public string SiteCode { get; private set; } = string.Empty;    public string? Floor { get; private set; }}

Keep the exact same Guid keys, sample rows, projections, and predicates used in TPH. Only the mapping strategy changes. That isolation is essential if the final comparison is to say anything useful.

2. Configure TPT on the root

modelBuilder.Entity<ServiceTarget>(builder =>{    builder.UseTptMappingStrategy();    builder.ToTable("service_targets_tpt");    builder.HasKey(x => x.Id);    builder.Property(x => x.TargetCode).HasColumnName("target_code").HasMaxLength(40);    builder.Property(x => x.DisplayName).HasColumnName("display_name").HasMaxLength(160);    builder.Property(x => x.RegisteredUtc).HasColumnName("registered_utc");    builder.Property(x => x.IsActive).HasColumnName("is_active");    builder.HasIndex(x => x.TargetCode).HasDatabaseName("ix_service_targets_tpt_code");});modelBuilder.Entity<EquipmentTarget>(builder =>{    builder.ToTable("equipment_targets_tpt");    builder.Property(x => x.Manufacturer).HasColumnName("manufacturer").HasMaxLength(120);    builder.Property(x => x.SerialNumber).HasColumnName("serial_number").HasMaxLength(120);});modelBuilder.Entity<FacilityTarget>(builder =>{    builder.ToTable("facility_targets_tpt");    builder.Property(x => x.SiteCode).HasColumnName("site_code").HasMaxLength(60);    builder.Property(x => x.Floor).HasColumnName("floor").HasMaxLength(40);});

3. Migration evidence: base row plus one derived row

CREATE TABLE service_targets_tpt (    Id TEXT NOT NULL PRIMARY KEY,    target_code TEXT NOT NULL,    display_name TEXT NOT NULL,    registered_utc TEXT NOT NULL,    is_active INTEGER NOT NULL);CREATE TABLE equipment_targets_tpt (    Id TEXT NOT NULL PRIMARY KEY,    manufacturer TEXT NOT NULL,    serial_number TEXT NOT NULL,    FOREIGN KEY (Id) REFERENCES service_targets_tpt(Id));CREATE TABLE facility_targets_tpt (    Id TEXT NOT NULL PRIMARY KEY,    site_code TEXT NOT NULL,    floor TEXT NULL,    FOREIGN KEY (Id) REFERENCES service_targets_tpt(Id));

An equipment target is now represented by two rows with the same key. Saving one object therefore requires coordinated commands across the base and equipment tables. The database enforces the derived-to-base link through the key/foreign-key relationship.

4. A polymorphic base query reconstructs type through joins

var query = db.Set<ServiceTarget>()    .AsNoTracking()    .Where(x => x.IsActive)    .OrderBy(x => x.TargetCode);Console.WriteLine(query.ToQueryString());

SELECT b.Id, b.target_code, b.display_name, b.registered_utc, b.is_active,       e.manufacturer, e.serial_number,       f.site_code, f.floor,       CASE         WHEN e.Id IS NOT NULL THEN 'equipment'         WHEN f.Id IS NOT NULL THEN 'facility'       END AS discriminatorFROM service_targets_tpt AS bLEFT JOIN equipment_targets_tpt AS e ON b.Id = e.IdLEFT JOIN facility_targets_tpt AS f ON b.Id = f.IdWHERE b.is_active = 1ORDER BY b.target_code;

Exact SQL is provider/version dependent, but the relational mechanism is stable: EF must join enough derived tables to know which subtype and subtype values exist.

5. Derived-only queries still join the base table

var equipment = db.Set<EquipmentTarget>()    .AsNoTracking()    .Where(x => x.Manufacturer == "Fabrikam")    .Select(x => new { x.TargetCode, x.Manufacturer, x.SerialNumber });Console.WriteLine(equipment.ToQueryString());

SELECT b.target_code, e.manufacturer, e.serial_numberFROM service_targets_tpt AS bINNER JOIN equipment_targets_tpt AS e ON b.Id = e.IdWHERE e.manufacturer = 'Fabrikam';

TPT does not allow EF to skip the base table when the projection needs base properties. Deeper hierarchies add more joins along the inheritance chain.

6. Deliberately wrong assumption: “normalized means faster”

Normalization reduces certain update anomalies and duplication, but TPT inheritance is an ORM mapping shape, not a performance theorem. A report that touches every subtype can pay for several joins per query. Conversely, a narrow subtype-specific query on well-indexed tables may be perfectly acceptable. The only defensible answer comes from workload-specific plans and timings.

EXPLAIN QUERY PLANSELECT b.target_code, e.manufacturer, e.serial_numberFROM service_targets_tpt AS bJOIN equipment_targets_tpt AS e ON b.Id = e.IdWHERE e.manufacturer = 'Fabrikam';

7. Cross-table index limitations matter

Because inherited and derived properties live in different tables, a single database index cannot cover a key made from ServiceTarget.TargetCode plus EquipmentTarget.Manufacturer. EF’s inheritance documentation explicitly warns that composite foreign keys/indexes spanning inherited and declared properties cannot be created as one relational constraint/index under TPT. Design indexes per table and inspect the resulting plan.

8. Write path: one entity can mean multiple SQL commands

var equipment = new EquipmentTarget(    Guid.Parse("22222222-2222-2222-2222-222222222222"),    "EQ-220", "Chiller 220", "Fabrikam", "SN-220");db.Add(equipment);Console.WriteLine(db.ChangeTracker.DebugView.LongView);await db.SaveChangesAsync(ct);

Enable EF command logging and observe one insert into the base table and another into the equipment table within the save operation. This is not “bad”; it is simply the relational consequence you must account for in write-heavy workloads and migrations.

9. Hands-on lab: hold everything except mapping constant

1. Create TptMappingContext against servicehub-tpt.db.
2. Use the same hierarchy and deterministic seed keys as Lesson 1.
3. Generate/review a separate migration in Data/Migrations/Tpt.
4. Capture base, equipment-only, and projected queries with ToQueryString().
5. Run EXPLAIN QUERY PLAN for each and save the raw output.
6. Enable command logging and record how many INSERT commands one new EquipmentTarget produces.
7. Add a scratch third-level subtype only in the lab and observe join growth; remove/reset it afterward.
8. Compare TPH/TPT schema files and explain which cost moved from row width to joins/write coordination.

10. Production judgment and bridge

TPT can fit legacy schemas or domains where subtype tables already exist and polymorphic reads are limited. Avoid choosing it just because the schema mirrors the class diagram. Review join counts, row counts, indexes, transaction/write volume, and migration impact. Lesson 3 tests the opposite decomposition: TPC removes base-table joins by duplicating inherited columns into every concrete table—and creates a different key-generation/FK problem.

Authoritative references

• Inheritance - EF Core — TPT mapping, joins, warnings, and table-specific facets
• Modeling for Performance - EF Core — TPH/TPT/TPC performance guidance
• Advanced Table Mapping - EF Core — related multi-table mapping mechanics
• Efficient Querying - EF Core — database-plan evidence and query shaping
• SQLite EXPLAIN QUERY PLAN — local plan inspection