199 - Deferred Execution · dotnetmadeeasy.com

"Deferred" means two genuinely different things depending on which world your query lives in — and mixing them up is where the expensive bugs live.

Intermediate lesson 117 taught you deferred execution as one idea: writing a LINQ query doesn't run it. That was true, complete, and correctly scoped for where you were at the time — entirely inside LINQ to Objects. Since then you've gone deep into two separate mechanisms that both happen to produce "deferred" behavior for entirely different reasons: the yield return state machine (lesson 196) and the IQueryable<T> expression tree (lesson 197). This lesson's job is to stop treating "deferred execution" as one fact and start treating it as two related but mechanically distinct facts — because the gotcha it causes in each world has wildly different real-world stakes.

You'll see, side by side, why a LINQ-to-Objects query is deferred (a paused state machine, waiting for the next MoveNext() call) and why an EF Core IQueryable<T> query is deferred (an unexecuted expression tree, waiting for Provider.Execute) — and then the same shape of gotcha in both worlds: re-enumerating an unmaterialized query silently redoes the whole computation, which is a minor inefficiency in one world and a genuine, expensive, repeated round trip to a real database in the other.

What Is It?

The Simple Explanation

"Deferred execution" is the umbrella term for "writing a query doesn't run it — something else, later, does." What that "something else, later" actually consists of is where the two worlds diverge completely, and this lesson's entire purpose is making that divergence concrete instead of leaving it as a single blurred idea.

The Technical Definition — Two Mechanisms, Not One

LINQ to Objects — deferred because of a paused state machine

Calling Where/Select constructs a yield return-generated object (lesson 196) whose MoveNext() hasn't been called yet.
"Not executed" means: the state field sits at its initial value, and none of your predicate/selector delegate has been invoked.
"Execution" means: repeated MoveNext() calls, each resuming the state machine from wherever it last paused.

LINQ to Queryables — deferred because of an unexecuted expression tree

