CS Fundamentals Trainer · Guides

LINQ in C#: deferred execution, multiple enumeration and IQueryable

Why a LINQ query runs when you enumerate it and not where you write it, how that causes repeated work and surprising results, the difference between IEnumerable (runs in memory) and IQueryable (translated to SQL), which operators buffer or stream, and the habits that avoid the usual bugs.

3 min read

LINQ (Language Integrated Query) gives collections, databases and XML a shared set of operators: Where, Select, OrderBy, GroupBy, Join and many more. Its most important property is not syntactic. It is that most query operators do not run when you call them.

Deferred execution

Where, Select, Take, OrderBy and friends return an object that describes the query. Nothing is computed until something enumerates it: a foreach, ToList(), ToArray(), Count(), First(), Any(), Sum() and so on.

var numbers = new List<int> { 1, 2, 3 };
var evens = numbers.Where(n => n % 2 == 0);   // nothing has run yet
numbers.Add(4);
Console.WriteLine(string.Join(",", evens));   // 2,4  (the query sees the list at enumeration time)

The result includes 4 because the query reads numbers when it is enumerated, not when it was written. To take a snapshot at a point in time, materialise: var snapshot = numbers.Where(...).ToList();.

Multiple enumeration

Each enumeration of a deferred query runs it again from the start. If the source is a database or does expensive work, that is repeated, and it may even return different data the second time.

var query = GetOrders().Where(o => o.Total > 100);    // GetOrders hits the database
if (query.Any()) { foreach (var o in query) { ... } } // the database is queried twice

Fix: enumerate once, into a list, if you need the data more than once (var list = query.ToList();). Analyzers (CA1851, and rules in ReSharper/Rider) warn about this. As an API design rule, take IEnumerable<T> as a parameter only if you enumerate it once; otherwise take IReadOnlyCollection<T> or a list.

Streaming and buffering operators

Operators fall into two groups:

Closures and captured variables

A lambda captures variables, not values, so a query that depends on a variable that changes before enumeration sees the new value.

int limit = 2;
var q = numbers.Where(n => n > limit);
limit = 3;                       // q now uses 3 when it runs

IEnumerable<T> or IQueryable<T>

var cheap = db.Products.Where(p => p.Price < 10).ToList();   // WHERE Price < 10 in SQL

The classic mistake is switching to IEnumerable too early, for example by calling AsEnumerable(), ToList() or passing the query through a method that takes IEnumerable<T>. Everything after that runs in memory, so a filter after that point fetches the entire table first. Also, only some C# can be translated: calling your own method inside a Where either throws or (in older EF) silently pulls data into memory. Keep the query to translatable expressions until the last moment, and ToListAsync() once.

Useful habits

  1. Materialise at a clear boundary (ToList, ToArray, ToDictionary) and name the variable accordingly.
  2. Prefer Any() to Count() > 0, and Count (property) to Count() on collections that have one.
  3. Use FirstOrDefault/SingleOrDefault deliberately: First throws on empty, Single also throws on more than one.
  4. Use ToLookup or GroupBy + dictionary instead of a Where inside a loop, which is quadratic.
  5. Do not put side effects in Select or Where; because they may run zero, one or many times, the result will be confusing.
  6. foreach plus modification of the source throws InvalidOperationException ("collection was modified") for the built-in collections.

Sources

Practise this topic: C# & .NET

More guides