LINQ and Collections

The problem: a page that reads a million rows

Our shop has one million products. The “products in a category” page is slow, and the server’s memory goes up. This is the code:

public IEnumerable<Product> GetAll() => db.Products;

// somewhere else
var phones = repository.GetAll()
    .Where(p => p.CategoryId == categoryId)
    .ToList();

The code looks right. The Where condition is there. So why is the whole table read? The answer is in two ideas: deferred execution and the difference between IEnumerable and IQueryable.

Deferred Execution

A LINQ query is like a recipe, not the food itself. When you write Where and Select, you only write the recipe. Nothing happens.

The query runs only when someone asks for the result:

• A foreach loop over it.
• Methods like ToList, ToArray, ToDictionary.
• Methods that return a single value, like Count, First, Any, Sum.

var prices = new List<decimal> { 900, 5 };

var cheap = prices.Where(p => p < 100); // nothing runs here

prices.Add(8);

Console.WriteLine(cheap.Count()); // 2: the query runs now and sees the new price

Danger: running more than once

Because the query is only a recipe, it runs again from the start every time you ask for the result.

var newProducts = db.Products.Where(p => p.CreatedAt > lastWeek);

if (newProducts.Any())                 // query 1 to the database
{
    foreach (var p in newProducts)     // query 2 to the database
        Notify(p);
}

Here we go to the database twice. If a product is added between the two, the results of the two queries are also different. The right way: get the result once with ToListAsync, then work with that list.

IEnumerable or IQueryable?

These two interfaces look alike, but they have a big difference:

1. The IEnumerable type runs the query with C# code. The Where condition is a normal method. The data must first be in the app’s memory.
2. The IQueryable type keeps the query as a tree (Expression Tree). EF Core reads this tree and translates it to SQL. The condition runs in the database itself.

Now look again at the code at the start of the lesson. The GetAll method returns IEnumerable. So, step by step:

1. The Where method called after it is the IEnumerable Where, not the IQueryable one.
2. This Where cannot be translated to SQL. So EF Core sends a query with no condition.
3. One million rows come over the network, and one million objects are created in memory.
4. Then, in memory, all but a few hundred rows are thrown away.

The same thing happens with the AsEnumerable method. Everything after it runs in memory.

The right code

Write the condition and the column selection before the query runs. Read only the columns you need:

public sealed record ProductRow(int Id, string Name, decimal Price);

public Task<List<ProductRow>> GetByCategoryAsync(int categoryId, CancellationToken ct) =>
    db.Products
        .Where(p => p.CategoryId == categoryId)
        .OrderBy(p => p.Price)
        .Select(p => new ProductRow(p.Id, p.Name, p.Price))
        .Take(50)
        .ToListAsync(ct);

Choosing the right collection

Now the data is in memory. Which collection should we choose? The main question is: what do you do with it most?

A real example. From 10 thousand order lines, we want to find the ones whose item is out of stock:

// Slow: for each line, Contains checks the whole list
List<int> outOfStock = await LoadOutOfStockIdsAsync(ct);
var blocked = lines.Where(l => outOfStock.Contains(l.ProductId)).ToList();

// Fast: one hash lookup for each line
HashSet<int> outOfStockSet = [.. await LoadOutOfStockIdsAsync(ct)];
var blocked2 = lines.Where(l => outOfStockSet.Contains(l.ProductId)).ToList();

With 10 thousand lines and 10 thousand ids, the first version does up to a hundred million comparisons. The second version does about 10 thousand.

Method parameter and return types

• For a method’s input, take the most general type. If you only loop over the data, IEnumerable is enough.
• For a method’s output, return a read-only type. Like IReadOnlyList or IReadOnlyCollection. The caller knows the data is fully in memory, and can get its count without running anything again.
• Never return IEnumerable over a DbSet from a Repository. That is the same problem as at the start of the lesson.

A few useful methods

• The Any method instead of comparing Count with zero. The Any method stops at the first match. On a database, it also builds a lighter query.
• The ToDictionary or ToLookup method for many lookups. If in a loop you search for an item many times with Where or First, build a Dictionary first.
• The CountBy method, from .NET 9. Counting by a key, without building full groups.

// How many orders does each customer have?
foreach (var (customerId, count) in orders.CountBy(o => o.CustomerId))
    Console.WriteLine($"{customerId}: {count}");

Key rules

1. A LINQ query is a recipe, not a result. It does not run until someone asks for the result.
2. Get the result once. If you use the result more than once, call ToList first.
3. On a database, write the condition, Select and Take before running the query. So all of them are in the SQL.
4. Know the IQueryable boundary. After AsEnumerable, or after returning IEnumerable, everything is in memory.
5. For many lookups, not a List. Use a Dictionary or a HashSet.
6. In hot code, LINQ has a cost. Each lambda and each step has a small allocation. In normal code it does not matter. In hot code, measure.

Common mistakes