Query performance and avoiding N+1

Multiple enumeration, N+1 queries, missing indexes and the measurement discipline that stops you from optimising the wrong thing.

The expensive mistakes

// 1. Multiple enumeration of a deferred, expensive source
IEnumerable<Order> Expensive()
{
    Console.WriteLine("query executed");
    return new[] { new Order(1, OrderStatus.Open, 10m) };
}

var q = Expensive().Where(o => o.Total > 5);
var count = q.Count();          // executes
var first = q.First();          // executes again
var list = q.ToList();          // and again

// Fix: materialise once when you know you need more than one pass
var once = q.ToList();

// 2. Contains on a large local collection compiles to a huge IN list
var ids = Enumerable.Range(1, 50_000).ToArray();
// var bad = db.Orders.Where(o => ids.Contains(o.Id)).ToList();
// 50,000 parameters: slow to build, may exceed the parameter limit

// Fix: a temporary table, a table-valued parameter, or chunking
foreach (var chunk in ids.Chunk(1000))
{
    var part = db.Orders.Where(o => chunk.Contains(o.Id)).ToList();
}

// 3. N+1 from lazy iteration
var customers = db.Customers.ToList();
// foreach (var c in customers) c.Orders.Count;   // one query per customer

// Fix: one query with a projection
var counts = db.Customers
    .Select(c => new { c.Id, Orders = c.Orders.Count() })
    .ToDictionaryAsync(x => x.Id, x => x.Orders);

// 4. Eager loading with several collections causes a cartesian explosion
var withAll = db.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()                     // separate queries instead of a cross join
    .ToList();

// 5. A query inside a loop that could be a single grouped query
var totals = db.Orders
    .Where(o => customerIds.Contains(o.CustomerId))
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Total = g.Sum(o => o.Total) })
    .ToDictionaryAsync(x => x.CustomerId, x => x.Total);

Measure, then change one thing

// A deliberately blunt stopwatch harness. Good enough to spot an order of
// magnitude, which is what LINQ problems usually are.
public static async Task TimeAsync(string label, Func<Task> work, int runs = 5)
{
    await work();                              // warm up
    var sw = System.Diagnostics.Stopwatch.StartNew();
    for (var i = 0; i < runs; i++) await work();
    sw.Stop();
    Console.WriteLine(label + ": " + (sw.ElapsedMilliseconds / runs) + " ms per run");
}

await TimeAsync("projection", async () =>
{
    await db.Orders.Select(o => new { o.Id, o.Total }).ToListAsync();
});
await TimeAsync("full entity", async () =>
{
    await db.Orders.ToListAsync();
});

// For a hot path, use a real benchmark and count allocations
// [MemoryDiagnoser] gives Allocated and Gen0 columns, which is what you need
// when the problem is churn rather than time.

FAQ

Related

Deferred execution and querying a database Debugging and testing LINQ queries

Last refreshed 2026-09-18.