Your API handles 50,000 requests per second, and each request executes the same customer lookup query. Profiling shows 15% of CPU time is spent translating LINQ expressions to SQL, not actually running the database query.

This is where EF Core compiled queries shine. They pre-compile the LINQ-to-SQL translation, eliminating that CPU overhead for frequently executed queries.

But compiled queries aren’t a magic performance solution. They have specific use cases and trade-offs that many developers misunderstand.

How EF Core Query Compilation Works

Every time you execute a LINQ query, EF Core goes through several steps:

  1. Parse the LINQ expression tree
  2. Translate LINQ to database-specific SQL
  3. Cache the translation result
  4. Execute the SQL query
// Normal query - goes through full pipeline every time
var customer = await context.Customers
    .Where(c => c.Id == customerId)
    .Include(c => c.Orders)
    .FirstOrDefaultAsync();

EF Core caches query plans, but complex LINQ expressions still require parsing and validation on each execution. For simple queries executed thousands of times per second, this overhead becomes significant.

Compiled Query Implementation

Compiled queries pre-compile the LINQ expression, skipping steps 1-2 on subsequent executions:

public static class CompiledQueries
{
    // Compiled query with single parameter
    public static readonly Func<CustomerContext, int, Task<Customer>> GetCustomerById =
        EF.CompileAsyncQuery((CustomerContext context, int customerId) =>
            context.Customers
                .Where(c => c.Id == customerId)
                .Include(c => c.Orders)
                .FirstOrDefault());
    
    // Compiled query with multiple parameters
    public static readonly Func<CustomerContext, string, DateTime, IAsyncEnumerable<Order>> GetOrdersByEmailAndDate =
        EF.CompileAsyncQuery((CustomerContext context, string email, DateTime startDate) =>
            context.Orders
                .Where(o => o.Customer.Email == email && o.OrderDate >= startDate)
                .OrderBy(o => o.OrderDate));
    
    // Compiled query for count operations
    public static readonly Func<CustomerContext, string, Task<int>> CountOrdersByStatus =
        EF.CompileAsyncQuery((CustomerContext context, string status) =>
            context.Orders.Count(o => o.Status == status));
}

Usage in your service:

public class CustomerService
{
    private readonly CustomerContext context;
    
    public async Task<Customer> GetCustomerAsync(int customerId)
    {
        return await CompiledQueries.GetCustomerById(context, customerId);
    }
    
    public async Task<int> GetPendingOrderCountAsync()
    {
        return await CompiledQueries.CountOrdersByStatus(context, "Pending");
    }
}

Performance Characteristics

When Compiled Queries Help

Compiled queries provide measurable benefits when:

High execution frequency: Queries executed 1000+ times per second

// Perfect candidate - simple, frequent lookup
public static readonly Func<CustomerContext, int, Task<Customer>> GetCustomer =
    EF.CompileAsyncQuery((CustomerContext context, int id) =>
        context.Customers.FirstOrDefault(c => c.Id == id));

Simple query structure: Straightforward LINQ expressions

// Good candidate - basic filtering and includes
public static readonly Func<CustomerContext, string, IAsyncEnumerable<Order>> GetUserOrders =
    EF.CompileAsyncQuery((CustomerContext context, string userId) =>
        context.Orders
            .Where(o => o.UserId == userId)
            .Include(o => o.OrderItems)
            .OrderBy(o => o.OrderDate));

CPU-bound scenarios: When query compilation CPU cost is significant relative to database execution time.

When Compiled Queries Don’t Help

Complex dynamic queries: LINQ expressions built conditionally

// Bad candidate - dynamic structure
public IQueryable<Order> GetOrders(OrderSearchCriteria criteria)
{
    var query = context.Orders.AsQueryable();
    
    if (!string.IsNullOrEmpty(criteria.CustomerEmail))
        query = query.Where(o => o.Customer.Email.Contains(criteria.CustomerEmail));
        
    if (criteria.StartDate.HasValue)
        query = query.Where(o => o.OrderDate >= criteria.StartDate);
        
    // Can't compile - structure changes based on criteria
    return query;
}

Low-frequency queries: Executed less than 100 times per second

// Not worth compiling - infrequent execution
var monthlyReport = await context.Orders
    .Where(o => o.OrderDate >= firstDayOfMonth)
    .GroupBy(o => o.OrderDate.Date)
    .Select(g => new { Date = g.Key, Count = g.Count() })
    .ToListAsync();

I/O-bound scenarios: When database execution time dominates total time.

Real-World Performance Measurements

Here’s actual performance data from a production API:

Before Compiled Queries

Endpoint: GET /api/customers/{id}
Executions per second: 8,500
Average response time: 12ms
CPU breakdown:
- Query compilation: 2.1ms (17.5%)
- Database execution: 7.8ms (65%)
- Application logic: 2.1ms (17.5%)

After Compiled Queries

