Deferred execution and querying a database

Why a query does nothing until you enumerate it, the IEnumerable versus IQueryable divide, and how EF Core turns expressions into SQL.

Nothing happens until you ask

LINQ operators that return a sequence build a description of work, not a result. Execution happens when something enumerates the query — a foreach, a ToList(), a Count(). This is deferred execution.

var query = orders.Where(o => o.Total > 100);   // no work done yet

orders.Add(new Order { Id = 99, Total = 500 });

var list = query.ToList();     // NOW it runs - and includes the order added above

// classic bug: the query closes over a loop variable
var results = new List<IEnumerable<Order>>();
foreach (var country in new[] { "FR", "DE" })
{
    results.Add(orders.Where(o => o.Country == country));
}
// outside the loop, every query already sees the final value of country

// fix: materialise inside the loop, or copy the variable
foreach (var country in new[] { "FR", "DE" })
{
    var c = country;
    results.Add(orders.Where(o => o.Country == c).ToList());
}

Deferral is a feature: it lets you compose a query from several methods and execute it once. It becomes a bug the moment the source changes between composition and enumeration.

IEnumerable versus IQueryable

EF Core in practice

// Projection: fetch only the columns you need
var summaries = await db.Orders
    .Where(o => o.Placed >= cutoff && o.Status == OrderStatus.Open)
    .OrderBy(o => o.Placed)
    .Select(o => new OrderSummary(o.Id, o.Customer.Name, o.Total))
    .Take(50)
    .ToListAsync(ct);          // one SQL round trip, deferred until here

// Read-only queries: skip change tracking
var names = await db.Customers
    .AsNoTracking()
    .Where(c => c.Country == country)
    .Select(c => c.Name)
    .ToListAsync(ct);

// Eager loading related data in one query
var withLines = await db.Orders
    .Include(o => o.Lines)
    .Where(o => o.Id == id)
    .AsSplitQuery()            // avoids a cartesian explosion with many includes
    .FirstOrDefaultAsync(ct);

// Aggregation runs on the server
var stats = await db.Orders
    .GroupBy(o => o.Country)
    .Select(g => new { Country = g.Key, Revenue = g.Sum(o => o.Total) })
    .ToListAsync(ct);

// Batched updates without loading rows
await db.Orders
    .Where(o => o.Placed < cutoff && o.Status == OrderStatus.Open)
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.Status, OrderStatus.Expired), ct);

• The provider translates the expression tree, so a method it does not know throws at runtime rather than being evaluated client-side (EF Core 3.0 and later).
• AsNoTracking is the default choice for read paths; tracked entities cost memory and change-detection time.
• Include without a projection loads full entities. A projection usually produces narrower, faster SQL.
• Always pass a CancellationToken to the async operators so an abandoned request stops costing the database.
• AsSplitQuery trades one round trip for several, which is usually the right call when collections multiply rows.

// N+1: one query for orders, then one per order
foreach (var order in await db.Orders.ToListAsync(ct))
{
    var count = await db.Lines.CountAsync(l => l.OrderId == order.Id, ct);
}

// fixed: aggregate and group in a single query
var counts = await db.Orders
    .Select(o => new { o.Id, Lines = o.Lines.Count })
    .ToDictionaryAsync(x => x.Id, x => x.Lines, ct);

FAQ

Related

Common LINQ operators Controllers, dependency injection and middleware

Last refreshed 2026-09-18.