Single vs Split Queries: Cartesian Explosion, Round Trips, Ordering, and Consistency Tradeoffs

Learning outcomes

A dashboard needs work orders with tags, notes, and attachments. A single query returns the right graph but multiplies sibling collections; a split query removes the cross product but executes multiple SQL statements and can observe database changes between them. This lesson compares the two strategies with cardinality math, command logs, EF Core 10 ordering behavior, network/consistency tradeoffs, and real provider evidence.

1. Same-level collections multiply each other

var query = db.WorkOrders    .Where(w => w.Id == id)    .Include(w => w.WorkOrderTags)    .Include(w => w.Notes)    .Include(w => w.Attachments)    .AsSingleQuery();Console.WriteLine(query.ToQueryString());

If the principal has 5 tag links, 4 notes, and 3 attachments, the relational join can produce 60 rows for that one work order. This is cartesian explosion across sibling collections. A nested path such as WorkOrder → WorkOrderTags → Tag does not create the same sibling cross product by itself because the second collection is not another collection directly off WorkOrder.

SELECT w..., wt..., n..., a...FROM work_orders AS wLEFT JOIN work_order_tags AS wt ON w.work_order_id = wt.work_order_idLEFT JOIN work_order_notes AS n ON w.work_order_id = n.work_order_idLEFT JOIN work_order_attachments AS a ON w.work_order_id = a.WorkOrderIdWHERE w.work_order_id = @__id_0ORDER BY w.work_order_id, wt.work_order_id, wt.tag_id, n.id;

2. Data duplication is different from cartesian explosion

Even one collection Include duplicates principal columns once per child row. This is often harmless, but it matters if the principal contains a large text/BLOB/JSON payload. Cartesian explosion multiplies sibling child rows; data duplication repeats principal columns. The repairs differ: projection can omit large principal columns, while split queries can avoid sibling cross products.

3. AsSplitQuery changes execution into multiple statements

var query = db.WorkOrders    .Where(w => w.Id == id)    .Include(w => w.WorkOrderTags)    .Include(w => w.Notes)    .Include(w => w.Attachments)    .AsSplitQuery()    .TagWith("Chapter10/Lesson3 split graph");var graph = await query.SingleAsync(ct);

EF executes a root query and additional query/queries for included collection edges, then assembles the graph using keys. One-to-one related entities remain joined because they do not create collection multiplication. In current EF Core, each additional statement still represents a database round trip, so high network latency can make split mode slower even when it transfers fewer rows.

Command 1: SELECT ... FROM work_orders WHERE work_order_id = @idCommand 2: SELECT ... FROM work_orders JOIN work_order_tags ... WHERE work_order_id = @idCommand 3: SELECT ... FROM work_orders JOIN work_order_notes ... WHERE work_order_id = @idCommand 4: SELECT ... FROM work_orders JOIN work_order_attachments ... WHERE work_order_id = @id

4. EF Core 10 fixed inconsistent split-query ordering propagation

Before EF Core 10, some split queries using pagination could apply an augmented unique ordering in the first statement but fail to propagate the same ordering into a later subquery, creating an incorrect-results risk. EF Core 10 fixes that propagation. That does not make ordering optional: relational databases still provide no default row order, and production pagination should use a fully deterministic order.

var page = await db.WorkOrders    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Skip(pageIndex * pageSize)    .Take(pageSize)    .Include(w => w.Notes)    .AsSplitQuery()    .ToListAsync(ct);

The shadow CreatedUtc : DateTime is reused from Chapter 04 because it is safely orderable in the mandatory SQLite lab. The primary key tie-breaker makes the ordering unique.

5. Consistency: multiple statements can see multiple moments

A single SQL statement normally observes one statement-level database view according to that engine/isolation mode. A split query executes multiple statements. Another transaction can insert/delete/update related rows between them, so the assembled graph may combine observations from different moments. Wrapping the split query in a stronger transaction (for example, snapshot/serializable where supported and appropriate) can improve consistency but adds locking/version-store/performance tradeoffs. Provider semantics matter.

6. Deliberately wrong: make split queries a global superstition

options.UseSqlite(connectionString,    sqlite => sqlite.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

A global default can be valid for a known workload, but it is not a free optimization. It can increase round trips, buffering, and repeated reference joins. Conversely, leaving single-query mode everywhere can trigger EF warnings and huge joins. The repair is to define a default intentionally and override hot queries with AsSingleQuery/AsSplitQuery based on evidence.

var graph = await db.WorkOrders    .Include(w => w.WorkOrderTags)    .Include(w => w.Notes)    .AsSplitQuery() // justified by measured sibling cardinality    .ToListAsync(ct);

7. Build an evidence sheet, not a folklore rule

Capture dataset size/distribution, collection cardinalities, selected columns, tracking mode, command count, elapsed time in Release mode, database plan/metrics, and network topology. If you cannot reproduce the production latency/topology locally, record that limitation instead of inventing a number.

8. Hands-on lab: same graph, two execution strategies

1. Seed 20 work orders with deterministic child cardinalities, including one “wide” work order with 5 tags, 4 notes, and 3 attachments.
2. Run the same include graph with AsSingleQuery() and AsSplitQuery().
3. Capture ToQueryString(), EF command logs, and result counts.
4. Use SQLite EXPLAIN QUERY PLAN on the emitted statements where useful; do not treat it as a cross-provider plan.
5. Add deterministic pagination using CreatedUtc + Id and verify stable keys across repeated runs.
6. Record a decision table for local SQLite and separately state what must be retested on the production provider/network.

9. Production judgment and bridge

Use AsSplitQuery when it solves a measured collection-join problem and its extra statements/consistency window are acceptable. Use AsSingleQuery when the joined shape is bounded and round-trip latency dominates. Often the best answer is a projection that avoids loading an entity graph at all. Lesson 4 covers another multiple-query strategy—explicit loading—where the application decides exactly when a relationship query runs.

Authoritative references

• Single vs. Split Queries - EF Core — cartesian explosion, data duplication, split-query characteristics and configuration.
• What's New in EF Core 10 — consistent ordering fix for split queries.
• Eager Loading of Related Data - EF Core — Include behavior and collection-loading caution.
• Using Transactions - EF Core — transaction/isolation coordination.
• SQLite Query Planning — local-plan evidence for the free lab.