Deferred Execution in LINQ — How Queries Actually Run

What Deferred Execution Means

A LINQ query is not a result — it is a description of a computation. The computation runs when you enumerate the query, not when you define it.

// This line does NOT query the database
var query = db.Patients
    .Where(p => p.IsActive)
    .OrderBy(p => p.LastName);

// The database is NOT touched here

// Query runs HERE — when you enumerate
foreach (var patient in query)  // ← SQL executes now
    Console.WriteLine(patient.Name);

// Or here
var list = query.ToList();  // ← SQL executes now

This has a profound implication: you can compose queries before they execute, and the final SQL reflects all the composed conditions.

Deferred vs Immediate Operators

Deferred (return IEnumerable or IQueryable):
  Where, Select, OrderBy, GroupBy, Join, Skip, Take
  SelectMany, Distinct, Concat, Union, Except, Intersect
  → Do not execute until enumerated

Immediate (force execution, return a concrete value):
  ToList(), ToArray(), ToDictionary(), ToHashSet()
  First(), FirstOrDefault(), Single(), SingleOrDefault()
  Count(), Sum(), Min(), Max(), Average(), Any(), All()
  ElementAt(), Last()
  → Execute the query immediately

The Deferred Execution Bug

// Bug: query is deferred, but the DbContext is disposed before enumeration
public IEnumerable<Patient> GetActivePatients()
{
    using var db = new AppDbContext(options);
    return db.Patients.Where(p => p.IsActive);  // returns IQueryable — not executed!
}  // DbContext disposed here

// Caller:
var patients = GetActivePatients();  // still not enumerated
foreach (var p in patients)  // ← executes now, but DbContext is gone
    Console.WriteLine(p.Name);  // ObjectDisposedException

// Fix: enumerate inside the using block
public List<Patient> GetActivePatients()
{
    using var db = new AppDbContext(options);
    return db.Patients.Where(p => p.IsActive).ToList();  // executes before dispose
}

Production issue I've seen: A repository method returned IQueryable<T> from a short-lived DbContext. In unit tests, the test set up a context and disposed it before calling the service. In production, the DI-managed context was alive long enough most of the time — but under high load, the context was recycled between the return and the enumeration, causing intermittent ObjectDisposedException errors in production that did not reproduce in tests.

Multiple Enumerations

// Expensive: query executes twice
var patients = db.Patients.Where(p => p.IsActive);
var count    = patients.Count();           // query 1
var list     = patients.ToList();          // query 2

// Cheap: query executes once
var list     = db.Patients.Where(p => p.IsActive).ToList();
var count    = list.Count;                 // in-memory count, no DB

Query Composition with Deferred Execution

Deferred execution enables powerful composition:

// Build query conditionally — only ONE SQL query runs
public async Task<List<PatientDto>> GetPatientsAsync(PatientFilter filter)
{
    var query = db.Patients.AsQueryable();

    if (filter.Department is not null)
        query = query.Where(p => p.Department == filter.Department);

    if (filter.IsActive.HasValue)
        query = query.Where(p => p.IsActive == filter.IsActive.Value);

    if (!string.IsNullOrEmpty(filter.SearchTerm))
        query = query.Where(p => p.Name.Contains(filter.SearchTerm));

    query = filter.SortBy switch
    {
        "name" => query.OrderBy(p => p.LastName),
        "date" => query.OrderByDescending(p => p.CreatedAt),
        _      => query.OrderBy(p => p.Id)
    };

    return await query
        .Select(p => new PatientDto(p.Id, p.Name, p.Department))
        .Skip(filter.Page * filter.PageSize)
        .Take(filter.PageSize)
        .ToListAsync();  // single SQL query with all conditions applied
}

IEnumerable vs IQueryable

// IQueryable — filter runs in SQL (efficient)
var q1 = db.Patients
    .Where(p => p.Name.StartsWith("Sm"))  // translated to: WHERE Name LIKE 'Sm%'
    .ToList();

// IEnumerable — loads ALL records then filters in memory (expensive)
IEnumerable<Patient> all = db.Patients;   // loads entire table!
var q2 = all
    .Where(p => p.Name.StartsWith("Sm"))  // filters 100,000 rows in memory
    .ToList();

Rule: Stay in IQueryable land until you are ready to materialize. Do not call .AsEnumerable() or .ToList() until the query is fully built.

Debugging What SQL Gets Generated

// Option 1: Log SQL in development
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information)
           .EnableSensitiveDataLogging());  // dev only — logs parameter values

// Option 2: Use EF Core's ToQueryString()
var query = db.Patients.Where(p => p.IsActive).OrderBy(p => p.Name);
Console.WriteLine(query.ToQueryString());
// Output: SELECT [p].[Id], [p].[Name], ... FROM [Patients] AS [p]
//         WHERE [p].[IsActive] = 1 ORDER BY [p].[Name]

Red Flag / Green Answer

Red Flag: "We return IQueryable<T> from our repository methods so callers can add more filters."

Leaking IQueryable from the repository couples callers to EF Core. Any caller can add arbitrary queries — including expensive ones that bypass pagination, load navigation properties, or execute multiple round trips. The repository's purpose is to encapsulate data access, not expose the query builder.

Green Answer:

Repository methods accept filter parameters and return materialized results (List<T> or domain objects). Query composition stays inside the repository. Callers express intent (filter by department, page 2) — not queries.

Key Takeaway

LINQ queries are descriptions, not results. Execution is deferred until enumeration. This enables query composition: build conditions conditionally and emit one SQL query. The traps: using a disposed context after returning IQueryable, enumerating the same query twice, and calling .AsEnumerable() before filtering (which loads the table). Use ToList(), FirstOrDefault(), or ToListAsync() as the last step — not in the middle of composition.