LINQ and Deferred Execution

Real-world analogy

What is it?

LINQ (Language Integrated Query) is a set of methods to filter, sort, group and reshape sequences. The same syntax works for lists, XML and databases. Most operators are deferred: they build a description of the work and do it only when you enumerate. Knowing when it runs, and where it runs, explains most slow or surprising LINQ code.

Real-world relevance

A dashboard page builds a filtered query, then shows the count and loops over the rows. Without a ToList(), that is two database queries. The other classic is a controller that calls _db.Orders.ToList() and then filters by user. That pulls a million rows into memory to return five.

Key points

• Core operators — Where filters, Select reshapes, OrderBy sorts, GroupBy groups, and Any asks if something exists. Chain them to describe what you want.
• First, FirstOrDefault, Single — First throws if nothing matches. FirstOrDefault returns null or default. Single throws if there are zero or more than one, so use it when exactly one is a rule.
• Deferred execution — Writing var q = list.Where(...) runs nothing. The work happens when you loop over q or call Count(), First() or ToList().
• ToList runs it now — ToList() executes the query immediately and stores the results in memory. Later changes to the source do not change that list.
• Multiple enumeration costs — Each time you enumerate a deferred query, it runs again. With a database that means a second round trip, and with an expensive filter, doing the work twice.
• IEnumerable versus IQueryable — IEnumerable runs in your app memory. IQueryable keeps the query as an expression that EF Core translates to SQL and runs in the database.
• ToList too early is a trap — db.Orders.ToList().Where(...) loads the whole table, then filters in memory. Filter first, so the database does it.
• Select versus SelectMany — Select gives one result per item, which may be a list per item. SelectMany flattens those lists into one sequence.

Code example

var numbers = new List<int> { 1, 2, 3, 4, 5, 6 };
var calls = 0;

// Only a recipe. Nothing runs yet, so calls is still 0.
var evens = numbers.Where(n =>
{
    calls++;
    return n % 2 == 0;
});

var count = evens.Count();   // runs the filter over all 6 items
var first = evens.First();   // runs it again, up to the first match
Console.WriteLine($"Filter calls: {calls}");   // 8, not 6

calls = 0;

// Run once, keep the result in memory
var cached = numbers.Where(n =>
{
    calls++;
    return n % 2 == 0;
}).ToList();

var count2 = cached.Count;   // no query runs
var first2 = cached[0];      // no query runs
Console.WriteLine($"Filter calls: {calls}");   // 6

Line-by-line walkthrough

1. 1. calls counts how many times the filter function is really called.
2. 2. The Where line only describes the filter. calls is still 0 after it.
3. 3. Count() has to look at every item, so it runs the whole query. First() then runs it again from the start.
4. 4. Count calls the filter 6 times, and First stops after the first even number, which is 2 calls. So calls is 8.
5. 5. ToList() runs the filter one time over all 6 items and stores the 3 results in a real list.
6. 6. Count and the index on the list read memory only. The filter is not called again.

Spot the bug

public List<Order> GetOrders(int userId)
{
    return _db.Orders
        .ToList()
        .Where(o => o.UserId == userId)
        .ToList();
}

Explain like I'm 5

Fun fact

Hands-on challenge

More resources

• Language Integrated Query (LINQ) (Microsoft Learn)
• Query execution in LINQ (Microsoft Learn)