Speeding Up EF Core with Compiled Queries: When, How, and What to Watch For

Why the Runtime Translation Matters

When you write a LINQ query against a DbContext, EF Core builds an expression tree, translates it into SQL, and then executes that SQL. For simple, one‑off queries the overhead of building the expression tree and performing the translation is negligible. But in data‑heavy applications that run the same query hundreds or thousands of times, the repeated translation can become a measurable bottleneck.

EF Core 2.1 introduced the EF.CompileQuery<T>() API. It allows you to pre‑compile a LINQ expression into a delegate that EF can reuse. The first execution still incurs the translation cost, but every subsequent run uses a cached, already‑compiled delegate, reducing the per‑query overhead by roughly 30‑50% in many real‑world scenarios.

When to Consider Compiled Queries

• Repetitive, parameterised queries – e.g., fetching orders by status, retrieving product lists for a shop.
• The query shape does not change between calls – same columns, same joins.
• You have a warm‑up phase where the first execution latency is acceptable.
• Application runs in a high‑throughput environment (e.g., API gateway, background job processor).

Compiled queries are not a silver bullet for ad‑hoc or highly dynamic LINQ. They also require that EF can translate the entire expression tree; any client‑side evaluation will throw at runtime.

How to Implement a Compiled Query

Below is a minimal, production‑ready example that shows how to define, register, and use a compiled query for retrieving orders by status. The example assumes EF Core 5.0+ and a Order entity with a Status enum.

// Order.cs
public class Order
{
    public int Id { get; set; }
    public OrderStatus Status { get; set; }
    public DateTime CreatedAt { get; set; }
    // … other properties
}

// OrderStatus.cs
public enum OrderStatus
{
    Pending,
    Shipped,
    Cancelled
}

// OrderRepository.cs
public static class OrderRepository
{
    // The compiled query is static and lives for the life of the process.
    private static readonly Func<AppDbContext, OrderStatus, IQueryable<Order>> _getByStatus;

    static OrderRepository()
    {
        // Compile once – the delegate is cached in EF's internal ICompiledQueryCache.
        _getByStatus = EF.CompileQuery((AppDbContext ctx, OrderStatus status) =>
            ctx.Orders
               .Where(o => o.Status == status)
               .OrderByDescending(o => o.CreatedAt)
               .AsNoTracking());
    }

    public static async Task<List<Order>> GetByStatusAsync(AppDbContext ctx, OrderStatus status)
    {
        return await _getByStatus(ctx, status).ToListAsync();
    }
}

Key points:

• EF.CompileQuery returns a Func<TContext, TParams, IQueryable<TEntity>> that you can store as a static field.
• All parameters must be plain values – no lambda expressions or custom delegates inside the query.
• Because the delegate is static, it lives for the life of the application; EF Core automatically stores it in its compiled‑query cache.
• Use AsNoTracking() if you only need read‑only data; it avoids change‑tracking overhead.

Measuring the Gain

To see the performance difference, run a simple benchmark that executes the query 1,000 times with and without the compiled version. Here’s a concise example that uses Stopwatch and EF Core’s built‑in logging to confirm identical SQL is produced.

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseSqlServer(connectionString)
    .UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()))
    .Options;

var stopwatch = new Stopwatch();

// Warm‑up
await OrderRepository.GetByStatusAsync(new AppDbContext(options), OrderStatus.Pending);

stopwatch.Start();
for (int i = 0; i < 1000; i++)
    await OrderRepository.GetByStatusAsync(new AppDbContext(options), OrderStatus.Pending);
stopwatch.Stop();
Console.WriteLine($"Compiled query time: {stopwatch.ElapsedMilliseconds} ms");

// Repeat the same loop without using the compiled query – build the query each time
stopwatch.Restart();
for (int i = 0; i < 1000; i++)
{
    using var ctx = new AppDbContext(options);
    var list = await ctx.Orders
        .Where(o => o.Status == OrderStatus.Pending)
        .OrderByDescending(o => o.CreatedAt)
        .AsNoTracking()
        .ToListAsync();
}
stopwatch.Stop();
Console.WriteLine($"Non‑compiled query time: {stopwatch.ElapsedMilliseconds} ms");

Typical results on a medium‑sized dataset show a 30‑40% reduction in total execution time after the first run of the compiled query. The SQL printed by the logger is identical for both approaches, confirming that the compiled query is simply re‑using the same translation.

Trade‑offs and Caveats

• Memory Footprint – Every compiled query adds a delegate to EF’s cache. In memory‑constrained environments or when you compile thousands of different queries, this can become noticeable.
• Cache Staleness – Compiled queries are tied to the DbContextOptions instance. If you change the model (e.g., add a new column) without restarting the application, the cache may serve stale translations until the next app restart.
• Complex or Dynamic Queries – If your query shape changes (different columns, joins, or filters) you cannot reuse the same compiled delegate. In such cases, the overhead may outweigh the benefit.
• Startup Latency – The first execution of a compiled query still incurs the translation cost. If the query is rarely executed, the benefit may be negligible.
• Debugging Complexity – When a compiled query throws a translation error, the stack trace points to the compiled delegate rather than the original LINQ expression, making debugging slightly harder.

Practical Checklist Before You Roll Out

1. Identify queries that run >100 times per minute and are parameterised but static.
2. Measure baseline performance with EF Core logging or a profiler.
3. Implement the compiled query as shown, ensuring no client‑side evaluation.
4. Re‑measure and confirm a 30‑50% reduction in translation overhead.
5. Monitor memory usage; if the cache grows beyond a few megabytes, consider limiting the number of compiled queries.
6. Document the compiled queries in your codebase so future changes to the query shape are caught early.

Conclusion

Compiled queries are a powerful tool in EF Core’s performance arsenal. They eliminate the repeated cost of translating the same LINQ expression into SQL, giving a clean 30‑50% speed boost for hot, repetitive queries. Use them when the query shape is stable, the operation is frequent, and you can tolerate a modest increase in memory usage. For ad‑hoc or highly dynamic queries, stick with the standard LINQ path to keep your code simple and your memory footprint low.