273 - Query Optimization · dotnetmadeeasy.com

A correct EF Core query and a fast one are not automatically the same query — the gap between them is almost always the shape of what you asked for, not a missing index.

Picture a screen that lists blog posts, and for each post shows its comments and its tags. The obvious LINQ query is one line:

List<Post> posts = await context.Posts
    .Include(p => p.Comments)
    .Include(p => p.Tags)
    .ToListAsync();

No N+1 here — you already know from Advanced Part V's LINQ performance lesson that Include is exactly how you avoid a query-per-row disaster. And yet, on a post with 40 comments and 6 tags, this single "optimized" query can come back slower and heavier than you'd expect — sometimes dramatically so. Not because Include is wrong. Because two Include calls on two different collections, translated into one SQL query, don't just add rows together — they multiply them.

This lesson is about making EF Core queries genuinely fast: a quick recap of N+1 avoidance (already covered — this isn't a re-teach), a deep look at AsSplitQuery() and the cartesian-product problem it exists to solve, .Select() projection as a first-class performance technique, and a look at what EF Core already caches for you automatically — which sets up the next lesson's narrower tool, compiled queries.

What Is It?

The Simple Explanation

Query optimization, in the EF Core sense, is choosing the query shape that gets you the data you need in the fewest round trips and the smallest transferred payload — without accidentally asking the database to do far more work than the question actually requires. It's rarely about clever SQL tricks; it's almost always about recognizing which of a small number of well-known shapes (N+1, cartesian-product joins, over-fetching) your query has accidentally taken, and reshaping it.

The Technical Definition

Three concrete tools sit at the center of this lesson: AsSplitQuery(), which tells EF Core to translate a query with multiple Included collection navigations into several separate SQL queries instead of one joined query; .Select() projection, which tells EF Core to fetch only the specific columns your code actually reads, instead of every mapped column on every entity in the query; and EF Core's own internal query-shape cache, an automatic mechanism (already referenced in the LINQ-to-EF-Core lesson) that avoids re-translating the same LINQ expression tree into SQL on every single call.

Why Does It Exist?

Quick Recap — N+1 Is Already Solved, Not Re-Taught Here

Advanced Part V's "Avoiding LINQ Performance Traps" already covered N+1 in full depth — the failure mode where a loop that reads a navigation property per row turns one query into N+1 queries, and the fix: eager-load with Include(...), or fetch exactly what you need with a .Select() projection across the navigation, so everything comes back in one round trip. If any of that feels unfamiliar, go back to that lesson first — this one assumes you have it solid, and moves straight to the next layer of the problem: what happens once you're already eager-loading correctly, but eager-loading more than one collection at once.

The Problem — a JOIN of Two Collections Multiplies, It Doesn't Add

Here's the part N+1 avoidance doesn't warn you about. Include(p => p.Comments) alone is fine — EF Core generates one query with a JOIN against Comments, and you get back one row per comment (with the post's columns repeated on each). Include(p => p.Tags) alone is equally fine. But Include(p => p.Comments).Include(p => p.Tags) together, translated as one SQL query, means the database joins Posts to Comments and to Tags in the same query — and a join across two independent one-to-many collections produces a cartesian product between them: a post with 40 comments and 6 tags doesn't come back as 40 + 6 = 46 rows, it comes back as 40 × 6 = 240 rows, each one repeating all of that post's own columns yet again. Add a third collection and the multiplication compounds further. The result set EF Core has to transfer and then de-duplicate back into your object graph gets wastefully larger the more collections you include together in one query — not because your data grew, but purely because of how a single joined query has to represent multiple independent one-to-many relationships at once.

The Solution — Split the Collections Into Separate Queries

AsSplitQuery() tells EF Core: instead of one query joining everything together, issue one query for the main entity plus one additional query per included collection navigation, and stitch the results back together into the same object graph in memory. No cartesian multiplication — each collection is fetched in its own right-sized query. The trade-off is exactly what you'd expect from trading one query for several: it's no longer a single atomic round trip, so a row could theoretically be added to Comments between the first and second query executing. For the vast majority of read scenarios that's a non-issue; where it genuinely matters, that's a signal you may need a transaction around the read, not a reason to avoid split queries altogether.

Big Picture

Single Query, Two Collections Included

One SQL query, two JOINs (Comments, Tags)
1 post × 40 comments × 6 tags = 240 rows over the wire
Post's own columns repeated 240 times
One round trip — but a bloated one

AsSplitQuery()

Three SQL queries: Posts, then Comments, then Tags
1 + 40 + 6 = 47 rows total, across all three
No repeated post columns at all
Three round trips — but each one right-sized

Same data, same final object graph in your code either way — post.Comments and post.Tags are populated identically at the end of both. The difference is entirely in how much data physically crossed the network to get there, and how much duplicate work the database and EF Core did assembling it.

How It Works

DECIDING HOW TO SHAPE A QUERY, STEP BY STEP

Step 1 — Do you need related data at all, or only a few of its fields?

If you only ever read two or three fields off the related entity, don't Include it at all — project with .Select() instead. Skip straight to Step 3.

▼

Step 2 — Including exactly one collection navigation?

A single Include on one collection is fine as one joined query — there's no second collection to multiply against, so there's no cartesian problem to solve.

▼

Step 3 — Including two or more collection navigations together?

Add .AsSplitQuery() to the end of the query. EF Core issues one query per collection instead of one joined query for all of them.

▼

Step 4 — Only reading a handful of fields, never the whole entity?

Reach for .Select() to project exactly those fields — narrower than Include, and it sidesteps the split-vs-single decision entirely because there's no collection navigation being materialized as tracked entities at all.

Simple Example — AsSplitQuery() in Practice

// Single query — two Includes joined together, cartesian-product-shaped
List<Post> posts = await context.Posts
    .Include(p => p.Comments)
    .Include(p => p.Tags)
    .ToListAsync();

// Split query — one query for Posts, one for Comments, one for Tags
List<Post> postsSplit = await context.Posts
    .Include(p => p.Comments)
    .Include(p => p.Tags)
    .AsSplitQuery()
    .ToListAsync();

The two calls populate post.Comments and post.Tags identically — AsSplitQuery() changes how the data is fetched, not what your object graph looks like afterward. You can also set it as the default for every query on a DbContext, and override per-query where needed:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(connectionString,
        o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
}

// Opt a specific query back into a single query, if you deliberately want that:
var single = await context.Posts.Include(p => p.Comments).AsSingleQuery().ToListAsync();

Real-World Example — an Order Detail Screen

An e-commerce order-detail page needs an order's line items, its shipment tracking events, and its payment attempts — three independent one-to-many collections hanging off Order. Joined together in one query, a busy order with 8 line items, 5 tracking events, and 3 payment attempts returns 8 × 5 × 3 = 120 rows, every one of them repeating the order's own columns. Split, it's three separate, right-sized queries returning 8 + 5 + 3 = 16 rows total, plus one row for the order itself.

Order? order = await context.Orders
    .Include(o => o.LineItems)
    .Include(o => o.TrackingEvents)
    .Include(o => o.PaymentAttempts)
    .AsSplitQuery()
    .FirstOrDefaultAsync(o => o.Id == orderId);

And for the order list screen — where each row in the list only ever shows the customer's name and the item count, never the full line items — reshaping the query with .Select() is a better fix than either single- or split-Include ever could be, because it avoids fetching the collections at all:

var summaries = await context.Orders
    .Select(o => new
    {
        o.Id,
        CustomerName = o.Customer!.Name,
        ItemCount = o.LineItems.Count
    })
    .ToListAsync();
// One query. No Include. No tracked Order or LineItem entities materialized at all.

This is the exact same projection technique Intermediate's .Select() lesson taught, and the same idea behind AsNoTracking() from the tracking-vs-no-tracking lesson: fetch only the shape the screen actually needs, and skip change-tracking overhead for data you'll never save back. Projection composes with both — a read-only, narrowly-shaped, untracked query is usually the single fastest thing EF Core can do for you.

Analogy

One Combined Invoice vs. Three Separate Receipts

Imagine a store that sold you a jacket in 3 sizes tried on and 2 colors considered, and insists on printing one combined receipt listing every size-color combination as its own line — 3 × 2 = 6 lines — even though you only actually bought one jacket. That's a single joined query across two collections: technically one piece of paper, but bloated with combinations nobody asked for.

AsSplitQuery() is the store instead handing you three separate, short receipts — one for the jacket, one for the sizes you tried, one for the colors you considered — each listing only what's actually relevant to it, no combinations. Three pieces of paper instead of one, but a fraction of the total ink and paper. .Select() projection, by contrast, is asking the register to print only the total — you never even see the sizes or colors, because you never needed them in the first place.

Under the Hood

HOW EF CORE TRANSLATES AND CACHES QUERY SHAPES

1. Multiple Includes translate to LEFT JOINs against the same driving row

A single, non-split query with two Included collections generates SQL with two LEFT JOINs off the root table. Relational join semantics mean every matching row on one join is paired with every matching row on the other — that pairing is the cartesian product, and it's a property of how joins work, not an EF Core inefficiency to "fix" in the general sense.

▼

2. AsSplitQuery() replaces the JOINs with independent queries sharing a filter

Each split query re-applies the same root filter (e.g. the same WHERE or the same set of matched primary keys) independently, then EF Core's materialization layer — not the database — stitches each collection's rows onto the correct parent entity in memory using the primary/foreign key relationships it already knows from the model.

▼

3. EF Core already caches the translated query "shape" — you get this for free, on every query

As the LINQ-to-EF-Core lesson forward-referenced, EF Core doesn't re-walk and re-translate an identical LINQ expression tree into SQL from scratch on every single call. The first time a given query shape runs, EF Core compiles it into an internal representation and caches that compiled shape, keyed by the structure of the expression tree; subsequent calls with the same shape (even with different parameter values — a different orderId, say) reuse the cached compiled shape and skip straight to execution. This happens automatically, for ordinary LINQ queries, with zero code from you.

▼

4. That automatic caching still leaves a small, real cost on every call — which the next lesson addresses

Even with the shape cached, every call still pays a small lookup cost to find that cached shape, plus the normal cost of building the LINQ expression tree itself before the cache can even be consulted. For the overwhelming majority of queries this cost is genuinely negligible. For a narrow category of extremely hot, extremely simple, frequently-repeated queries, EF Core offers one further, explicit tool to shave off even that — EF.CompileQuery — which is exactly where the next lesson picks up.

Common Confusion

1. "AsSplitQuery() is just another way to fix N+1"

They solve different shapes of the same broader "too many round trips or too much data" family, but they aren't interchangeable. N+1 is the failure to eager-load at all — a loop silently issuing one query per row. AsSplitQuery() assumes you've already eager-loaded correctly with Include — its problem is specifically what happens when you eager-load more than one collection navigation in a single joined query. Fixing N+1 means adding Include in the first place; reaching for AsSplitQuery() only makes sense once you're already past that point.

2. "More round trips is always worse than one big query"

That's the intuition AsSplitQuery() deliberately overturns for this specific shape. One round trip sounds strictly better in the abstract, but when that "one" round trip is carrying a cartesian-multiplied payload, three small, targeted round trips can genuinely transfer less total data and finish faster than the single bloated one. Round-trip count and total work done are two different costs — this lesson's whole point is that the second one can dominate.

Common Mistakes

Mistake 1 — Including several collections together and never noticing the row-count explosion

Chaining three or four Include calls on independent collections in one query because it "reads cleanly," without ever looking at how many rows the database actually returns for a busy parent entity. Whenever a query includes two or more collection navigations together, default to AsSplitQuery() — and when in doubt, check the actual row count with query logging (from the LINQ-to-EF-Core lesson's diagnostic tools) before and after.

Mistake 2 — Reaching for AsSplitQuery() on a single-collection Include

Adding .AsSplitQuery() to a query with only one Included collection — there's no second collection for it to multiply against, so there's no cartesian problem, and splitting only adds an unnecessary extra round trip for nothing. Reserve AsSplitQuery() for queries including two or more collection navigations together — a single Include is already fine as one query.

Mistake 3 — Using Include when a projection would do

Include-ing a whole related entity graph just to read two of its fields on a read-only display screen, paying for tracked entities, full column sets, and change-tracker snapshots you'll never use. If you're not going to modify and save the related data, project with .Select() instead — it's narrower than any Include shape, single or split, because it never materializes the related entity as a tracked object at all.

When Should I Use It?

Situation	Reach for
A loop reading a navigation property per row, no eager loading at all	Fix the N+1 first — Include(...) or a projection (see 205)
One collection navigation eager-loaded	A plain Include — no split needed
Two or more collection navigations eager-loaded together	.AsSplitQuery()
Only a handful of fields ever read, related data never modified	A .Select() projection — skip Include entirely
A specific, simple, parameterized query called extremely often on a measured hot path	The narrower tool covered next: explicit compiled queries

Rule of thumb: One collection, one query. Two or more collections together, split them. Only a few fields, project them. Reach for each tool because you have that specific shape of query, not as a blanket default on every query in the codebase.

Mental Model

N+1 = forgetting to eager-load at all.
Cartesian explosion = eager-loading correctly, but joining too many collections into one query.
AsSplitQuery() = one query per collection instead of one join across all of them.
.Select() projection = fetch only the fields you actually read, skip materializing whole entities.

Remember: EF Core already caches translated query shapes automatically — compiled queries, next, are a narrower tool on top of that, not a replacement for it.

Key Takeaway

N+1 avoidance (205) and query optimization are related but distinct — this lesson assumes N+1 is already solved via Include or projection.
Including multiple collection navigations in one query produces a JOIN whose result set is cartesian-product-shaped — it multiplies, not adds.
AsSplitQuery() issues one query per included collection instead, avoiding the multiplication, at the cost of no longer being one atomic round trip.
.Select() projection is a genuine performance technique — fetch only the columns your code reads, and skip tracked-entity materialization for related data you never modify.
EF Core already caches translated query shapes automatically — the next lesson's EF.CompileQuery is a narrower, explicit optimization on top of that, not a fix for a gap that's otherwise unaddressed.

Check Your Understanding

You've seen why joining multiple collections multiplies rows, and the tools that fix it. Let's confirm it clicked.

1. A query does context.Posts.Include(p => p.Comments).Include(p => p.Tags).ToListAsync() as one single (non-split) query. A post has 40 comments and 6 tags. Roughly how many rows does the database return for that one post?

A) 46 rows — 40 comments plus 6 tags
B) 240 rows — 40 × 6, the cartesian product of the two joined collections
C) 1 row — EF Core automatically deduplicates before returning results
D) 2 rows — one per Include call

