Reducing EF Core Latency with Compiled Queries

In high-throughput .NET applications, the performance bottleneck isn't always the database engine; often, it is the CPU overhead spent translating LINQ expressions into SQL. Every time a standard LINQ query is executed, Entity Framework Core must parse the expression tree and map it to the model to generate the final SQL string. For simple queries executed thousands of times per second, this translation phase introduces measurable latency.

Compiled queries allow you to perform this translation exactly once. By pre-compiling the query into a delegate, you bypass the LINQ provider's translation overhead on subsequent calls, which can reduce execution time by 20% to 40% for frequent, simple projections.

The Translation Overhead Problem

When you run a query like context.Blogs.Where(b => b.Id == id).First(), EF Core checks its internal query cache. While the cache helps, the process of evaluating the expression tree and performing the lookup still consumes CPU cycles. In a microservices environment where response times are measured in milliseconds, these microseconds add up, limiting the vertical scalability of the application server.

Implementing EF.CompiledQuery

To eliminate this overhead, use the EF.CompiledQuery.Compile method. This creates a delegate that accepts your DbContext and any required parameters. Because the compilation is expensive, the resulting delegate must be stored in a static field or a singleton service to be reused across the application lifetime.

The following example demonstrates how to implement a compiled query to fetch blog names based on a rating threshold:

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.Linq;

public static class BlogQueries
{
    // The delegate is compiled once and stored statically
    private static readonly Func<MyDbContext, int, IEnumerable<string>> _getBlogsByRating = 
        EF.CompiledQuery.Compile((MyDbContext context, int minRating) => 
            context.Blogs
                .Where(b => b.Rating > minRating)
                .Select(b => b.Name));

    public static IEnumerable<string> GetBlogsByRating(MyDbContext context, int minRating)
    {
        // Execute the pre-compiled delegate instead of a LINQ expression
        return _getBlogsByRating(context, minRating);
    }
}

To execute this, call BlogQueries.GetBlogsByRating(context, 3). The framework skips the translation phase and sends the pre-generated SQL directly to the provider.

Engineering Trade-offs

Compiled queries are a specialized tool and should not be applied to every query in a project. Consider these limitations:

• Immutability: Once compiled, the delegate is fixed. If the underlying entity model or DbContext options change, the delegate must be recompiled.
• Translation Constraints: Logic that requires client-side evaluation (C# methods that cannot be converted to SQL) will fail or behave unexpectedly because the translation happens upfront.
• Memory Pressure: Each compiled query consumes memory within the application domain. Over-using this for ad-hoc or highly variable queries increases memory usage without providing a meaningful performance gain.

Verification and Decision Path

To determine if a query is a candidate for compilation, identify "hot paths"—queries that are stable in structure and executed frequently. You can verify the impact by wrapping a standard LINQ query and a compiled delegate in a loop of 100,000 executions using a Stopwatch.

If the execution time reduction is less than 10%, the standard EF Core query cache is likely sufficient, and the added code complexity of compiled queries is not justified. Only implement this pattern when the translation overhead is a proven bottleneck in your profiling data.