In EF Core 10, what SQL do db.Payments and db.Payments.OfType<CardPayment>() send under TPH, TPT and TPC, and which strategy suits which query pattern?
answer
- same LINQ, three very different statements
- TPH: one table, discriminator filter
- TPT: LEFT JOINs plus a CASE
- TPC: UNION ALL, pruned by OfType
- TPH default, TPC for leaf-type reads
basics
~20 sTPH reads one table, adding a discriminator filter for OfType<CardPayment>(). TPT joins the base table to every subtype table and computes the type with CASE. TPC unions every concrete table, and OfType narrows that to the CardPayments table. TPH suits mixed-type reads; TPC suits single-type reads.
solid answer
~40 sUnder TPH, `db.Payments` is one `SELECT` from `Payments` with no filter, and `OfType<CardPayment>()` adds `WHERE Discriminator = 'CardPayment'`, the same SQL as `db.Set<CardPayment>()`. Under TPT, the base query `LEFT JOIN`s `CardPayments`, `BankTransferPayments` and `VoucherPayments` to `Payments` and synthesizes the type with `CASE WHEN c.Id IS NOT NULL THEN 'CardPayment' ...`; asking for card payments only still joins `Payments` to `CardPayments`. Under TPC, the base query is a `UNION ALL` of the three tables, each padded with `NULL AS` columns it lacks and a literal type; `OfType<CardPayment>()` prunes the union to `CardPayments`. EF Core's guidance: TPH as the default, especially for queries across types; TPC when most queries target one leaf type and benchmarks show a gain; TPT only when an existing schema forces it.
code
sql · 12 lines-- TPC: db.Payments (column lists shortened)
SELECT c."Id", c."Amount", c."CardLast4", NULL AS "Iban", 'CardPayment' AS "Discriminator"
FROM "CardPayments" AS c
UNION ALL
SELECT b."Id", b."Amount", NULL AS "CardLast4", b."Iban", 'BankTransferPayment' AS "Discriminator"
FROM "BankTransferPayments" AS b
UNION ALL
SELECT v."Id", v."Amount", NULL AS "CardLast4", NULL AS "Iban", 'VoucherPayment' AS "Discriminator"
FROM "VoucherPayments" AS v;
-- TPC: db.Payments.OfType<CardPayment>() reads only CardPayments
-- TPH: the same query is FROM "Payments" WHERE "Discriminator" = 'CardPayment'go deeper
Recall that the same LINQ query produces different SQL for each strategy, and that TPH reads a single table.
Describe each shape: the discriminator filter in TPH, LEFT JOINs with CASE in TPT, UNION ALL padded with NULLs in TPC.
Choose the strategy from the real query mix, confirm with ToQueryString and a benchmark, and explain why TPC pruning makes leaf-type reads cheap.
Own the trade between query cost and schema shape for a hierarchy that many teams query in different ways.
## Same LINQ, three statements The mapping strategy is invisible in C#: `db.Payments.ToListAsync()` and `db.Payments.OfType<CardPayment>().ToListAsync()` compile the same way whichever strategy the hierarchy uses. What changes is the SQL, and with it the cost. The examples use an abstract `Payment` (`Id`, `Amount`, `CreatedAt`) and `CardPayment`, `BankTransferPayment` and `VoucherPayment` subclasses. ## TPH: one table, a filter | LINQ | SQL shape | |---|---| | `db.Payments` | `SELECT ... FROM Payments` with no `WHERE` | | `db.Payments.OfType<CardPayment>()` | `... WHERE Discriminator = 'CardPayment'` | | `db.Set<CardPayment>()` | the same as `OfType` | | a mid-level type with two concrete subtypes | `... WHERE Discriminator IN (...)` | Every query reads one table. An index on the discriminator column helps the filtered queries. The base query skips the filter because the discriminator mapping is complete by default; `IsComplete(false)` would add it. ## TPT: joins and a computed type Data for one entity is split across tables, so reads must join: 1. **Base query**: `Payments` `LEFT JOIN`ed to `CardPayments`, `BankTransferPayments` and `VoucherPayments` on `Id`, plus a computed column `CASE WHEN v.Id IS NOT NULL THEN 'VoucherPayment' WHEN b.Id IS NOT NULL THEN ... END`. The EF Core documentation calls this **discriminator synthesis**: whichever subtype table has a matching row decides the class. 2. **Card payments only**: `Payments` joined to `CardPayments`, the two tables that hold a card payment's columns. 3. Deeper hierarchies add one join per level and per sibling. Joins are the expensive part of relational queries, which is why the benchmark in EF Core's performance documentation measured TPT at roughly twice the time of TPH and TPC. ## TPC: unions, then pruning Each concrete type's rows live in one complete table, so reads combine tables instead of joining them: - **Base query**: `SELECT ... FROM CardPayments UNION ALL SELECT ... FROM BankTransferPayments UNION ALL SELECT ... FROM VoucherPayments`. Each branch pads the columns it does not have with `NULL AS [CardLast4]` and so on, and adds a literal such as `'CardPayment' AS [Discriminator]` so EF Core knows which class to create. - **`OfType<CardPayment>()` or `db.Set<CardPayment>()`**: EF Core prunes the union to the one table that can hold card payments. One table, no filter, no join: the best case of any strategy. - `UNION ALL` does not match or de-duplicate rows, so it is cheaper than a join, but a base query still touches every table. ## Base set versus derived set `db.Payments.OfType<CardPayment>()` and `db.Set<CardPayment>()` (or a `DbSet<CardPayment>` property) produce the same SQL under each strategy. Pick by what the code needs: - start from the base set when a query mixes conditions on base members and then narrows the type; - start from the derived set when the code only ever deals with card payments. Filtering on a derived member from the base set needs a type test, for example `Where(p => p is CardPayment && ((CardPayment)p).CardBrand == "Visa")`; EF Core translates `is` into the strategy's type check. ## Reading the SQL yourself The fastest way to settle a strategy argument is to look at the statements: 1. Call `ToQueryString()` on the `IQueryable` to print the SQL without executing it. 2. Run the two or three queries that dominate the workload, not a synthetic `SELECT *`. 3. Compare the plans in the database for joins (TPT), unions (TPC) and the discriminator filter (TPH), and check which indexes each one can use. 4. Benchmark with realistic row counts before switching; the EF Core documentation's own advice is to measure. ## Choosing by query pattern | Your queries mostly... | Prefer | |---|---| | read payments of several kinds together (statements, totals) | **TPH** | | read one leaf type at a time, in a large hierarchy with many columns per subtype | **TPC**, after a benchmark | | must fit an existing normalized schema | **TPT**, accepting the joins | The EF Core documentation sums it up: TPH is a good default for most applications, TPC pays off when most queries target a single leaf type and measurements confirm it, and TPT should be used only when external constraints require it. Check the SQL with `ToQueryString()` or the logs before and after switching, because the LINQ never shows the difference.
- Under TPT, how does EF Core know a row is a VoucherPayment when there is no discriminator column?It left-joins every subtype table to `Payments` and builds a `CASE` expression: if the `VoucherPayments` join produced a row, `v.Id` is not null and the type is `VoucherPayment`. The EF Core documentation calls this discriminator synthesis. It is correct but costs one join per subtype on every base-type query.
- A billing report sums Amount across all payment kinds every hour. Which strategy hurts it least?TPH: the report reads one table, needs no type filter and no union. TPC must `UNION ALL` every concrete table, and TPT must at least read the base table and, for full entities, join every subtype table. TPH stays the simplest fit for reports that cross payment kinds.
- OfType<CardPayment>() on db.Payments loads every payment and filters in memory.
- Under TPC, OfType<CardPayment>() still unions all the concrete tables.
- TPT reads a hidden Discriminator column to know each row's type.
- TPC is always faster than TPH because each table is smaller.
- db.Set<CardPayment>() and db.Payments.OfType<CardPayment>() send different SQL.