Calling Where/Select (resolved to Queryable's versions, lesson 197) builds a bigger Expression tree and wraps it in a new IQueryable<T>.
"Not executed" means: Provider.Execute has never been called on the tree — no SQL has even been generated yet.
"Execution" means: the provider walks the whole tree once, translates it, and runs the result against the real data source.

Both are real, both are "LINQ doesn't run when you write it" — but one is about a paused CLR-level object waiting for its next resume signal, and the other is about a data structure waiting to be handed, whole, to a translator. They happen to produce the same-looking symptom (nothing runs until enumeration) through mechanically unrelated means.

Why Does It Exist?

Why LINQ to Objects Defers

As lesson 196 established, deferring is simply what yield return-based iterators do by nature — a state machine's whole point is pausing at each yield return until asked to resume. LINQ to Objects didn't choose deferred execution as a separate design decision layered on top of iterators; it inherited it automatically, for free, by being built out of iterators in the first place.

Why LINQ to Queryables Defers

As lesson 197 established, this is a genuinely deliberate design choice, for a different reason entirely: an expression tree can only be translated correctly once it's complete. If Where executed the instant it was called, there would be no opportunity to also see the OrderBy and Select that come after it in the same chain, and no way to combine all three into one efficient SQL statement. Deferring until the whole tree is built — and only then handing it, complete, to the provider — is what makes single-round-trip translation possible at all.

The One-Sentence Distinction

LINQ to Objects defers because pausing is what an iterator does. LINQ to Queryables defers because translation needs the whole picture before it can start. Same word, same surface symptom, two unrelated underlying reasons — and, as the rest of this lesson shows, two very different costs when you get the "re-enumerate without meaning to" gotcha wrong.

Big Picture

THE SAME GOTCHA, TWO VERY DIFFERENT BILLS

Re-enumerate a List<T>.Where(...) result twice

The predicate delegate runs twice, in-process.
Cost: a second CPU pass over a list already sitting in memory.
Usually negligible.

Re-enumerate an EF Core IQueryable<T> result twice

The ENTIRE SQL query runs against the database a SECOND TIME.
Cost: a second network round trip, a second execution plan, a second full table scan or index seek.
A real, expensive production bug.

Identical-looking code — query.Count(); foreach (var x in query) ... — with a cost difference that can span orders of magnitude.

How It Works

World 1 — LINQ to Objects, Traced Through the State Machine

1. products.Where(...) — CONSTRUCTS a state machine, runs NOTHING

Exactly as lesson 196 traced: a hidden class instance is allocated, fields for source and predicate are set, the state field sits at its initial value.

▼

2. FIRST enumeration — MoveNext() called repeatedly, predicate runs once per source item

Real CPU work happens here, in-process, against whatever products currently contains.

▼

3. SECOND enumeration on the SAME query variable — a BRAND NEW state machine, from GetEnumerator()

As lesson 196's Under the Hood covered, a fresh foreach calls GetEnumerator() again, producing an independent (or freshly-copied) state machine starting from state zero — the predicate runs again, once per item, from scratch.

World 2 — LINQ to Queryables, Traced Through the Expression Tree

1. context.Products.Where(...) — WRAPS a bigger Expression tree, runs NOTHING

Exactly as lesson 197 traced: Queryable.Where calls Provider.CreateQuery, returning a new IQueryable<T> whose .Expression is bigger — no SQL generated, no connection opened.

▼

2. FIRST enumeration — Provider.Execute walks the tree, generates SQL, sends it, materializes rows

A real network round trip happens here — one connection, one command, one result set read back.

▼

3. SECOND enumeration on the SAME query variable — Provider.Execute runs AGAIN, on the SAME tree

The IQueryable<T> variable still just holds an expression tree — it has no memory of having been executed before. A second enumeration triggers a second, complete, independent translate-and-execute cycle: a second connection round trip, a second SQL execution against the live database.

Simple Example

The identical shape of mistake, written twice — once against each world, so the contrast is impossible to miss:

// ─── WORLD 1 — LINQ to Objects ───
List<Product> products = LoadProductsIntoMemory(); // already in memory

var inStock = products.Where(p => p.Stock > 0); // deferred — a paused state machine

int count = inStock.Count();          // ENUMERATION #1 — predicate runs once per item, in-process
foreach (var p in inStock) { ... }    // ENUMERATION #2 — predicate runs AGAIN, in-process

// Cost of the second enumeration: one more CPU pass over an in-memory list.
// Wasteful, but bounded, cheap, and entirely local.

// ─── WORLD 2 — LINQ to Queryables (EF Core) ───
IQueryable<Product> inStockQuery = context.Products.Where(p => p.Stock > 0); // deferred — an unexecuted tree

int count = await inStockQuery.CountAsync();       // EXECUTION #1 — SELECT COUNT(*) ... sent to the DATABASE
var list = await inStockQuery.ToListAsync();        // EXECUTION #2 — SELECT * FROM ... sent to the DATABASE AGAIN

// Cost of the second execution: a SECOND network round trip, a SECOND query plan,
// a SECOND full read against the live database — for data that, in this example,
// didn't even need to be fetched twice.

Code → Meaning → Result: Both snippets are the same shape of bug — a deferred query variable enumerated more than once without realizing it — and both are, mechanically, "correct" C# that compiles and runs without error. The difference is entirely about what "enumerate" costs in each world: a re-run in-memory loop versus a second live query hitting a production database. The fix is identical in both worlds too, and it's the one you already learned in Intermediate lesson 118 (Immediate Execution): materialize once, with ToList()/ToListAsync(), and reuse the materialized result for everything after.

//  FIX — identical pattern in both worlds: materialize ONCE, reuse the result
var inStockList = products.Where(p => p.Stock > 0).ToList();          // World 1 — one CPU pass
int count = inStockList.Count;                                          // reads a stored List<T>.Count — O(1)
foreach (var p in inStockList) { ... }                                   // reuses the SAME materialized list

var inStockList2 = await context.Products.Where(p => p.Stock > 0).ToListAsync(); // World 2 — ONE round trip
int count2 = inStockList2.Count;                                        // reads the already-fetched List<T>.Count
foreach (var p in inStockList2) { ... }                                  // reuses the SAME materialized list — no second query

Real-World Example

An ASP.NET Core endpoint that returns a paginated list alongside a total count — a genuinely common shape, and a genuinely common place for this exact bug to hide, since it's not obvious at a glance that two separate database queries are about to fire:

//  BEFORE — the query variable is enumerated TWICE, silently, against the database
[HttpGet]
public async Task<IActionResult> GetProducts(int page, int pageSize)
{
    IQueryable<Product> query = context.Products.Where(p => p.IsActive);

    int totalCount = await query.CountAsync();              // ROUND TRIP #1 — SELECT COUNT(*) ...
    var items = await query
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();                                       // ROUND TRIP #2 — SELECT * ... with paging

    return Ok(new { totalCount, items });
}

This specific example is actually correct — it's not a bug, because CountAsync and the paginated ToListAsync genuinely need to run two different SQL statements (a COUNT and a paged SELECT aren't the same query). It's included deliberately to show the important nuance: two round trips aren't automatically wrong — what's wrong is not knowing that two round trips are happening. The real bug shows up when a developer, trying to "simplify," reuses the same fully-built query for both without realizing each await is its own independent execution:

//  THE ACTUAL BUG — same query re-enumerated for no reason, THREE times
public async Task<IActionResult> GetProductSummary()
{
    var expensiveQuery = context.Products
        .Where(p => p.IsActive)
        .Where(p => ExpensiveComputedFilter(p)); // imagine a costly translated WHERE clause

    if (await expensiveQuery.AnyAsync())          // ROUND TRIP #1
    {
        int total = await expensiveQuery.CountAsync();       // ROUND TRIP #2 — SAME filter, re-run
        var top10 = await expensiveQuery.Take(10).ToListAsync(); // ROUND TRIP #3 — SAME filter, re-run AGAIN
        return Ok(new { total, top10 });
    }
    return Ok(new { total = 0, top10 = Array.Empty<Product>() });
}

//  FIX — materialize the FILTERED set once, or restructure to genuinely need only what's necessary
public async Task<IActionResult> GetProductSummary()
{
    var matching = await context.Products
        .Where(p => p.IsActive)
        .Where(p => ExpensiveComputedFilter(p))
        .ToListAsync(); // ONE round trip — the expensive filter runs against the database exactly once

    return Ok(new { total = matching.Count, top10 = matching.Take(10).ToList() });
}

Every one of the three round trips in the "before" version re-sends the identical, potentially expensive WHERE clause to the database — three full re-evaluations of ExpensiveComputedFilter's translated SQL, against live data, for a single incoming HTTP request. This is precisely the class of bug that's invisible by reading the code casually — it looks like three short, simple lines — and precisely why the mental model from this lesson (an IQueryable<T> variable has no memory of having run before) matters in practice, not just in theory.

Analogy

Re-Reading Your Own Notes vs Re-Placing the Same Phone Call

Re-enumerating a LINQ-to-Objects query is like re-reading a page of notes you already have sitting in front of you — a little redundant, mildly wasteful of your own time, but the information was already right there the whole time; nothing outside the room had to be involved again.

Re-enumerating an EF Core IQueryable<T> is like re-dialing the same phone number and re-asking the same question to someone on the other end of a long-distance call — every single time, even though you already got the answer a moment ago. The information wasn't sitting in front of you; it lived somewhere else, and getting it again means paying the full cost of the call again: the dial time, the connection, the other person's time answering the exact same question they just answered.

Under the Hood

WHY NEITHER WORLD CACHES A PREVIOUS RESULT FOR YOU

1. AN IEnumerable<T> QUERY VARIABLE HOLDS A STATE-MACHINE FACTORY, NOT A RESULT

As lesson 196 established, GetEnumerator() is designed to produce a fresh, independent state machine on every call — that's precisely what lets two simultaneous loops over the same query never interfere with each other. The tradeoff is exactly this lesson's gotcha: there is no shared memory of "we already ran this," by design.

▼

2. AN IQueryable<T> VARIABLE HOLDS AN EXPRESSION TREE, NOT A RESULT — AND TREES ARE IMMUTABLE

As lesson 197 established, an IQueryable<T> is just an Expression plus a Provider reference. Calling Provider.Execute doesn't mutate the tree or record "this has run" anywhere on the IQueryable<T> object itself — the tree is exactly as re-executable after being run once as it was before.

▼

3. EF Core'S CHANGE TRACKER DOES NOT PREVENT THIS EITHER

