IQueryable, Deferred Execution, Expression Trees, and Where Database Work Actually Happens

Learning outcomes

A ServiceHub API endpoint must return only open high-priority work orders for one customer. A developer writes several LINQ calls and assumes every line is “running against the database.” That mental model makes it easy to download too much data, execute the same query twice, or move filtering into memory by accident. This lesson makes the boundary explicit: query construction builds an expression tree; query execution happens only when a terminal operation enumerates the provider-backed query.

1. The stable course domain stays the same

// Existing course model — deliberately not redesigned for Chapter 08.public sealed partial class WorkOrder{    public int Id { get; private set; }    public WorkOrderPublicId PublicId { get; private set; }    public string WorkOrderNumber { get; private set; } = string.Empty;    public string CustomerName { get; private set; } = string.Empty;    public string Summary => _summary;    public WorkOrderPriority Priority { get; private set; }    public DateTimeOffset OpenedUtc { get; private set; }    // Chapter 04 also maps shadow DateTime "CreatedUtc" -> created_utc    // so the SQLite lab can compare/order a UTC timestamp server-side.    public int? AssignedTechnicianId { get; private set; }    public Technician? AssignedTechnician { get; private set; }    public ServiceAddress ServiceAddress { get; private set; } = null!;    public Guid Revision { get; private set; }}public enum WorkOrderPriority{    Low = 1,    Normal = 2,    High = 3}

Chapters 01–07 evolved this model deliberately. Chapter 08 does not introduce a new persistence model; it asks a new question: when a C# LINQ pipeline targets DbSet<WorkOrder>, which parts remain as provider-translatable structure and which parts have already become ordinary in-memory .NET work?

SQLite timestamp boundary inherited from Chapter 04

The domain still exposes OpenedUtc : DateTimeOffset, but the Microsoft SQLite provider does not support all DateTimeOffset comparison/ordering operations. Chapter 04 therefore added shadow CreatedUtc : DateTime mapped to created_utc specifically for server-side SQLite ordering/comparison. The mandatory Chapter 08 labs reuse that property; SQL Server/PostgreSQL/provider-specific labs may use richer timestamp mappings after verifying provider semantics.

2. DbSet begins as IQueryable

IQueryable<WorkOrder> query = db.WorkOrders    .AsNoTracking();if (!string.IsNullOrWhiteSpace(customer))    query = query.Where(w => w.CustomerName == customer);query = query    .Where(w => w.Priority == WorkOrderPriority.High)    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id);Console.WriteLine(query.GetType().FullName);Console.WriteLine(query.Provider.GetType().FullName);Console.WriteLine(query.Expression);Console.WriteLine(query.ToQueryString());// No ToList/First/Count/await foreach yet: no result rows have been read.

Where, OrderBy, and ThenBy are compositional here. They create new query objects that carry a larger expression tree. ToQueryString() asks EF to render diagnostic SQL, but Microsoft documents it as a debugging representation; it is not the result set and is not a timing or execution-plan API.

3. Expression tree versus delegate

Func<WorkOrder, bool> compiledDelegate =    w => w.Priority == WorkOrderPriority.High;Expression<Func<WorkOrder, bool>> expressionTree =    w => w.Priority == WorkOrderPriority.High;Console.WriteLine(compiledDelegate.Method);Console.WriteLine(expressionTree.Body.NodeType); // EqualConsole.WriteLine(expressionTree);

A delegate is executable IL-backed behavior; EF cannot generally decompile an arbitrary delegate back into SQL. An expression tree preserves nodes such as member access, constants, parameters, equality, method calls, and logical operators. Queryable.Where accepts Expression<Func<T,bool>>, which is why EF can inspect the lambda before execution.

4. Terminal operators cross the I/O boundary

Console.WriteLine("Before execution");var rows = await query    .Take(25)    .ToListAsync(ct); // command is sent hereConsole.WriteLine($"Materialized {rows.Count} rows");

EF command logging should place the SELECT between those messages. That timestamped evidence is stronger than guessing from fluent syntax.

5. Representative SQL proves server-side composition