Show answer

Correct: B

Why B is correct: Joining a parent to two independent one-to-many collections in one SQL query produces a cartesian product between them — every comment row is paired with every tag row for that post, giving 40 × 6 = 240 rows, all repeating the post's own columns.

Why A is incorrect: That's what you'd get if the two collections were added rather than joined together — which is exactly the mistaken intuition this lesson corrects.

Why C is incorrect: Deduplication happens in EF Core's in-memory materialization when reconstructing the object graph from the raw rows — but the database still has to generate and transfer all 240 raw rows first.

Why D is incorrect: The number of Include calls doesn't map directly to row count — it's the product of each included collection's size that determines the row count in a single joined query.

Reinforcement: A join across multiple one-to-many collections multiplies, not adds — this is exactly the shape AsSplitQuery() exists to avoid.

2. What trade-off does AsSplitQuery() introduce in exchange for avoiding the cartesian-product row explosion?

A) It disables change tracking for all entities returned by the query
B) The query is no longer executed as one atomic round trip — it becomes several separate queries that could, in theory, see data change between them
C) It silently converts the query to no-tracking mode
D) It requires the entity to have a RowVersion concurrency token

Show answer

Correct: B

Why B is correct: Splitting one joined query into several separate queries means they no longer execute as a single atomic unit — a row could theoretically be inserted, updated, or deleted between the separate queries. For most read scenarios this is a non-issue; it's the real, honest cost of avoiding the multiplication.

