Parameterization, Query Compilation, Server Evaluation, and Unsupported Expressions

Learning outcomes

ServiceHub adds user-supplied customer filters and a local formatting helper. Two apparently harmless changes can have opposite consequences: a captured filter value is normally parameterized and reuses a query shape, while a non-translatable helper inside Where causes EF Core to throw. This lesson exposes query compilation, cache shape, parameterization, and the exact boundary where client evaluation is permitted.

1. Same query shape, different value

// 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}

static IQueryable<WorkOrder> ForCustomer(    ServiceHubContext db,    string customer)    => db.WorkOrders        .AsNoTracking()        .Where(w => w.CustomerName == customer);var q1 = ForCustomer(db, "Contoso Cold Storage");var q2 = ForCustomer(db, "Northwind Workshop");Console.WriteLine(q1.ToQueryString());Console.WriteLine(q2.ToQueryString());

The expression-tree structure is the same; the captured scalar value changes. EF can generally reuse its compiled query representation and send the customer as a database parameter.

.param set @__customer_0 'Contoso Cold Storage'SELECT ...FROM work_orders AS wWHERE w.customer_name = @__customer_0;

2. EF query compilation is not C# compilation

Before execution, EF processes a LINQ expression tree into a database command/materialization pipeline. Because that processing is expensive relative to simple in-memory method calls, EF caches outputs by query-tree shape. The cache lookup still compares expression-tree structure; it is separate from the database engine’s own SQL plan cache.

3. Deliberately wrong dynamic tree: embed a changing constant