SELECT w.work_order_id,       w.work_order_number,       w.customer_name,       w.priority,       w.created_utc,       w.assigned_technician_id,       w.revisionFROM work_orders AS wWHERE w.customer_name = @__customer_0  AND w.priority = 3ORDER BY w.created_utc, w.work_order_idLIMIT @__p_1;

The exact projection may include additional mapped columns such as JSON/complex-value storage; provider patch versions can also vary aliases and parameter declarations. The mechanism to verify is that predicates, ordering, and limit are represented in SQL before rows are materialized.

6. Deliberately wrong: materialize first, filter second

// WRONG for a large table: this executes first.var all = await db.WorkOrders    .AsNoTracking()    .ToListAsync(ct);// Ordinary LINQ-to-Objects now filters in process memory.var urgentForCustomer = all    .Where(w => w.CustomerName == customer)    .Where(w => w.Priority == WorkOrderPriority.High)    .Take(25)    .ToList();

The SQL log for the first command has no customer/priority predicate and no 25-row limit. The database sends every selected row, EF materializes every entity, and only then does the process discard most of them. Small development data can hide this failure.

var urgentForCustomer = await db.WorkOrders    .AsNoTracking()    .Where(w => w.CustomerName == customer)    .Where(w => w.Priority == WorkOrderPriority.High)    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))    .ThenBy(w => w.Id)    .Take(25)    .ToListAsync(ct);

Verify the repair with ToQueryString() and command logs. Do not merely assert that it is “more optimized.”

7. AsEnumerable is an explicit semantic boundary

var serverPart = db.WorkOrders    .AsNoTracking()    .Where(w => w.Priority == WorkOrderPriority.High)    .Select(w => new { w.Id, w.WorkOrderNumber, w.Summary });var clientPart = serverPart    .AsEnumerable() // after this point: LINQ-to-Objects    .Where(x => LocalSummaryPolicy(x.Summary));

AsEnumerable() is not “make EF translate more.” It deliberately stops composing provider SQL. Use it only after bounding/projecting the database result enough that client work is intentional and reviewed. Lesson 3 returns to this boundary for non-translatable expressions.

8. A query object can execute more than once

var high = db.WorkOrders    .AsNoTracking()    .Where(w => w.Priority == WorkOrderPriority.High);var count = await high.CountAsync(ct); // command 1var rows = await high.ToListAsync(ct); // command 2Console.WriteLine($"Count={count}, materialized={rows.Count}");

This may be exactly what you intend, but deferred execution means storing IQueryable does not cache results. If one logical operation needs both count and rows, decide whether two commands are acceptable, whether count is actually needed, and what consistency you expect if data changes between commands.

9. Hands-on lab: prove the boundary

1. Use the existing disposable servicehub-lab.db and deterministic seed; add enough rows that an unbounded query is visible in logs.
2. Enable Microsoft.EntityFrameworkCore.Database.Command logging without sensitive-data logging.
3. Build the customer/high-priority query and print Expression and ToQueryString(); confirm no result command has executed.
4. Call ToListAsync and capture the command plus returned row count.
5. Repeat the deliberately wrong materialize-first version and compare SQL shape, materialized row count, and process allocations only if you measure them.
6. Enumerate one IQueryable twice and count commands.
7. Reset/reseed only the disposable lab if you changed its dataset.

10. Production judgment and bridge

Keep filters, projections, ordering, and cardinality limits on IQueryable until there is a deliberate reason to cross into memory. Verify important queries with SQL/log evidence and tests against the production-like provider. Do not expose long-lived IQueryable across disposed-context or architectural boundaries just to preserve “flexibility”; it also carries provider/context lifetime assumptions. Lesson 2 now shapes real result sets: projections, deterministic ordering, pagination, distinctness, and SQL/C# NULL semantics.

Authoritative references

• How Queries Work - EF Core — query construction/execution mental model
• Client vs Server Evaluation - EF Core — explicit client/server boundaries and translation rules
• ToQueryString API - EF Core 10 — diagnostic SQL representation
• Asynchronous Programming - EF Core — async terminal operators and AsAsyncEnumerable
• Efficient Querying - EF Core — project/filter/limit database work