Why A is incorrect: AsSplitQuery() has nothing to do with tracking behavior — tracked queries stay tracked, no-tracking queries stay no-tracking, independent of split vs. single.

Why C is incorrect: Same reasoning as A — splitting and tracking are unrelated, orthogonal query options.

Why D is incorrect: AsSplitQuery() has no relationship to concurrency tokens — that's an unrelated EF Core feature covered in a later lesson.

Reinforcement: Trading one atomic round trip for several right-sized ones is the honest cost of AsSplitQuery() — know the trade-off, not just the benefit.

3. A query only ever reads a customer's name and an order's total on a list screen — it never modifies either. What is the best query shape?

A) Include(o => o.Customer).AsSplitQuery()
B) A .Select() projection pulling only CustomerName and Total, with no Include at all
C) Include(o => o.Customer) with no split, since only one navigation is involved
D) Loading all orders, then all customers separately, and joining them in C# after both come back

Show answer

Correct: B

Why B is correct: Since the related data is only ever read, never modified, a projection is narrower than any form of Include — it fetches only the two fields actually needed and never materializes a tracked Customer entity at all.

Why A is incorrect: Splitting only matters when multiple collection navigations are joined together — here there's only a single reference navigation, and more importantly, a full Include is unnecessary when only one field off the related entity is ever read.