static IQueryable<WorkOrder> BuildWrong(    ServiceHubContext db,    string customer){    var w = Expression.Parameter(typeof(WorkOrder), "w");    var customerProperty = Expression.Property(w, nameof(WorkOrder.CustomerName));    var changingConstant = Expression.Constant(customer); // shape/value changes    var body = Expression.Equal(customerProperty, changingConstant);    var lambda = Expression.Lambda<Func<WorkOrder, bool>>(body, w);    return db.WorkOrders.Where(lambda);}

Microsoft’s performance guidance shows this as a common dynamic-query mistake: constant nodes can create distinct expression shapes, force EF recompilation, and pollute database plan caches. Prefer ordinary parameterized LINQ or construct a parameterized expression only when dynamic tree construction is genuinely necessary.

var customer = request.Customer;var query = db.WorkOrders    .Where(w => w.CustomerName == customer); // captured value -> parameter

4. Modern EF does not silently client-evaluate a filter

static bool NeedsManualReview(string summary)    => summary.Contains("manual", StringComparison.OrdinalIgnoreCase)       || summary.Length > 240;var broken = db.WorkOrders    .Where(w => NeedsManualReview(w.Summary));var rows = await broken.ToListAsync(ct); // translation occurs; throws

EF Core permits client evaluation only in the top-level projection. A non-translatable expression elsewhere—such as a predicate—causes a runtime translation exception rather than silently downloading the table for client filtering.

InvalidOperationException:The LINQ expression '... NeedsManualReview(w.Summary) ...' could not be translated.Either rewrite the query in a form that can be translated, or switch toclient evaluation explicitly by inserting AsEnumerable/AsAsyncEnumerable/ToList/ToListAsync ...

5. Repair A: express the predicate in translatable terms

var review = db.WorkOrders    .Where(w => w.Summary.Contains("manual") || w.Summary.Length > 240)    .Select(w => new { w.Id, w.WorkOrderNumber, w.Summary });Console.WriteLine(review.ToQueryString());var rows = await review.ToListAsync(ct);

Whether a specific overload translates depends on provider/version. The safe workflow is: choose a provider-supported expression, inspect SQL, test semantics with edge cases, and keep the result bounded.

6. Repair B: make the client boundary explicit

var candidates = db.WorkOrders    .AsNoTracking()    .Where(w => w.Priority == WorkOrderPriority.High)    .OrderByDescending(w => EF.Property<DateTime>(w, "CreatedUtc"))    .Take(200)    .Select(w => new { w.Id, w.WorkOrderNumber, w.Summary })    .AsEnumerable(); // intentional boundaryvar review = candidates    .Where(w => NeedsManualReview(w.Summary))    .ToList();

This can be correct when the database cannot express the domain helper and the server-side phase already limits rows/columns to an acceptable set. Document why the client boundary exists and test its worst-case cardinality.

7. Top-level projection can contain client computation

static string DisplayNumber(string number)    => $"[{number}]";var rows = await db.WorkOrders    .AsNoTracking()    .Where(w => w.Priority == WorkOrderPriority.High)    .Select(w => new    {        w.Id,        Label = DisplayNumber(w.WorkOrderNumber) // top-level projection    })    .ToListAsync(ct);

EF retrieves the required database column and applies the non-translatable projection part in .NET. Be cautious with instance methods/objects captured by a cached query delegate; Microsoft documents potential memory-leak safeguards when constants cannot be parameterized.

8. Explicit compiled queries are an optimization, not a baseline

private static readonly Func<    ServiceHubContext,    WorkOrderPriority,    int,    IAsyncEnumerable<WorkOrder>> HotQueueQuery =        EF.CompileAsyncQuery(            (ServiceHubContext db, WorkOrderPriority priority, int take) =>                db.WorkOrders                    .AsNoTracking()                    .Where(w => w.Priority == priority)                    .OrderBy(w => EF.Property<DateTime>(w, "CreatedUtc"))                    .ThenBy(w => w.Id)                    .Take(take));await foreach (var row in HotQueueQuery(db, WorkOrderPriority.High, 50)    .WithCancellation(ct)){    // consume row}

Compiled-query delegates are thread-safe to invoke on different context instances, but one DbContext still cannot run concurrent operations. Microsoft also documents that compiled queries are tied to one EF model and are intended for simple scalar parameters. Benchmark before adding this complexity.

9. Query Cache Hit Rate is a diagnostic clue

EF Core exposes query-cache metrics; Microsoft notes that a normal application typically approaches a 100% hit rate after warmup once recurring query shapes have run. A persistently low rate suggests dynamically varying query trees or other cache-defeating construction. It is a clue, not a universal service-level objective: inspect actual query-generation code and workload before acting.

10. Hands-on lab: translation and cache shape

1. Run the same customer filter for two different customers and capture both ToQueryString() outputs; verify a parameter rather than customer literals where expected.
2. Implement the non-translatable NeedsManualReview predicate and capture the translation exception.
3. Repair it once as server-translatable LINQ and once as an explicit bounded AsEnumerable boundary.
4. Build the wrong dynamic constant-node expression for several customer values and compare expression text/query-cache diagnostics if available.
5. Replace it with ordinary captured-value LINQ.
6. Optionally benchmark a hot query with and without EF.CompileAsyncQuery; record Release build, dataset, provider, warmup, and sample distribution. Do not invent performance claims if the effect is below noise.

11. Production judgment and bridge

Prefer simple parameterized LINQ and stable query shapes. Treat translation exceptions as design feedback, not an invitation to insert AsEnumerable() at the top of the query. Keep explicit client work bounded and observable. Use compiled queries only for measured hotspots. Lesson 4 now addresses a separate runtime concern: asynchronous execution, streaming versus buffering, cancellation, and the fact that a context still supports only one active operation at a time.

Authoritative references

• Client vs Server Evaluation - EF Core — translation failures and permitted top-level client projection
• Advanced Performance Topics - EF Core — query caching, parameterization, dynamic queries, compiled queries
• How Queries Work - EF Core — translation/execution pipeline
• ToQueryString API - EF Core 10 — diagnostic translation inspection
• Efficient Querying - EF Core — keep filtering/projection at the database where appropriate