sdlc.dev

C# and .NET path · The modern language · Topic 9 of 14

LINQ: deferred execution, IEnumerable vs IQueryable

Not started

LINQ is a set of extension methods (Where, Select, OrderBy, GroupBy, Join and many more) plus an optional query syntax, over any IEnumerable<T>. Most of the operators don't produce results when called. They return an object that remembers its source and what to do, and the work happens when something enumerates that object. Knowing when that is, and whether the work runs in your process or in a database, explains nearly every LINQ bug.

Why it matters: Most LINQ surprises (double database calls, queries that see later changes, filters that ran in memory instead of SQL) come from not knowing when and where a query runs.

  1. 1Deferred

    Where, Select, OrderBy and friends build a query object. Nothing runs until you enumerate it with foreach, ToList, Count or First.

  2. 2Re-run on every enumeration

    Enumerating the same query twice runs it twice and sees the source as it is at that moment. Materialise with ToList when you need a snapshot.

  3. 3Delegates or expression trees

    On IEnumerable<T>, lambdas are compiled delegates run in memory. On IQueryable<T>, they are expression trees a provider translates, for example into SQL.

  4. 4Watch the boundary

    Calling AsEnumerable, ToList or a method the provider can't translate moves the rest of the query into memory.

1

How it works

The explanation, with examples you can run.

Deferred execution

var numbers = new List<int> { 1, 2, 3 };int calls = 0;var evens = numbers.Where(n => { calls++; return n % 2 == 0; });Console.WriteLine($"after Where: {calls} calls");numbers.Add(4);Console.WriteLine($"count: {evens.Count()}, calls: {calls}");Console.WriteLine($"count: {evens.Count()}, calls: {calls}");var snapshot = evens.ToList();numbers.Add(6);Console.WriteLine($"snapshot: {snapshot.Count}, live: {evens.Count()}");

On .NET 10 this printed:

after Where: 0 callscount: 2, calls: 4count: 2, calls: 8snapshot: 2, live: 3

Where ran the predicate zero times. Each Count() ran it over all four elements again. ToList() captured a snapshot that later changes don't affect. The operators also stream: Where and Select pass elements on one at a time, so First() stops at the first match. Some operators, such as OrderBy, GroupBy and Reverse, must read the whole source before producing the first result.

The rule of thumb: if you'll enumerate a query more than once, or the source may change, call ToList() or ToArray() once and reuse the result. Return IReadOnlyList<T> from methods that have already done the work, so callers don't need to wonder.

Two kinds of lambdas

There are two sets of LINQ methods with the same names:

  • Enumerable.Where(this IEnumerable<T>, Func<T, bool>): the lambda compiles to a delegate, and LINQ to Objects calls it for each element in memory.
  • Queryable.Where(this IQueryable<T>, Expression<Func<T, bool>>): the compiler builds an expression tree, a data structure describing the lambda. The query provider (EF Core, for example) reads the tree and translates it, into SQL in EF Core's case.

The compiler picks one by the static type of the source. A DbSet<Order> is an IQueryable<Order>, so db.Orders.Where(o => o.Total > 100) becomes WHERE "Total" > 100. Pass the same DbSet to a method whose parameter is IEnumerable<Order> and the same-looking Where becomes Enumerable.Where: EF Core loads every row and the filter runs in C#.

Since EF Core 3.0, a query whose filter can't be translated (because it calls one of your own C# methods, for instance) throws rather than silently evaluating on the client, except in the final Select, where untranslatable parts are run in memory on the results. If you really want to continue in memory, say so with AsEnumerable() after the parts the database should do.

Performance notes

LINQ to Objects allocates enumerators and delegates, and lambdas that capture variables allocate a closure. In hot loops that matters; in most code it doesn't. Each recent release has made common operators faster, and the runtime team publishes the numbers every year in its performance blog posts.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.What does this print?
    var numbers = new List<int> { 1, 2, 3 };var evens = numbers.Where(n => n % 2 == 0);numbers.Add(4);Console.WriteLine(evens.Count());
  2. 2.`IQueryable<Order> q = db.Orders.Where(o => o.Total > 100);` What is `o => o.Total > 100` here?
  3. 3.A method takes `IEnumerable<Order>` and you pass it an EF Core DbSet. The method calls `.Where(o => o.Total > 100).ToList()`. Where does the filter run?

0 of 3 right so far. Get all three to finish this topic.

Go deeper