It's tempting to assume EF Core's tracking of loaded entities would make a second identical query "free" — it doesn't. Change tracking governs what happens to already-materialized entity instances (avoiding duplicate tracked copies of the same row); it has no bearing on whether a query gets re-sent to the database. Both round trips in the earlier example genuinely hit the database, regardless of tracking configuration.

Common Confusion

1. "Deferred execution is one concept" — it's one word covering two mechanisms

This entire lesson exists because that conflation is genuinely easy to fall into once you've learned both mechanisms separately — Intermediate 117 taught the LINQ-to-Objects version thoroughly, and it's natural to assume the same explanation ("builds an iterator, runs on MoveNext()") applies unchanged to IQueryable<T>. It doesn't; IQueryable<T>'s deferral has nothing to do with yield return or state machines at all — it's about an expression tree waiting to be handed to a provider.

2. "Materializing early always defeats the purpose of IQueryable<T>" — only if you materialize before you've finished building the query

Intermediate lesson 110's Common Mistakes warned against calling ToList() too early, before all filtering has been chained on — that remains correct and important. This lesson's advice is different and complementary: once the full query is built and about to be reused more than once, materializing with ToList()/ToListAsync() is exactly right. The rule isn't "never materialize early" — it's "materialize exactly once, at the point where the fully-built query is about to be consumed more than once."

Common Mistakes

Mistake 1 — Passing a still-`IQueryable<T>` value through several methods, each enumerating it independently

A service method that builds a filtered IQueryable<T> and returns it, letting three different callers each independently call .ToListAsync() or .CountAsync() on it without realizing every call is its own live database round trip. Either materialize inside the method that builds the query (return a List<T>, not an IQueryable<T>, once the caller only needs the data), or clearly document that the returned IQueryable<T> is meant to be enumerated exactly once by its caller.

Mistake 2 — Assuming the LINQ-to-Objects "it's just a minor inefficiency" mindset transfers to EF Core

Treating a repeated IQueryable<T> enumeration the same way you'd treat a repeated in-memory Where enumeration — "a bit wasteful, not a big deal." Recognize that the cost model is categorically different: an in-memory re-pass costs microseconds of CPU; a repeated database round trip costs network latency, connection/command overhead, and real load on a shared production database — often milliseconds to seconds, multiplied across every request your API serves.

When Should I Use It?

Keep a query deferred (don't materialize yet) when

You're still chaining additional filtering, sorting, or paging operators onto it — materializing early forfeits translation for anything chained after (Intermediate lesson 110's warning).
The query genuinely will only be enumerated exactly once.

Materialize immediately (ToList/ToListAsync) when

The fully-built query is about to be enumerated more than once — always, in both worlds, but with dramatically higher stakes in the IQueryable<T> world.
A method is about to return a query result to a caller who has no visibility into whether re-enumerating it is cheap (in-memory) or expensive (a live database round trip) — prefer returning an already-materialized collection in that case.

Rule of thumb: Before enumerating a query variable a second time, ask what kind of deferred it is. If it's IEnumerable<T>, a second enumeration is a minor, local inefficiency. If it's IQueryable<T>, a second enumeration is a second trip to a real, shared, possibly-remote database — treat it with the seriousness of any other unnecessary network call, because that's exactly what it is.

Mental Model

LINQ to Objects is deferred = "a state machine, paused, waiting for the next MoveNext()"
LINQ to Queryables is deferred = "an expression tree, unexecuted, waiting for Provider.Execute"

Remember:
· Same word, two mechanisms — a paused iterator vs. an unexecuted tree.
· Both re-run in full on every enumeration — neither remembers a previous run.
· Re-enumerating an in-memory query costs a redundant CPU pass. Re-enumerating an EF Core query costs a redundant, real trip to the database.
· The fix is identical in both worlds: materialize once with ToList()/ToListAsync(), at the point the query is about to be used more than once.

Key Takeaway

In LINQ to Objects, deferred execution means a yield return state machine hasn't had MoveNext() called yet — nothing runs until it does.
In LINQ to Queryables, deferred execution means an expression tree hasn't been handed to Provider.Execute yet — no translation, no SQL, no round trip until it is.
Both mechanisms independently produce the same-looking gotcha: re-enumerating a query variable re-runs the entire computation from scratch, every time.
The cost of that gotcha is categorically different between the two worlds — a redundant in-memory CPU pass versus a redundant, real network round trip against a live database.
The fix is the same in both worlds: materialize exactly once, with ToList()/ToListAsync(), once the fully-built query is about to be consumed more than once.

