Choose Mapping Strategies with Measured SQL Plans Instead of Object-Oriented Preference

Learning outcomes

TPH, TPT, and TPC all map the same C# hierarchy and can all be “correct.” The engineering question is which mapping fits ServiceHub’s actual query/write/migration workload. This lesson builds an evidence pack: identical data, identical LINQ projections, generated SQL, SQLite plans, command counts, file/schema shape, and repeatable timing. The goal is not to crown a universal winner.

1. A fair comparison controls variables

Use the same ServiceTarget hierarchy, same deterministic IDs, same row counts, same equipment/facility distribution, same query projections, same tracking mode, and same machine/runtime. Only mapping strategy should change. Record .NET/EF/provider versions and database settings alongside results.

.NET SDK: 10.0.400.NET runtime: 10.0.11EF Core: 10.0.11Microsoft.EntityFrameworkCore.Sqlite: 10.0.11OS/CPU/RAM: <record locally>Databases: servicehub-tph.db / servicehub-tpt.db / servicehub-tpc.dbDataset: <record total + subtype distribution>Tracking: AsNoTracking for read benchmarkLogging during timing: <on/off; disclose>Warmup iterations: <record>Measured iterations: <record>SQLite journal/page/cache settings: <record if changed>

2. Define workloads before measuring

3. Capture LINQ and generated SQL first

static IQueryable<TargetRow> ActiveTargets(DbContext db) =>    db.Set<ServiceTarget>()      .AsNoTracking()      .Where(x => x.IsActive)      .OrderBy(x => x.TargetCode)      .Select(x => new TargetRow(x.Id, x.TargetCode, x.DisplayName));var query = ActiveTargets(db);Console.WriteLine(query.ToQueryString());var rows = await query.ToListAsync(ct);

ToQueryString() proves translation shape; it is not an execution plan and does not include network, locking, cache state, or actual row counts. Store each strategy’s SQL beside the results.

4. Ask SQLite for the plan—but keep provider scope explicit

-- TPHEXPLAIN QUERY PLANSELECT Id, target_code, display_nameFROM service_targets_tphWHERE is_active = 1ORDER BY target_code;-- TPT/TPC: paste the actual SELECT emitted/logged by your lab,-- then prefix it with EXPLAIN QUERY PLAN.

SQLite’s plan vocabulary differs from SQL Server actual execution plans, PostgreSQL EXPLAIN (ANALYZE, BUFFERS), MySQL EXPLAIN ANALYZE, and Oracle plans. The local lab teaches evidence collection, not cross-engine plan equivalence.

5. Time repeated end-to-end queries without pretending one stopwatch is science

static async Task<double[]> MeasureAsync(    Func<Task> operation,    int warmups,    int iterations){    for (var i = 0; i < warmups; i++)        await operation();    var samples = new double[iterations];    for (var i = 0; i < iterations; i++)    {        var sw = Stopwatch.StartNew();        await operation();        sw.Stop();        samples[i] = sw.Elapsed.TotalMilliseconds;    }    return samples;}static double Percentile(double[] values, double p){    var ordered = values.OrderBy(x => x).ToArray();    var index = (int)Math.Clamp(        Math.Ceiling(p * ordered.Length) - 1,        0, ordered.Length - 1);    return ordered[index];}

Report median/p95 together with dataset/environment, not a single best run. For serious microbenchmarks, BenchmarkDotNet can add process/warmup rigor, but it cannot replace database plan and topology analysis.

6. Count commands and table touches on writes

optionsBuilder.LogTo(    Console.WriteLine,    new[] { DbLoggerCategory.Database.Command.Name },    LogLevel.Information);// Insert the same deterministic EquipmentTarget in each strategy context.// Record the emitted INSERT commands, affected tables, and transaction boundary.

Expected mechanism: TPH writes one hierarchy row; TPT writes base + derived rows; TPC writes one concrete row. Actual batching/provider behavior is version-specific, so record commands rather than assuming a fixed round-trip count.

7. Compare schema/migration blast radius, not only SELECT latency

dotnet ef migrations add AddTargetOwnerTph   --context TphMappingContext   --output-dir Data/Migrations/Tphdotnet ef migrations add AddTargetOwnerTpt   --context TptMappingContext   --output-dir Data/Migrations/Tptdotnet ef migrations add AddTargetOwnerTpc   --context TpcMappingContext   --output-dir Data/Migrations/Tpc

A new base property typically means one table change in TPH/TPT but a column added to every concrete table in TPC. Conversely, splitting/normalizing can make some constraints easier or harder. Review generated migrations; do not infer operational risk from LOC counts alone.

8. Decision matrix: evidence plus domain constraints

9. Deliberately wrong benchmark: one query, ten rows, Debug logging

A developer runs each strategy once on ten rows with sensitive/detailed logging enabled and declares the fastest. That result mixes JIT, model initialization, cache state, console I/O, and tiny-data noise. It proves almost nothing about production.

Repair the experiment

1. Use representative cardinality and subtype distribution.
2. Separate startup/model cost from steady-state query cost.
3. Warm up deliberately and record warmup policy.
4. Disable noisy logging during timed runs, but keep a separate evidence run for SQL/commands.
5. Run repeated samples and report a distribution.
6. Inspect the database plan and indexes.
7. Repeat on the production-like provider before a production mapping decision.

10. Hands-on capstone: produce a mapping decision record

1. Reset and seed all three SQLite strategy databases with identical deterministic data.
2. Run W1–W5 from this lesson and save SQL, plans, timings, command counts, and migration diffs.
3. Fill in the measurement manifest completely.
4. Write one paragraph explaining which mapping you would choose for ServiceHub under the measured workload.
5. Write one paragraph naming a workload change that would make you reconsider.
6. State provider-specific validation still required before deployment.
7. Describe migration/rollback ownership if changing an existing hierarchy mapping with real data.

11. Chapter 07 production judgment and bridge

Inheritance mapping is an architectural choice with schema, SQL, key, constraint, migration, and performance consequences. Prefer the simplest mapping that satisfies the domain and measured workload, and keep mapping strategy changes rare because production data migration can be substantial. Chapter 08 now turns from model shape to the EF query pipeline itself: IQueryable, expression trees, deferred execution, translation, async execution, and generated SQL.

Authoritative references

• Inheritance - EF Core — authoritative TPH/TPT/TPC mechanics and constraints
• Modeling for Performance - EF Core — measure inheritance mapping strategies
• Efficient Querying - EF Core — query/plan/index evidence
• Advanced Table Mapping - EF Core — table/entity splitting tradeoffs used in the chapter
• Simple Logging - EF Core — capturing database command evidence
• .NET 10 download — current SDK/runtime baseline checkpoint