Compiled Queries, EF Query Cache Behavior, and When Compilation Helps

Learning outcomes

1. Most queries are already “compiled” and cached internally

When EF Core receives a LINQ expression tree, it translates the tree and prepares a database command/materializer. That work is cached by query shape. A later query with the same expression structure can reuse the cached compilation even when parameter values differ. The database may separately cache or prepare its own execution plan; that is an engine/driver concern, not EF’s query cache.

Explicit compiled queries are therefore not the first optimization. They bypass EF’s expression-tree cache lookup and some associated processing. Microsoft’s own guidance describes that overhead as negligible in most applications compared with network I/O and database execution.

2. Stable parameterization gives EF one reusable shape

static IQueryable<WorkOrder> QueueFor(    ServiceHubContext db,    WorkOrderPriority priority)    => db.WorkOrders        .AsNoTracking()        .Where(w => w.Priority == priority)        .OrderByDescending(w => EF.Property<DateTime>(w, "CreatedUtc"))        .ThenBy(w => w.Id);var high = await QueueFor(db, WorkOrderPriority.High).Take(25).ToListAsync(ct);var normal = await QueueFor(db, WorkOrderPriority.Normal).Take(25).ToListAsync(ct);

The captured method parameter becomes a SQL parameter, so the expression-tree shape is stable. Compare that with dynamically constructing an expression tree containing a new constant node for every request; EF sees different shapes and the database may also see different SQL text.

var parameter = Expression.Parameter(typeof(WorkOrder), "w");var member = Expression.Property(parameter, nameof(WorkOrder.CustomerName));var constant = Expression.Constant(customerName); // different tree value embeddedvar body = Expression.Equal(member, constant);var predicate = Expression.Lambda<Func<WorkOrder, bool>>(body, parameter);var rows = await db.WorkOrders.Where(predicate).ToListAsync(ct);

When dynamic composition is actually necessary, prefer ordinary lambdas or construct parameterized expression shapes. A persistent EF Query Cache Hit Rate below 100% after warm-up is a diagnostic signal that dynamic shapes may be defeating the cache; it is not itself proof of user-visible slowness.

3. Compile the actual hot query, not an artificial wrapper

private static readonly Func<    ServiceHubContext,    WorkOrderPriority,    int,    IAsyncEnumerable<WorkOrderQueueItem>> HotQueue =    EF.CompileAsyncQuery(        (ServiceHubContext db, WorkOrderPriority priority, int take) =>            db.WorkOrders                .AsNoTracking()                .Where(w => w.Priority == priority)                .OrderByDescending(w => EF.Property<DateTime>(w, "CreatedUtc"))                .ThenBy(w => w.Id)                .Take(take)                .Select(w => new WorkOrderQueueItem(                    w.Id,                    w.WorkOrderNumber,                    w.CustomerName,                    w.Priority,                    EF.Property<DateTime>(w, "CreatedUtc"))));var results = new List<WorkOrderQueueItem>();await foreach (var item in HotQueue(db, WorkOrderPriority.High, 50)    .WithCancellation(ct)){    results.Add(item);}

The compiled delegate is thread-safe and may be invoked concurrently on different context instances. It does not make one DbContext thread-safe. Chapter 08’s one-active-operation-per-context rule still applies.

4. Understand the limitations before turning queries into static fields

5. Failure case: constant-shaped dynamic filters poison the cache

ServiceHub adds a configurable report builder. A developer uses the expression API and inserts request values as Expression.Constant. Every distinct value can force a new EF compilation and potentially a distinct database plan. CPU rises even though the SQL operation is logically identical.

IQueryable<WorkOrder> query = db.WorkOrders.AsNoTracking();if (!string.IsNullOrWhiteSpace(customerName)){    var value = customerName.Trim();    query = query.Where(w => w.CustomerName == value);}var rows = await query    .OrderBy(w => w.Id)    .Take(100)    .ToListAsync(ct);

The repair has the same request-dependent behavior without manually manufacturing a constant expression node.

6. Separate first-use, warm-cache, and compiled-delegate measurements

Run three phases in Release mode: (1) cold process/model/query first use; (2) normal LINQ after warm-up; (3) explicit compiled delegate after warm-up. Keep the database query tiny and indexed so database I/O does not swamp the CPU difference, but still acknowledge that the test executes a real SQLite command. Repeat many operations and report distributions rather than one stopwatch sample.

// Warm the model and the ordinary query first.await db.WorkOrders.Where(w => w.Id > 0).Take(1).ToListAsync(ct);for (var i = 0; i < iterations; i++){    await using var iterationDb = await factory.CreateDbContextAsync(ct);    var priority = (i & 1) == 0        ? WorkOrderPriority.High        : WorkOrderPriority.Normal;    await foreach (var _ in HotQueue(iterationDb, priority, 1).WithCancellation(ct))    {    }}

Creating a fresh context here prevents tracked-state growth from contaminating the loop. Lesson 4 later measures whether context pooling changes context-creation overhead.

7. What Query Cache Hit Rate can and cannot tell you

EF exposes metrics that include query-cache hit rate. After the application’s normal query shapes have executed, a healthy stable workload normally approaches 100%. A persistently lower value can point to dynamic shape churn. It does not identify slow SQL, missing indexes, lock waits, N+1 I/O, or network latency. Chapter 18 will wire those metrics into observability; here the metric is a hypothesis generator.

8. Lab decision record

1. Capture an ordinary parameterized queue query and prove repeated SQL shape.
2. Introduce a constant-shaped dynamic expression and record the churn in generated SQL/query-cache behavior.
3. Repair the shape.
4. Implement EF.CompileAsyncQuery for one stable, frequently executed queue query.
5. Benchmark normal warm-cache versus explicit compiled delegate under the same provider/data/build settings.
6. Keep the compiled path only if the measured CPU/latency gain is material to the workload; otherwise prefer ordinary LINQ for maintainability.

9. Production judgment

Compiled queries optimize a small client-side phase. They are valuable in high-throughput paths where query execution is already cheap and EF overhead is measurable. They are irrelevant to a table scan, lock wait, slow network, oversized result set, or N+1 pattern. Chapter 17 next moves earlier in the lifecycle—from per-query processing to the cost of constructing a large EF model at first use.

Authoritative references

Performance behavior is workload-, provider-, and version-sensitive. Re-check these primary sources before carrying a result into production.

• Advanced Performance Topics - EF Core — compiled queries, query cache, parameterization and Query Cache Hit Rate
• Efficient Querying - EF Core — database-side performance context
• Async programming with EF Core — async execution and context-operation rules
• Tracking Queries — tracking overhead/semantics in read paths
• Performance Diagnosis — diagnosing before optimizing