Compiled queries for hot paths: best practices explained

Introduction

You've optimized your database indexes, tuned your connection pooling, and profiled your network latency—yet a critical endpoint still struggles under load. The culprit is often hiding in plain sight: Entity Framework Core's query compilation overhead on hot paths. Compiled queries for hot paths: best practices explained takes you beyond EF's built-in caching to eliminate per-call translation work entirely, keeping response times predictable even when a single query runs thousands of times per minute.

EF Core's query plan caching: good, but not enough

Entity Framework Core does cache query plans. When you run the same LINQ query twice, EF doesn't generate fresh SQL for the second execution—it reuses the compiled expression tree. This is smart and saves considerable CPU. However, there's a hidden cost that often goes unnoticed in production.

Every time you call your query, EF Core still performs:

• Parameter binding and validation
• Expression tree traversal and analysis
• Null checking and type resolution
• LINQ-to-SQL translation setup

These steps happen per invocation, even with a cached plan. For a query executed 5,000 times per minute, that's 5,000 sets of redundant work. On high-traffic endpoints—user lookups, session fetches, inventory checks—this overhead compounds quickly and directly impacts tail latency.

Consider a typical user lookup in an e-commerce system:

// Traditional approach: still does per-call overhead
var user = await db.Users
  .FirstOrDefaultAsync(u => u.Id == userId);

Under moderate load (1,000 requests/sec), each small overhead multiplies. The plan is cached, but the cost isn't zero.

EF.CompileAsyncQuery: removing the translation step

EF Core's EF.CompileAsyncQuery (and its synchronous counterpart EF.CompileQuery) bake the entire query compilation into a static delegate at application startup. This delegate is then called directly for each invocation—no re-evaluation, no per-call translation.

Here's the pattern:

public class UserRepository
{
  private static readonly Func<AppDb, int, Task<User?>> ById =
    EF.CompileAsyncQuery((AppDb db, int id) =>
      db.Users.FirstOrDefault(u => u.Id == id));

  public Task<User?> GetUserByIdAsync(AppDb db, int userId)
  {
    return ById(db, userId);
  }
}

What happens here: the lambda expression passed to EF.CompileAsyncQuery is compiled into IL at application startup. The result is a delegate that directly executes the database command. No LINQ-to-SQL translation per call. Parameter binding is optimized. You get raw performance.

The signature is critical to understand:

• First parameter is always the DbContext (or DbSet, in some cases).
• Remaining parameters are your query variables (userId, productId, etc.).
• The return type is what you'd normally get from .FirstOrDefaultAsync()—a Task<T> or Task<IList<T>>.

Here's a more complex example from a ride-hailing platform:

private static readonly Func<RideDb, string, DateTime, Task<List<ActiveRide>>> 
  GetActiveRidesByDriver = EF.CompileAsyncQuery(
    (RideDb db, string driverId, DateTime since) =>
      db.ActiveRides
        .Where(r => r.DriverId == driverId && r.CreatedAt >= since)
        .OrderByDescending(r => r.CreatedAt)
        .ToList());

public Task<List<ActiveRide>> GetDriverRidesAsync(string driverId)
{
  return GetActiveRidesByDriver(db, driverId, DateTime.UtcNow.AddHours(-1));
}

Every driver polling for their active rides uses the same compiled query. No re-compilation. No redundant LINQ overhead. Just direct SQL execution.

Measure first: how to identify true hot paths

Compiled queries are powerful, but they're not a blanket optimization. Premature application is cargo-cult engineering. You must measure.

Start by profiling your application with production-like traffic:

1. Enable EF Core logging to see query frequency and timing in your environment.
2. Use APM tools (Application Insights, Datadog, New Relic) to identify endpoints with high request volume and slow response percentiles (p95, p99).
3. Check if the bottleneck is actually query compilation—not network, not I/O, not CPU in application logic.
4. Verify query frequency—a query that runs 10 times per hour doesn't need compilation; one running 1,000 times per minute does.

A practical example: you notice your payment processing dashboard is slow. APM shows 40% of requests spend time in data access. Logging reveals the same user-by-ID query runs 15 times per request. That's a clear hot path. Compile it.

Conversely, a quarterly report query that runs once per day has no business being compiled—the overhead of maintaining the static delegate isn't worth it.

Best practices for compiled queries

Keep them static and module-scoped. Compiled queries are expensive to create but cheap to invoke. Store them as static readonly fields in your repository class or a dedicated query provider. Never compile in a loop or per request.

Avoid complex projections. Compiled queries work best for simple materialization. If you need complex SelectMany operations or nested includes, the compilation overhead may outweigh the benefit. Stick to straightforward filtering and basic shaping.

Test under load. Benchmark before and after. Use tools like BenchmarkDotNet or load-test with realistic traffic. A 5% improvement is not worth the extra code complexity; aim for 20% or more on proven hot paths.

// Good: simple, focused query
private static readonly Func<ShoppingDb, int, Task<Product?>> ProductById =
  EF.CompileAsyncQuery((ShoppingDb db, int id) =>
    db.Products.FirstOrDefault(p => p.Id == id));

// Avoid: complex logic better handled in application code
private static readonly Func<ShoppingDb, int, Task<object>> ComplexAnalysis =
  EF.CompileAsyncQuery((ShoppingDb db, int userId) =>
    db.Orders
      .Where(o => o.UserId == userId)
      .GroupBy(o => o.ProductCategory)
      .Select(g => new
      {
        Category = g.Key,
        Total = g.Sum(o => o.Amount),
        AvgPrice = g.Average(o => o.UnitPrice),
        Items = g.Select(o => new { o.Id, o.Name }).ToList()
      })
      .ToList());

Document why it's compiled. Future maintainers should understand the intent. A single-line comment prevents someone from "simplifying" the code back to a traditional query.

// Compiled for high-frequency lookups (~5k/min in production).
// Measured 35% latency improvement on user fetch endpoint.
private static readonly Func<AppDb, int, Task<User?>> ById =
  EF.CompileAsyncQuery((AppDb db, int id) =>
    db.Users.FirstOrDefault(u => u.Id == id));

Common mistakes

Compiling queries that aren't hot paths. A query that runs once per hour compiles faster than it executes the first time. You've added complexity with zero benefit. Measure first.

Passing DbContext incorrectly. Compiled queries require the DbContext as the first parameter. Forgetting this, or passing it as a property instead of a parameter, breaks compilation.

Assuming compatibility with all LINQ operators. Compiled queries support a subset of LINQ operations. Skip complex GroupBy patterns, multiple SelectMany chains, or client-side evaluation. Stick to Where, FirstOrDefault, Single, ToList, OrderBy, and basic Select.

Not benchmarking in production conditions. A 10x speed improvement in a unit test with a fast connection may become 1.2x in production under network latency. Always test under realistic load.

Creating a new DbContext inside the compiled query. The DbContext should be injected or passed. If you create a new one per call, you've negated most of the optimization benefit and introduced connection pool pressure.

Key takeaways

• EF Core caches query plans but still performs per-call overhead—parameter binding, expression traversal, and LINQ translation setup add up on hot paths.
• Use EF.CompileAsyncQuery to bake queries into static delegates, eliminating translation overhead for repeated calls.
• Measure with production traffic before optimizing—not every query is worth compiling.
• Keep compiled queries simple, static, and well-documented with a comment explaining why compilation was necessary.
• Compile only queries running hundreds or thousands of times per minute; the complexity cost isn't justified otherwise.
• Benchmark real-world scenarios under load to confirm the improvement justifies the added code complexity.