Why C is incorrect: While technically correct that a single navigation doesn't need splitting, this still over-fetches — it pulls every column of both Order and Customer when only two specific fields are needed.

Why D is incorrect: Loading two full sets separately and joining them in C# is strictly worse than either option — more data transferred, more memory used, and manual join logic that the database already does far better.

Reinforcement: When you only read a few fields and never save the related data back, projection beats every form of Include, split or otherwise.

4. What does EF Core's automatic internal query-shape caching already do for you, without any explicit compiled-query code?

A) It caches the actual query results, so a second call with a different parameter value returns stale data from the first call
B) It avoids re-translating an identical LINQ expression tree shape into SQL from scratch on every call, reusing the cached compiled shape across calls with different parameter values
C) It eliminates the need for parameterized SQL entirely
D) It automatically converts every query to use AsSplitQuery()

Show answer

Correct: B

Why B is correct: EF Core automatically caches the translated shape of a query, keyed by the structure of its expression tree, so subsequent calls with the same shape — even with different parameter values — skip re-translation and reuse the cached compiled shape. This happens for ordinary LINQ queries with zero opt-in code.

Why A is incorrect: This caches the translated query shape, not the returned data — every call still executes against the database and gets current results; nothing about it risks stale data.

Why C is incorrect: Generated SQL is still parameterized regardless of this caching — the two are related (parameterization is part of why the same shape can be reused across different values) but shape caching doesn't replace parameterization.

Why D is incorrect: Query-shape caching and split-query behavior are unrelated features — caching happens automatically for every query; split-query behavior must be explicitly opted into.

Reinforcement: This automatic caching is exactly what the next lesson builds on — EF.CompileQuery is a further, narrower, explicitly opted-into optimization on top of what already happens for free.

You now know how to reshape a query that's technically correct into one that's actually fast. Next up: the narrow, explicit tool that sits on top of EF Core's automatic caching for the hottest of hot paths — compiled queries.

dotnetmadeeasy.com — Learn C# and .NET, the right way.