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:
- Streaming:
Where,Select,Take,Skip,ConcatandDistinct(which remembers what it has seen) produce items one at a time and can stop early.First()on a streaming query reads only as many items as it needs. - Buffering:
OrderBy,GroupBy,Reverse, and the right side ofJoinmust read all of the source before returning anything.OrderBy(...).First()sorts the whole sequence;MinByorMindoes it in one pass.
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>
IEnumerable<T>operators take delegates (Func<T, bool>) and run in memory in your process.IQueryable<T>operators take expression trees (Expression<Func<T, bool>>). A provider such as Entity Framework Core inspects the tree and translates it, for example to SQL, so the filtering happens in the database.
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
- Materialise at a clear boundary (
ToList,ToArray,ToDictionary) and name the variable accordingly. - Prefer
Any()toCount() > 0, andCount(property) toCount()on collections that have one. - Use
FirstOrDefault/SingleOrDefaultdeliberately:Firstthrows on empty,Singlealso throws on more than one. - Use
ToLookuporGroupBy+ dictionary instead of aWhereinside a loop, which is quadratic. - Do not put side effects in
SelectorWhere; because they may run zero, one or many times, the result will be confusing. foreachplus modification of the source throwsInvalidOperationException("collection was modified") for the built-in collections.
Sources
- Microsoft Learn: Introduction to LINQ queries.
- Microsoft Learn: Classification of standard query operators by manner of execution.
- Microsoft Learn: How query expressions are translated by EF Core.
Practise this topic: C# & .NET