LINQ Deferred Execution: When Does a Query Run? IEnumerable and IQueryable

Version note: The code in this post uses C# 10 and the .NET 6 APIs; it was built and run with the .NET 10 SDK.

Guess First

We define a query, then add a book to the list and raise the price limit, and only after that do we ask for the result. What does Count() print, and which books does the foreach list? Does the query run against the state at the moment it was defined, or does it see the changes we made afterwards?

var books = new List<Book>
{
    new("978-0132350884", "Clean Code", 37.90m, 12),
    new("978-0135957059", "The Pragmatic Programmer", 42.50m, 0),
    new("978-0321125217", "Domain-Driven Design", 54.99m, 3),
    new("978-1617294532", "C# in Depth", 44.99m, 5),
};

decimal maxPrice = 40m;
IEnumerable<Book> cheap = books.Where(b => b.Price < maxPrice);

books.Add(new("978-0201633610", "Design Patterns", 29.99m, 7));
maxPrice = 50m;

Console.WriteLine(cheap.Count());
foreach (Book book in cheap)
    Console.WriteLine(book.Title);

record Book(string Isbn, string Title, decimal Price, int Stock);

A Query Is a Recipe, Not a Result

Here is the output:

4
Clean Code
The Pragmatic Programmer
C# in Depth
Design Patterns

When the query was defined the list had three books and the limit was 40; had it run at that moment, only Clean Code would come back. Yet the result has four books: Design Patterns, added later, is there, and so are The Pragmatic Programmer and C# in Depth, which now qualify because the limit went up to 50. So the books.Where(...) line filtered no books at all. All it did was create an object that says "walk books and give me the ones cheaper than maxPrice". The filtering happened when Count() walked that object, with the list and the maxPrice as they were at that moment.

This is called deferred execution. A query is like a recipe: writing the recipe is not cooking, and the ingredients come out of the fridge not when you write the recipe but when you start cooking. If you put something new in the fridge in between, it goes into the pot too.

The effect of maxPrice deserves extra attention. The lambda captures the variable itself, not its value; that is a closure. When the query runs, the lambda reads maxPrice right then and sees 50. Every captured variable you change after defining the query silently changes the result.

Element by Element

So when the moment to run arrives, how does the work proceed? Does it filter the whole list first and then transform everything? Let's put a log line inside Where and Select:

IEnumerable<string> query = books
    .Where(b =>
    {
        Console.WriteLine($"  Where:  {b.Title}");
        return b.Stock > 0;
    })
    .Select(b =>
    {
        Console.WriteLine($"  Select: {b.Title}");
        return b.Isbn;
    });

Console.WriteLine("Query defined");

foreach (string isbn in query)
    Console.WriteLine($"foreach got {isbn}");

Query defined
  Where:  Clean Code
  Select: Clean Code
foreach got 978-0132350884
  Where:  The Pragmatic Programmer
  Where:  Domain-Driven Design
  Select: Domain-Driven Design
foreach got 978-0321125217
  Where:  C# in Depth
  Select: C# in Depth
foreach got 978-1617294532

"Query defined" comes first: neither lambda ran during the definition. The interesting part is what follows. When the foreach asks for the first element, Select asks its own source, Where, for an element; Where starts pulling elements from the list and hands up the first one that satisfies the condition. Clean Code travels through all three layers this way and reaches the foreach, and only then is the second book looked at. The Pragmatic Programmer is dropped in Where and never reaches Select.

This is a pull model. Not a factory conveyor belt but a made-to-order kitchen: each plate is prepared when the consumer at the very end says "one more". No intermediate list is built; however many operators you add, each element goes through the chain on its own. In the previous post we said naming intermediate steps costs nothing; this is why.

Operators That Run Immediately

Not every LINQ operator is deferred. Roughly: the ones that return a sequence (Where, Select, OrderBy, Take…) are deferred. The ones that return a single value (Count, Sum, Max, First, Any…) and the ones that fill a new collection with the result (ToList, ToArray, ToDictionary, ToHashSet) run immediately. To hand back a value or a filled collection, they have to walk the source right then.

IEnumerable<Book> inStock = books.Where(b => b.Stock > 0);
List<Book> snapshot = books.Where(b => b.Stock > 0).ToList();
int countThen = books.Count(b => b.Stock > 0);

books.Add(new("978-0201633610", "Design Patterns", 29.99m, 7));

Console.WriteLine($"deferred: {inStock.Count()}");
Console.WriteLine($"ToList:   {snapshot.Count}");
Console.WriteLine($"Count():  {countThen}");

