TPH Inheritance: Discriminators, Completeness, Query Shape, and Index Design

Learning outcomes

ServiceHub already has a mature WorkOrder aggregate. Inheritance is a different modeling pressure: operations now wants one “service target” concept for equipment and facilities, while reports often query all targets polymorphically. EF Core can map that class hierarchy several ways. This chapter keeps the hierarchy in a disposable ServiceHub.MappingLab so we can compare schemas and SQL without rewriting the main course database.

1. One object hierarchy does not imply one database design

A discriminator is a column whose value tells EF which CLR type a row represents. In table-per-hierarchy (TPH), the base and every derived type share one table. Columns needed only by one subtype are usually nullable for rows of sibling types. EF Core uses TPH by default for relational inheritance unless another strategy is configured.

2. Add the isolated ServiceTarget hierarchy

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 Guid key is intentional: it stays valid across TPH, TPT, and the SQLite TPC lab, letting later lessons compare mapping strategy instead of changing identity generation at the same time.

3. Configure explicit TPH discriminator metadata

modelBuilder.Entity<ServiceTarget>(builder =>{    builder.ToTable("service_targets_tph");    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.HasDiscriminator<string>("target_kind")        .HasValue<EquipmentTarget>("equipment")        .HasValue<FacilityTarget>("facility");    builder.HasIndex("target_kind", nameof(ServiceTarget.TargetCode))        .HasDatabaseName("ix_service_targets_tph_kind_code");});modelBuilder.Entity<EquipmentTarget>(builder =>{    builder.Property(x => x.Manufacturer).HasColumnName("manufacturer").HasMaxLength(120);    builder.Property(x => x.SerialNumber).HasColumnName("serial_number").HasMaxLength(120);});modelBuilder.Entity<FacilityTarget>(builder =>{    builder.Property(x => x.SiteCode).HasColumnName("site_code").HasMaxLength(60);    builder.Property(x => x.Floor).HasColumnName("floor").HasMaxLength(40);});

4. Migration evidence: one table, one key, sparse subtype columns

CREATE TABLE service_targets_tph (    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,    target_kind TEXT NOT NULL,    manufacturer TEXT NULL,    serial_number TEXT NULL,    site_code TEXT NULL,    floor TEXT NULL);CREATE INDEX ix_service_targets_tph_kind_codeON service_targets_tph (target_kind, target_code);

The generated migration is authoritative; this DDL shows the expected shape. TPH’s “one table” convenience is visible, but so is the cost: every equipment row has facility columns and every facility row has equipment columns.

5. Derived queries carry a discriminator predicate

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

SELECT ...FROM service_targets_tph AS sWHERE s.target_kind = 'equipment'  AND s.is_active = 1ORDER BY s.target_code;

OfType<EquipmentTarget> changes the database predicate; it is not merely a C# cast after materialization. A base query normally represents every mapped discriminator value and therefore may not need a discriminator predicate when the mapping is complete.

6. Observe the discriminator in runtime metadata

var targetType = db.Model.FindEntityType(typeof(ServiceTarget))!;var discriminator = targetType.FindDiscriminatorProperty();Console.WriteLine($"Discriminator property: {discriminator?.Name}");Console.WriteLine($"Column: {discriminator?.GetColumnName()}");foreach (var type in targetType.GetDerivedTypesInclusive()){    Console.WriteLine($"{type.DisplayName()} => {type.GetDiscriminatorValue()}");}

This proves what the EF model believes. It does not prove index usefulness, row distribution, or runtime latency; those require database evidence.

7. Deliberately broken case: a legacy discriminator appears

Suppose a previous application inserted target_kind='legacy_meter'. With a complete mapping, a base query can retrieve that row and EF cannot materialize it because no CLR type owns that discriminator. The result is a materialization exception—not a new dynamic subtype.

INSERT INTO service_targets_tph(Id, target_code, display_name, registered_utc, is_active, target_kind)VALUES('11111111-1111-1111-1111-111111111111', 'LEG-001', 'Legacy meter', '2026-08-27T00:00:00Z', 1, 'legacy_meter');

Repair A: map the legacy type

If the row is still part of the application domain, model the CLR subtype and explicit discriminator value.

Repair B: declare the mapping incomplete

builder.HasDiscriminator<string>("target_kind")    .HasValue<EquipmentTarget>("equipment")    .HasValue<FacilityTarget>("facility")    .IsComplete(false);

IsComplete(false) tells EF to add discriminator filtering even for base queries. It avoids materializing unknown rows, but it also makes those rows invisible through this model. That is an integration decision that needs explicit ownership and tests.

8. Index the workload, not the inheritance acronym

The composite (target_kind, target_code) index helps only if workload predicates/orderings can use that leading column order. If most queries ask for target_code across all target types, the discriminator-leading index may be inferior to a different index. SQLite EXPLAIN QUERY PLAN and production-engine plans are the evidence.

EXPLAIN QUERY PLANSELECT target_code, display_nameFROM service_targets_tphWHERE target_kind = 'equipment'ORDER BY target_code;

9. Hands-on lab: build and break the TPH model

1. Create ServiceHub.MappingLab targeting net10.0 with EF Core/SQLite/Design 10.0.11.
2. Add the shared ServiceTarget hierarchy and TphMappingContext.
3. Create Data/Migrations/Tph, review the migration, and apply it only to servicehub-tph.db.
4. Seed deterministic equipment/facility rows and print model discriminator metadata.
5. Capture ToQueryString() for base and OfType<EquipmentTarget> queries.
6. Insert the disposable legacy_meter row and reproduce the complete-mapping failure.
7. Apply IsComplete(false), rerun, and verify the unknown row is filtered rather than materialized.
8. Run EXPLAIN QUERY PLAN before/after the discriminator-aware index; record observed plan text instead of inventing performance numbers.

10. Production judgment and bridge

TPH is usually the first strategy to test because one-table polymorphic queries are simple and key generation is straightforward. It is not automatically best: a very wide hierarchy can produce sparse rows, large indexes, and awkward subtype constraints. Keep discriminator values stable across deployments and review migration/data compatibility before renaming them. Next, TPT trades sparse columns for a normalized-looking table hierarchy—and introduces joins that must be measured.

Authoritative references

• Inheritance - EF Core — TPH discriminator configuration, IsComplete, shared columns, TPT/TPC alternatives
• Modeling for Performance - EF Core — why mapping strategy must be measured
• Indexes - EF Core — index order and relational metadata
• Efficient Querying - EF Core — query-shape and plan-oriented performance guidance
• SQLite Query Planner — SQLite plan/index behavior for the free local lab