Optimizing EF Core with Compiled Queries: When and How to Use Them

The Problem: Repeated Query Translation Overhead

Every time you call a LINQ query against a DbContext, EF Core parses the expression tree, builds a SQL string, and maps parameters. For a high‑frequency query—say a dashboard that pulls the same data 10 000 times per minute—this translation can dominate the total latency.

EF Core 2.1 introduced compiled queries to address this. By compiling the expression once, the framework re‑uses the generated SQL plan and parameter bindings, saving the cost of expression‑tree analysis on each call.

Compiled Queries: The Core Idea

A compiled query is a delegate created by EF.CompileAsyncQuery (for async) or CompiledQuery.Compile (for sync). When invoked, the delegate re‑uses a cached execution plan. The delegate must be executed while its DbContext is alive; otherwise EF Core throws an ObjectDisposedException.

Defining a Compiled Query

Below is a minimal example that shows how to declare a compiled query for a simple entity Product filtered by CategoryId and Price. The code is intended to run in a console app targeting .NET 6.

// Product entity
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int CategoryId { get; set; }
    public decimal Price { get; set; }
}

// DbContext
public class ShopContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlServer("Server=.;Database=Shop;Trusted_Connection=True;");
}

// Compiled query delegate
private static readonly Func<ShopContext, int, decimal, Task<List<Product>>>
    _productsByCategoryAndPrice = EF.CompileAsyncQuery(
        (ShopContext ctx, int categoryId, decimal maxPrice) =>
            ctx.Products
               .Where(p => p.CategoryId == categoryId)
               .Where(p => p.Price <= maxPrice)
               .ToListAsync());

Key points:

• The delegate is static so that the compiled plan is shared across all contexts.
• Only server‑side evaluable expressions are allowed. If you call a local method inside the query, EF will fall back to normal translation and the cache will not be used.
• Because the delegate captures the ShopContext only as a parameter, you can pass any context instance at runtime.

Using It in a Real App

Here’s how you would call the compiled query inside a typical service method. Notice that the context is created in a using block, so the delegate is executed before the context is disposed.

public async Task<List<Product>> GetAffordableProductsAsync(int categoryId, decimal maxPrice)
{
    await using var ctx = new ShopContext();
    return await _productsByCategoryAndPrice(ctx, categoryId, maxPrice);
}

If you try to call the delegate after the context has been disposed, EF Core throws ObjectDisposedException. Therefore, the delegate should not be stored in a field that outlives the context.

Measuring the Gain

To quantify the benefit, run the same query 10 000 times with both a compiled and a non‑compiled version. Use Stopwatch for timing and enable EF Core logging to the console so you can verify that the SQL is emitted only once for the compiled delegate.

// Non‑compiled loop
var sw = Stopwatch.StartNew();
for (int i = 0; i < 10000; i++)
{
    await using var ctx = new ShopContext();
    var list = await ctx.Products
        .Where(p => p.CategoryId == 1)
        .Where(p => p.Price <= 50m)
        .ToListAsync();
}
sw.Stop();
Console.WriteLine($"Non‑compiled: {sw.ElapsedMilliseconds} ms");

// Compiled loop
sw.Restart();
for (int i = 0; i < 10000; i++)
{
    await using var ctx = new ShopContext();
    var list = await _productsByCategoryAndPrice(ctx, 1, 50m);
}
sw.Stop();
Console.WriteLine($"Compiled: {sw.ElapsedMilliseconds} ms");

Typical results on a modest machine show a 20–30% reduction in average latency for the compiled version. The exact numbers depend on the query complexity and database engine.

When to Avoid Compiled Queries

• Dynamic query parts: If you need to add optional Where clauses based on runtime flags, you must create a separate compiled delegate for each variation or revert to a normal query. Maintaining many compiled delegates can become unwieldy.
• Client‑side evaluation: Calls to local methods, custom types, or unsupported operators prevent compilation. The query will be executed normally each time, negating the benefit.
• Short‑lived contexts: In scenarios where contexts are disposed immediately after each use (e.g., per‑request in a web API), the compiled delegate may still be useful, but you must ensure the delegate is invoked before disposal. In very high‑traffic services, the overhead of context creation may dominate.

Takeaway

Compiled queries are a proven, low‑maintenance way to shave query translation time in EF Core. They are fully supported from EF Core 2.1 through the current LTS releases and require only a small change to your query code. However, they work best for static, server‑side evaluable queries. If your data‑access logic is highly dynamic or relies on client‑side evaluation, stick to normal LINQ queries or consider alternative caching strategies.

Next steps: add EF Core logging to verify the SQL emission, experiment with a multi‑parameter query, and monitor the impact in your production workload.