deferred: 4
ToList:   3
Count():  3

All three variables were defined before the book was added. inStock is still a recipe, so when Count() was called it saw the new book too. snapshot is a separate list filled at the moment of ToList(); a photograph of that moment. countThen is a number computed at that moment as well. If you want to freeze a query's result at a given point, you materialize it with ToList() or ToArray().

Streaming and Buffering Operators

Deferred operators come in two kinds as well. Streaming operators like Where, Select and Take process each element as they get it and pass it up. Non-streaming operators like OrderBy, GroupBy and Reverse have to read the entire source before they can hand out the first element. You cannot know the cheapest book without looking at all of them:

bool Log(Book b)
{
    Console.WriteLine($"  checked {b.Title}");
    return b.Stock > 0;
}

Console.WriteLine("Where + Take(1):");
foreach (Book book in books.Where(Log).Take(1))
    Console.WriteLine($"-> {book.Title}");

Console.WriteLine("Where + OrderBy + Take(1):");
foreach (Book book in books.Where(Log).OrderBy(b => b.Price).Take(1))
    Console.WriteLine($"-> {book.Title}");

Where + Take(1):
  checked Clean Code
-> Clean Code
Where + OrderBy + Take(1):
  checked Clean Code
  checked The Pragmatic Programmer
  checked Domain-Driven Design
  checked C# in Depth
-> Clean Code

In the first query Take(1) stopped after taking one element; the list was checked only once. In the second query Take(1) still asks for a single element, but the OrderBy in between reads and sorts the entire source on the first MoveNext(). Buffering operators are deferred too, so they do nothing when the query is defined; but once iteration starts, they pull the whole source into memory. This distinction decides whether a query works at all on an infinite sequence or on a file with millions of lines.

Running the Same Query Twice

The recipe analogy has a price: every iteration applies the recipe from scratch. If the query is cheap, no problem, but if it contains an expensive calculation, a file read or a service call, things change:

int calls = 0;

IEnumerable<decimal> pricesWithTax = books.Select(b =>
{
    calls++;
    return b.Price * 1.2m;
});

decimal total = pricesWithTax.Sum();
decimal highest = pricesWithTax.Max();
int lines = pricesWithTax.Count();
Console.WriteLine($"lazy:         {calls} selector calls");

calls = 0;
List<decimal> materialized = pricesWithTax.ToList();
total = materialized.Sum();
highest = materialized.Max();
lines = materialized.Count;
Console.WriteLine($"materialized: {calls} selector calls");

lazy:         12 selector calls
materialized: 4 selector calls

On a four-book list, Sum, Max and Count ran the query from scratch three times and the selector was called 12 times. Notice that even Count() calls the selector: .NET does not skip it here; silently skipping a lambda that might have side effects would change the program's behavior. Once you materialize the result with ToList(), the selector runs four times and the next three operations work on the list. The rule is simple: if you will use a query's result more than once, materialize it once.

The second price of deferral is that it moves where errors show up. Bad data blows up not when the query is defined but when it is iterated:

string[] rawStocks = { "12", "7", "x", "3" };

IEnumerable<int> stocks = rawStocks.Select(int.Parse);
Console.WriteLine("Query defined, no exception yet");

try
{
    foreach (int stock in stocks)
        Console.WriteLine(stock);
}
catch (FormatException ex)
{
    Console.WriteLine($"FormatException: {ex.Message}");
}

Query defined, no exception yet
12
7
FormatException: The input string 'x' was not in a correct format.

The query was defined, the first two elements were printed successfully, and the exception came only while processing the third. In a real project the query might be built in a repository and iterated in a controller; the stack trace takes you not to the bad data but to the line where the iteration happens. There is no .NET 6 runtime on this machine, so the programs ran on the .NET 8 runtime through DOTNET_ROLL_FORWARD=Major; the wording of exception messages can differ between runtime versions.

IEnumerable and IQueryable

So far everything has been in memory and our own process ran the query. With a database provider like EF Core, the same Where call takes a very different path. To see the difference, let's first assign the same lambda to two different types:

using System.Linq.Expressions;

Func<Book, bool> asDelegate = b => b.Price < 40m;
Expression<Func<Book, bool>> asExpression = b => b.Price < 40m;

Console.WriteLine(asDelegate);
Console.WriteLine(asExpression);
Console.WriteLine(asExpression.Body.NodeType);

