Compiled Queries for High Throughput

Every time you execute a LINQ query like _context.Users.Where(u => u.Id == 5), EF Core goes through a severe computational cost: it checks its internal query cache, parses the C# Expression Tree, compiles the LINQ into a SQL string, and generates mapping code. For high-frequency API endpoints hit 50,000 times a second, this "Expression Compilation" overhead will max out your server's CPU.

1. EF Core Default Query Caching

Normally, EF Core caches query translations. If you run the exact same LINQ query twice, the second time is faster. However, EF Core still has to traverse the C# Expression Tree mathematically to generate the "cache key" to do the lookup. Compiled Queries allow us to entirely bypass the parsing and cache-lookup phases.

2. Explicitly Compiling the Query

We use EF.CompileAsyncQuery. We define the query exactly ONCE as a static delegate. EF Core translates it to SQL during startup, locking the translation entirely into machine code memory.

public class UserRepository 
{
    // The query is compiled statically ONE TIME.
    // Parameter 1: The DbContext.
    // Parameter 2: The dynamic variable we are filtering by (the Integer ID).
    // Parameter 3: The Task Result.
    private static readonly Func<ApplicationDbContext, int, Task<User?>> _getUserByIdCompiled =
        EF.CompileAsyncQuery((ApplicationDbContext context, int userId) =>
            context.Users.FirstOrDefault(u => u.Id == userId));

    private readonly ApplicationDbContext _context;

    public async Task<User?> GetUserFastAsync(int id)
    {
        // Executes instantly! No compilation, no cache-lookups, 
        // just raw execution of a pre-compiled SQL string.
        return await _getUserByIdCompiled(_context, id);
    }
}

3. When NOT to use Compiled Queries

Compiled Queries are very rigid. You CANNOT dynamically append .Where() or .OrderBy() clauses to a query after it has been explicitly compiled. They should be reserved exclusively for your most frequently accessed, highly rigid scalar queries (like grabbing a User by their ID to authenticate a JWT Token).

4. Interview Mastery

Q: "We implemented a Compiled Query to boost performance. However, when a developer tries to use `.Include(u => u.Orders)` inside the delegate, they are getting an error stating that `.Include` is not supported. Why?"

Architect Answer: "Historically, EF Core explicitly restricted `Include` and `AsSplitQuery` methods from operating inside statically compiled delegates due to immense complexities in how navigation mappings are cached in memory. Microsoft actually resolved this limitation in recent .NET versions (EF Core 6+), allowing Includes inside Compiled Queries. However, if you are attempting to compile massive, multi-faceted queries returning dense relational graphs that shift dynamically, you are fundamentally misunderstanding the use-case of Compiled Queries. Compiled Queries excel at bypassing CPU overhead for tiny, repetitive scalar point-lookups. If a query is large enough to warrant 8 Includes, the SQL Server Engine I/O time will absolutely dwarf the EF Core LINQ Translation time, rendering the C#-side compile optimization statistically meaningless."