Check Your Understanding

You've traced deferred execution through both mechanisms and seen the same gotcha carry very different real-world costs. Let's check your understanding.

1. Why is a LINQ-to-Objects query "deferred," mechanically?

A) Because the .NET runtime schedules it on a background thread
B) Because calling Where/Select constructs a yield return-generated state machine object whose MoveNext() hasn't been called yet — nothing in the method body runs until it is
C) Because LINQ to Objects always waits five seconds before executing, by design
D) Because the compiler inserts an explicit Task.Delay call

Show answer

Correct: B

Why B is correct: This is precisely lesson 196's mechanism, restated as the "why" behind LINQ-to-Objects deferral — the state machine simply hasn't been asked to advance yet.

Why A is incorrect: No background threading or async scheduling is involved in ordinary LINQ-to-Objects deferral — it's entirely a synchronous, single-threaded state-machine mechanism unless you separately introduce async code yourself.

Why C is incorrect: There's no artificial delay of any kind — execution begins the instant something calls MoveNext(), which can happen immediately.

Why D is incorrect: No such call is inserted — deferral is purely a consequence of how iterator methods are structured, not any explicit timing mechanism.

Reinforcement: LINQ-to-Objects deferral is a direct, mechanical consequence of how yield return compiles — nothing more exotic than that.

2. Why is an EF Core IQueryable<T> query "deferred," mechanically — and is it the SAME reason as question 1?

A) Yes — it's the identical yield return state-machine mechanism, just running against a database instead of memory
B) No — it's deferred because it holds an unexecuted Expression tree, which must be complete before the provider can translate it; nothing runs until Provider.Execute is called, a mechanism unrelated to state machines or MoveNext()
C) No — it's deferred because EF Core deliberately throttles requests to avoid overloading the database
D) Yes — both mechanisms are identical because IQueryable<T> extends IEnumerable<T>

Show answer

Correct: B

Why B is correct: This is the central distinction the entire lesson is built to make explicit — IQueryable<T>'s deferral comes from an unexecuted expression tree needing to be complete before translation, a mechanically separate reason from LINQ-to-Objects' paused state machine.

Why A is incorrect: Queryable.Where never uses yield return — it builds tree nodes via Expression.Call, as lesson 197 showed directly; there is no state machine involved at all in this path.

Why C is incorrect: There's no throttling mechanism involved — deferral here is purely about the tree needing to be fully built before it can be translated, not about rate-limiting.

Why D is incorrect: IQueryable<T> extending IEnumerable<T> is an interface inheritance relationship, not a claim that both use identical underlying mechanisms — Queryable's own operator implementations are entirely separate from Enumerable's.

Reinforcement: Same word, "deferred," but two unrelated mechanisms — a paused iterator versus an unexecuted, translatable tree.

3. An EF Core IQueryable<Product> query variable is enumerated with await query.ToListAsync(), and then later, the exact same variable is enumerated again with await query.CountAsync(). What happens?

A) EF Core recognizes the query already ran and returns a cached count instantly, with no second database round trip
B) The second call throws an exception, since a query can only be executed once
C) A completely separate SQL statement is generated and sent to the database a second time — the query variable has no memory of the first execution
D) Only the first call actually touches the database; the second is a no-op

Show answer

Correct: C

Why C is correct: As Under the Hood explained, the IQueryable<T> variable is just an immutable expression tree plus a provider reference — executing it once doesn't mark or mutate the tree in any way, so a second enumeration is a fully independent second translate-and-execute cycle, complete with its own database round trip.

Why A is incorrect: There is no automatic result caching on an IQueryable<T> variable — this is exactly the false assumption Under the Hood point 3 warned against, including the specific point that EF Core's change tracker doesn't provide this either.

Why B is incorrect: Re-enumerating the same IQueryable<T> variable multiple times is entirely valid, exception-free C# — it's just potentially wasteful, not an error.

Why D is incorrect: Both calls genuinely execute against the database — nothing about the second call is skipped or treated as redundant automatically.

Reinforcement: An IQueryable<T> variable has no built-in memory of having run before — every enumeration is a fresh, full execution.