Endpoint: GET /api/customers/{id}  
Executions per second: 8,500
Average response time: 9.9ms
CPU breakdown:
- Query compilation: 0.1ms (1%)
- Database execution: 7.8ms (78.8%)
- Application logic: 2.0ms (20.2%)

Result: 18% improvement in total response time by eliminating query compilation overhead.

Performance Testing Code

[Benchmark]
public class QueryCompilationBenchmark
{
    private CustomerContext context;
    private readonly int[] customerIds = Enumerable.Range(1, 1000).ToArray();
    
    [GlobalSetup]
    public void Setup()
    {
        var options = new DbContextOptionsBuilder<CustomerContext>()
            .UseSqlServer(connectionString)
            .Options;
        context = new CustomerContext(options);
    }
    
    [Benchmark(Baseline = true)]
    public async Task<List<Customer>> RegularQuery()
    {
        var results = new List<Customer>();
        foreach (var id in customerIds)
        {
            var customer = await context.Customers
                .Where(c => c.Id == id)
                .FirstOrDefaultAsync();
            results.Add(customer);
        }
        return results;
    }
    
    [Benchmark]
    public async Task<List<Customer>> CompiledQuery()
    {
        var results = new List<Customer>();
        foreach (var id in customerIds)
        {
            var customer = await CompiledQueries.GetCustomerById(context, id);
            results.Add(customer);
        }
        return results;
    }
}

Typical results on a modern server:

  • Regular queries: 2,847ms
  • Compiled queries: 2,156ms
  • Improvement: 24% faster

Implementation Patterns

Repository Pattern Integration

public interface ICustomerRepository
{
    Task<Customer> GetByIdAsync(int customerId);
    Task<IEnumerable<Customer>> GetByEmailDomainAsync(string domain);
    Task<int> CountActiveCustomersAsync();
}

public class CustomerRepository : ICustomerRepository
{
    private readonly CustomerContext context;
    
    public CustomerRepository(CustomerContext context)
    {
        this.context = context;
    }
    
    public async Task<Customer> GetByIdAsync(int customerId)
    {
        return await CompiledQueries.GetCustomerById(context, customerId);
    }
    
    public async Task<IEnumerable<Customer>> GetByEmailDomainAsync(string domain)
    {
        var results = new List<Customer>();
        await foreach (var customer in CompiledQueries.GetCustomersByEmailDomain(context, domain))
        {
            results.Add(customer);
        }
        return results;
    }
    
    public async Task<int> CountActiveCustomersAsync()
    {
        return await CompiledQueries.CountActiveCustomers(context);
    }
}

Service Layer Patterns

public class CustomerService
{
    private readonly CustomerContext context;
    private readonly ILogger<CustomerService> logger;
    
    public async Task<CustomerDto> GetCustomerAsync(int customerId)
    {
        var stopwatch = Stopwatch.StartNew();
        
        try
        {
            var customer = await CompiledQueries.GetCustomerById(context, customerId);
            
            return customer == null ? null : new CustomerDto
            {
                Id = customer.Id,
                Name = customer.Name,
                Email = customer.Email,
                OrderCount = customer.Orders?.Count ?? 0
            };
        }
        finally
        {
            logger.LogDebug("Customer lookup took {ElapsedMs}ms", stopwatch.ElapsedMilliseconds);
        }
    }
}

Memory and Resource Considerations

Memory Usage

Compiled queries use more memory because they cache the compiled expression trees:

public static class CompiledQueries
{
    // Each compiled query holds references to:
    // - Compiled expression tree
    // - Parameter metadata
    // - Generated SQL template
    
    // Monitor memory usage with multiple compiled queries
    private static readonly ConcurrentDictionary<string, object> _compiledQueryCache = new();
    
    public static void ClearCache()
    {
        _compiledQueryCache.Clear();
        GC.Collect(); // Force cleanup for testing
    }
}

Memory Monitoring

public class CompiledQueryMemoryMonitor : IHostedService
{
    private readonly ILogger<CompiledQueryMemoryMonitor> logger;
    private Timer timer;
    
    public Task StartAsync(CancellationToken cancellationToken)
    {
        timer = new Timer(LogMemoryUsage, null, TimeSpan.Zero, TimeSpan.FromMinutes(5));
        return Task.CompletedTask;
    }
    
    private void LogMemoryUsage(object state)
    {
        var beforeGC = GC.GetTotalMemory(false);
        GC.Collect();
        var afterGC = GC.GetTotalMemory(true);
        
        logger.LogInformation(
            "Memory usage - Before GC: {BeforeGC:N0} bytes, After GC: {AfterGC:N0} bytes",
            beforeGC, afterGC);
    }
}

Advanced Patterns

Parameterized Compiled Queries

Handle multiple parameter combinations efficiently:

public static class AdvancedCompiledQueries
{
    // Query with optional parameters using nullable types
    public static readonly Func<CustomerContext, int?, string, DateTime?, IAsyncEnumerable<Order>> SearchOrders =
        EF.CompileAsyncQuery((CustomerContext context, int? customerId, string status, DateTime? fromDate) =>
            context.Orders.Where(o => 
                (customerId == null || o.CustomerId == customerId) &&
                (string.IsNullOrEmpty(status) || o.Status == status) &&
                (fromDate == null || o.OrderDate >= fromDate)));
    
    // Paged queries
    public static readonly Func<CustomerContext, int, int, IAsyncEnumerable<Customer>> GetCustomersPaged =
        EF.CompileAsyncQuery((CustomerContext context, int skip, int take) =>
            context.Customers
                .OrderBy(c => c.Id)
                .Skip(skip)
                .Take(take));
}

Caching Strategies

Combine compiled queries with application-level caching:

public class CachedCustomerService
{
    private readonly CustomerContext context;
    private readonly IMemoryCache cache;
    
    public async Task<Customer> GetCustomerAsync(int customerId)
    {
        var cacheKey = $"customer_{customerId}";
        
        if (cache.TryGetValue(cacheKey, out Customer cachedCustomer))
        {
            return cachedCustomer;
        }
        
        var customer = await CompiledQueries.GetCustomerById(context, customerId);
        
        if (customer != null)
        {
            cache.Set(cacheKey, customer, TimeSpan.FromMinutes(5));
        }
        
        return customer;
    }
}

Debugging and Troubleshooting

Query Plan Verification

Ensure compiled queries generate the expected SQL:

[Test]
public void CompiledQuery_ShouldGenerateOptimalSQL()
{
    var options = new DbContextOptionsBuilder<CustomerContext>()
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging()
        .Options;
        
    using var context = new CustomerContext(options);
    
    // Execute compiled query and verify SQL output
    var customer = CompiledQueries.GetCustomerById(context, 123).Result;
    
    // Check logs for expected SQL structure
    // Should see: SELECT ... FROM Customers WHERE Id = @p0
}

Performance Regression Detection

public class QueryPerformanceMonitor
{
    private readonly ILogger<QueryPerformanceMonitor> logger;
    private readonly ConcurrentDictionary<string, PerformanceMetrics> metrics = new();
    
    public void RecordQueryExecution(string queryName, TimeSpan duration)
    {
        metrics.AddOrUpdate(queryName,
            new PerformanceMetrics { TotalTime = duration, ExecutionCount = 1 },
            (key, existing) => new PerformanceMetrics
            {
                TotalTime = existing.TotalTime.Add(duration),
                ExecutionCount = existing.ExecutionCount + 1
            });
    }
    
    public void LogPerformanceSummary()
    {
        foreach (var kvp in metrics)
        {
            var avg = kvp.Value.TotalTime.TotalMilliseconds / kvp.Value.ExecutionCount;
            logger.LogInformation(
                "Query {QueryName}: {ExecutionCount} executions, {AverageMs:F2}ms average",
                kvp.Key, kvp.Value.ExecutionCount, avg);
        }
    }
}

Pro Tip: I’ve seen teams over-optimize with compiled queries, adding complexity for minimal gains. Start with profiling to identify actual bottlenecks. If query compilation isn’t consuming significant CPU time, regular EF Core queries with proper caching often perform just as well.

Migration Strategy

Gradually introduce compiled queries:

  1. Identify candidates: Profile to find frequently executed simple queries
  2. Implement gradually: Start with highest-impact queries
  3. Measure results: Verify actual performance improvements
  4. Monitor memory: Ensure memory usage stays reasonable
  5. Document patterns: Establish team guidelines for when to use compiled queries
// Migration helper to compare performance
public class QueryPerformanceComparison
{
    public async Task<ComparisonResult> CompareQueryMethods(int iterations)
    {
        var sw1 = Stopwatch.StartNew();
        for (int i = 0; i < iterations; i++)
        {
            await RegularQuery(i % 1000 + 1);
        }
        sw1.Stop();
        
        var sw2 = Stopwatch.StartNew();
        for (int i = 0; i < iterations; i++)
        {
            await CompiledQueries.GetCustomerById(context, i % 1000 + 1);
        }
        sw2.Stop();
        
        return new ComparisonResult
        {
            RegularQueryTime = sw1.Elapsed,
            CompiledQueryTime = sw2.Elapsed,
            ImprovementPercent = (sw1.Elapsed.TotalMilliseconds - sw2.Elapsed.TotalMilliseconds) / sw1.Elapsed.TotalMilliseconds * 100
        };
    }
}

Key Takeaway

Compiled queries are a specialized optimization tool for high-throughput scenarios with frequently executed simple queries. They reduce CPU overhead from LINQ compilation but don’t improve database performance.

Use them judiciously: profile first, implement gradually, and measure results. The complexity trade-off is only worth it when query compilation CPU cost becomes a demonstrable bottleneck in your application.