IQueryable<Book> source = books.AsQueryable();
IQueryable<Book> cheap = source.Where(b => b.Price < 40m);
Console.WriteLine(cheap.Expression);
Console.WriteLine(cheap.Count());

System.Func`2[Book,System.Boolean]
b => (b.Price < 40)
LessThan
System.Collections.Generic.List`1[Book].Where(b => (b.Price < 40))
1

Func<Book, bool> is a compiled delegate, ready to run. You cannot ask it what is written inside; printing it only shows the type name. Expression<Func<Book, bool>> is the same lambda as a data structure, an expression tree. Instead of making the code executable, the compiler builds a tree of objects that says "compare b.Price and 40 with LessThan", and that tree can be walked and interpreted.

LINQ's two families split along these two types. The operators in the Enumerable class work on IEnumerable<T> and take a Func: they run code in memory, element by element. The operators in the Queryable class work on IQueryable<T> and take an Expression<Func>: they run nothing and only grow the expression tree. You can see this in the cheap.Expression output above: the Where call has become part of the tree. When the query is iterated, the tree is handed to the provider. The in-memory provider we got from AsQueryable() compiled and ran the tree; EF Core translates the same tree into SQL and sends it to the database. The SQL we saw with ToQueryString() in the LINQ queries post of the EF Core series is the translation of that tree.

This distinction can turn into a real bug when you choose a parameter type:

IQueryable<Book> source = books.AsQueryable();

IEnumerable<Book> viaEnumerable = CheapEnumerable(source);
IQueryable<Book> viaQueryable = CheapQueryable(source);

Console.WriteLine(viaEnumerable is IQueryable<Book>);
Console.WriteLine(viaQueryable is IQueryable<Book>);

static IEnumerable<Book> CheapEnumerable(IEnumerable<Book> books) =>
    books.Where(b => b.Price < 40m);

static IQueryable<Book> CheapQueryable(IQueryable<Book> books) =>
    books.Where(b => b.Price < 40m);

False
True

The bodies of the two methods are identical letter for letter. But because CheapEnumerable's parameter is IEnumerable<Book>, the compiler picks Enumerable.Where and the result is no longer an IQueryable. Overload resolution follows the compile-time type, not the runtime type. With EF Core this means the filter never makes it into SQL: the whole table is pulled from the database and the filter is applied in memory. You won't notice with test data; you will notice in production with a million-row table. If you deliberately want to continue the query in memory, say so explicitly with AsEnumerable(). We saw this pitfall with real SQL output in the EF Core series.

Common Pitfalls

Thinking any lambda can become an expression tree. Expression trees represent expressions; a lambda with curly braces and statements in its body cannot be converted to one:

using System.Linq.Expressions;

Expression<Func<Book, bool>> cheap = b =>
{
    return b.Price < 40m;
};

record Book(string Isbn, string Title, decimal Price, int Stock);

Program.cs(3,38): error CS0834: A lambda expression with a statement body cannot be converted to an expression tree

Thinking every expression that compiles can be translated is a separate pitfall. If you call a C# method of your own inside Where, the code compiles, because an expression tree can represent that call too. But EF Core cannot translate that method into SQL, and the query throws when it runs. In lambdas written for IQueryable, stick to things the provider knows.

Calling ToList() too early. dbContext.Books.ToList().Where(...) first loads the whole table into memory and then filters. Materializing is the right tool, but its place is after filtering and projection.

Returning a deferred query over a resource whose lifetime is over. If you build a query over a DbContext or a file opened with using inside a method and return it without materializing, the resource is already closed by the time the caller starts iterating. Call ToList() before the resource's lifetime ends.

Changing a captured variable afterwards. Like maxPrice in the opening example, every variable you change after defining the query affects the result. If you want the query to run with the current value, copy the value into a local that you don't touch again, or materialize the result right away.

A Small Experiment

★☆☆ In the opening program, create cheap with books.Where(b => b.Price < maxPrice).ToList(). How does the output change, and why?

★★☆ In the logged Where/Select example, add .OrderBy(isbn => isbn) after Select. How does the order of the log lines change? Then move the OrderBy before Where and look again.

★★★ Write an iterator that returns IEnumerable<int> and produces order numbers forever. Apply Where(n => n % 7 == 0).Take(3) to it and watch the program finish. Then put OrderBy(n => n) in between and explain, with the two kinds of operators from this post, why it never finishes.

Now we know when and how a query runs. In the next post we put that knowledge to work on the two most common jobs: filtering with Where and sorting with OrderBy, ThenBy and Reverse, including sort stability and culture-sensitive string ordering.