4. Why is re-enumerating an unmaterialized EF Core IQueryable<T> query considered a more serious real-world problem than re-enumerating an unmaterialized in-memory IEnumerable<T> query?

A) There's no meaningful difference — both cost roughly the same amount of time in practice
B) The IEnumerable<T> case only re-runs an in-process CPU pass over data already in memory; the IQueryable<T> case re-runs an entire SQL query against a real, often remote database — a network round trip, connection/command overhead, and real database load, repeated unnecessarily
C) EF Core queries are always slower than LINQ to Objects queries, regardless of re-enumeration
D) IQueryable<T> queries corrupt data when run more than once

Show answer

Correct: B

Why B is correct: This is the Big Picture comparison made explicit — the same shape of mistake carries a fundamentally different cost profile depending on where the "re-run" actually happens: local memory versus a real network call to a shared database.

Why A is incorrect: The cost difference can span orders of magnitude — microseconds of local CPU work versus milliseconds-to-seconds of network and database overhead, especially under load.

Why C is incorrect: This lesson makes no such blanket claim — the concern here is specifically about redundant re-execution, not a general comparison of the two worlds' baseline speed.

Why D is incorrect: Nothing about re-running a read query corrupts data — the concern is purely about wasted performance and unnecessary load, not correctness or data integrity.

Reinforcement: The mechanism causing the gotcha is similar in shape across both worlds; the real-world stakes of paying that cost are not.

5. A method builds an IQueryable<Product> with several chained filters and returns it directly to three different callers, each of whom independently calls .ToListAsync() on it. What is the correct fix, and why?

A) Nothing needs to change — IQueryable<T> results are automatically cached across callers
B) Call .ToListAsync() once inside the method itself (or otherwise ensure the query is materialized before being shared), so all three callers reuse one already-fetched result instead of each triggering their own independent database round trip
C) Mark the method async so the three calls happen in parallel instead of sequentially
D) Add a lock statement around each caller's .ToListAsync() call

Show answer

Correct: B

Why B is correct: This is exactly Mistake 1 and the "When Should I Use It?" guidance — since each caller enumerating the shared IQueryable<T> independently triggers its own full database round trip, materializing once (before the three callers ever see the result) collapses that down to a single query, reused everywhere it's needed.

Why A is incorrect: As established throughout this lesson, there is no automatic caching of query results across callers — each independent enumeration is a fully independent execution.

Why C is incorrect: Running the three redundant queries in parallel instead of sequentially still means three separate, wasteful database round trips — it doesn't address the actual root cause at all.

Why D is incorrect: Locking doesn't reduce the number of database round trips — it would only serialize three still-independent, still-redundant executions.

Reinforcement: When a query result will genuinely be needed by more than one consumer, materialize it once and share the materialized result — never share the still-deferred query itself.

You now understand deferred execution as two distinct, traceable mechanisms rather than one vague idea. Next: the concrete performance facts — allocations, boxing, and how to actually measure any of this — that close out this module.

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

What Is It?

The Simple Explanation

The Technical Definition — Two Mechanisms, Not One

LINQ to Objects — deferred because of a paused state machine

LINQ to Queryables — deferred because of an unexecuted expression tree

Why Does It Exist?

Why LINQ to Objects Defers

Why LINQ to Queryables Defers

Big Picture

How It Works

World 1 — LINQ to Objects, Traced Through the State Machine

World 2 — LINQ to Queryables, Traced Through the Expression Tree

Simple Example

Real-World Example

Analogy

Under the Hood

Common Confusion

1. "Deferred execution is one concept" — it's one word covering two mechanisms

2. "Materializing early always defeats the purpose of IQueryable<T>" — only if you materialize before you've finished building the query

Common Mistakes

Mistake 1 — Passing a still-IQueryable<T> value through several methods, each enumerating it independently

Mistake 2 — Assuming the LINQ-to-Objects "it's just a minor inefficiency" mindset transfers to EF Core

When Should I Use It?

Keep a query deferred (don't materialize yet) when

Materialize immediately (ToList/ToListAsync) when

Mental Model

Key Takeaway

Check Your Understanding

Mistake 1 — Passing a still-`IQueryable<T>` value through several methods, each enumerating it independently