EF Core Compiled Query — Complete Guide

In this tutorial, you'll learn about EF Core Compiled Query. We cover key concepts, practical examples, and best practices.

Every time EF Core executes a LINQ query, it compiles the expression tree into SQL. For queries executed hundreds of times per second, this compilation overhead adds up. Compiled queries cache the translation so EF Core skips the compilation step on subsequent executions.

The Problem

public async Task<Order?> GetOrderByIdAsync(int id)
{
    return await db.Orders
        .Include(o => o.Items)
        .FirstOrDefaultAsync(o => o.Id == id);
    // Compiles expression tree to SQL every time
}

Output: Works. For 1000 requests/second, the same query is compiled 1000 times/second.

Right

private static readonly Func<AppDbContext, int, Task<Order?>> GetOrderById =
    EF.CompileAsyncQuery((AppDbContext db, int id) =>
        db.Orders.Include(o => o.Items).FirstOrDefault(o => o.Id == id));

public async Task<Order?> GetOrderByIdAsync(int id)
{
    return await GetOrderById(db, id);
}

Output: The query is compiled once. Subsequent executions use the cached SQL.

For synchronous queries:

private static readonly Func<AppDbContext, int, Order?> GetOrderByIdSync =
    EF.CompileQuery((AppDbContext db, int id) =>
        db.Orders.FirstOrDefault(o => o.Id == id));

Compiled queries support multiple parameters and complex expressions:

private static readonly Func<AppDbContext, string, decimal, Task<List<Order>>> GetOrders =
    EF.CompileAsyncQuery((AppDbContext db, string status, decimal minAmount) =>
        db.Orders.Where(o => o.Status == status && o.Amount > minAmount)
                 .OrderBy(o => o.Date)
                 .ToList());

Prevention

• Use compiled queries for hot-path queries executed frequently with different parameters.
• Use compiled queries with EF.CompileQuery (sync) or EF.CompileAsyncQuery (async).
• Use static readonly fields for compiled query delegates.
• Limit compiled queries to a handful of hot paths — do not compile every query.
• Include all necessary Include calls in the compiled expression.
• Be aware that compiled queries cannot adapt to model changes at runtime.

Common Mistakes with core compiled query

1. Non-exhaustive pattern matches that compile with warnings then crash at runtime
2. Misunderstanding that String is [Char] with poor performance for large text operations
3. Using foldl instead of foldl' causing stack overflow on large lists

These mistakes appear frequently in real-world EF code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ

Compiled queries optimize hot paths in DodaTech's high-traffic API endpoints. For more EF Core, visit DodaTech.