TPC Inheritance: Duplicated Columns, Key Generation, and Query Performance

Learning outcomes

In the ServiceHub mapping lab, TPT normalized inherited properties into a base table but paid join costs. Table-per-concrete-type (TPC) takes the opposite route: no table is created for an abstract base; each concrete subtype table repeats all inherited columns. Polymorphic reads become unions instead of inheritance joins. On SQLite, TPC also exposes a critical key-generation constraint that the lab must handle honestly.

1. Same objects, no base table

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; }}

The abstract ServiceTarget contributes properties and EF metadata, but TPC stores equipment rows only in equipment_targets_tpc and facility rows only in facility_targets_tpc. Each table repeats TargetCode, DisplayName, RegisteredUtc, and IsActive.

2. Configure TPC deliberately

modelBuilder.Entity<ServiceTarget>(builder =>{    builder.UseTpcMappingStrategy();    builder.HasKey(x => x.Id);    builder.Property(x => x.Id).ValueGeneratedNever();    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");});modelBuilder.Entity<EquipmentTarget>(builder =>{    builder.ToTable("equipment_targets_tpc");    builder.Property(x => x.Manufacturer).HasColumnName("manufacturer").HasMaxLength(120);    builder.Property(x => x.SerialNumber).HasColumnName("serial_number").HasMaxLength(120);    builder.HasIndex(x => x.TargetCode).HasDatabaseName("ix_equipment_tpc_code");});modelBuilder.Entity<FacilityTarget>(builder =>{    builder.ToTable("facility_targets_tpc");    builder.Property(x => x.SiteCode).HasColumnName("site_code").HasMaxLength(60);    builder.Property(x => x.Floor).HasColumnName("floor").HasMaxLength(40);    builder.HasIndex(x => x.TargetCode).HasDatabaseName("ix_facility_tpc_code");});

ValueGeneratedNever makes the lab’s client-generated Guid explicit. That is not required for every TPC/provider combination; it is a transparent choice for the SQLite baseline.

3. Migration evidence: inherited columns are duplicated

CREATE TABLE equipment_targets_tpc (    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,    manufacturer TEXT NOT NULL,    serial_number TEXT NOT NULL);CREATE TABLE facility_targets_tpc (    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,    site_code TEXT NOT NULL,    floor TEXT NULL);

There is no service_targets_tpc base table. Schema duplication is intentional. A change to a base-mapped property can touch every concrete table’s migration/index design.

4. Polymorphic query = combine concrete tables

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

SELECT u.Id, u.target_code, u.display_name, u.registered_utc, u.is_active,       u.manufacturer, u.serial_number, u.site_code, u.floor, u.target_kindFROM (    SELECT e.Id, e.target_code, e.display_name, e.registered_utc, e.is_active,           e.manufacturer, e.serial_number,           NULL AS site_code, NULL AS floor,           'equipment' AS target_kind    FROM equipment_targets_tpc AS e    UNION ALL    SELECT f.Id, f.target_code, f.display_name, f.registered_utc, f.is_active,           NULL AS manufacturer, NULL AS serial_number,           f.site_code, f.floor,           'facility' AS target_kind    FROM facility_targets_tpc AS f) AS uWHERE u.is_active = 1ORDER BY u.target_code;

The exact SQL can differ, but TPC must combine concrete sets for a polymorphic base query. Derived-only queries can go directly to one concrete table without a base join.

5. The key must be unique across the hierarchy

EF’s identity map is keyed by hierarchy identity, not “table + key.” An equipment target and facility target cannot both have Id=1 and be valid instances in the same hierarchy. SQL Server’s EF provider solves integer TPC generation with a shared database sequence by default. SQLite has no sequences and no configurable identity seed/increment strategy suitable for disjoint TPC integer ranges, so the official EF documentation states integer key generation is unsupported for SQLite TPC. Client-generated/global keys such as Guid work.

6. Deliberately broken design: independent AUTOINCREMENT tables

-- WRONG mental model for TPC identity:CREATE TABLE equipment_targets_tpc_bad (    Id INTEGER PRIMARY KEY AUTOINCREMENT,    target_code TEXT NOT NULL);CREATE TABLE facility_targets_tpc_bad (    Id INTEGER PRIMARY KEY AUTOINCREMENT,    target_code TEXT NOT NULL);-- First insert into each table can generate Id = 1 in both tables.

The database sees two valid independent keys; EF sees duplicate hierarchy identity. Fix the model by using a global key generator. In the cross-platform lab, create Guid.NewGuid() values in the domain/factory and persist them unchanged.

7. Polymorphic foreign keys lose a simple relational target

Suppose WorkOrder had one ServiceTargetId that could refer to any subtype. With TPH or TPT, a single base table contains every target key and can be referenced by one FK. With TPC, equipment and facility keys live in separate tables; a conventional FK cannot point to “one of these tables.” EF’s inheritance documentation calls out this referential-integrity limitation. You may need a different relational design, separate subtype FKs, or application-level validation—none is a free abstraction.

8. Storage and index duplication are the price for join-free subtype reads

9. Hands-on lab: prove TPC’s different cost center

1. Create TpcMappingContext against servicehub-tpc.db.
2. Reuse the same deterministic Guid IDs and seed rows.
3. Generate/review Data/Migrations/Tpc; verify no abstract-base table exists.
4. Capture base and equipment-only SQL with ToQueryString().
5. Run SQLite EXPLAIN QUERY PLAN and record the union/subquery structure.
6. Create the two scratch AUTOINCREMENT tables, show duplicate integer IDs across concrete tables, then drop/reset them.
7. Insert the same logical row count used for TPH/TPT and compare database file size only after recording page-size/vacuum conditions.
8. Document how a future WorkOrder -> ServiceTarget FK would differ under TPC.

10. Production judgment and bridge

TPC can be attractive when derived-only reads dominate and duplicated base columns are acceptable. It is less attractive when polymorphic queries, cross-hierarchy FKs, frequent base-property schema changes, or global integer-key generation are central. Keep provider capabilities explicit. Next, we leave inheritance strategies and examine two orthogonal legacy techniques: multiple entities sharing one row (table splitting) and one entity spanning several required rows (entity splitting).

Authoritative references

• Inheritance - EF Core — TPC mapping, UNION semantics, key generation, SQLite limitation, FK consequences
• Modeling for Performance - EF Core — inheritance strategy performance tradeoffs
• SQLite Provider - EF Core — SQLite provider behavior/limitations
• Keys - EF Core — entity identity and generated values
• SQLite AUTOINCREMENT — SQLite rowid/autoincrement semantics for the broken lab comparison