klod68/ef-query-optimization

---

name: ef-query-optimization description: "Use when diagnosing or improving slow EF Core queries, fixing N+1 problems, eliminating cartesian explosions from multi-level Includes, or when API endpoints are loading full entities when only a few columns are needed. Covers DTO projections with Select, AsNoTracking and AsNoTrackingWithIdentityResolution, AsSplitQuery to prevent cartesian explosions, compiled queries for hot paths, query tags for SQL profiler identification, filtered Includes, and slow-query interceptors. Domain: Data Access, Performance. Level: Advanced. Tags: ef-core, performance, query, sql, optimization."

EF Core Query Optimization

Problem

EF Core generates SQL that is often correct but suboptimal: N+1 queries from lazy loading, cartesian explosions from multi-level includes, full-entity materialization when only two columns are needed, and repeated query compilation overhead.

Optimization Techniques

1. Project to DTOs — Never Load Full Entities for Read

// ❌ Loads entire entity + all columns
var orders = await dbContext.Orders.ToListAsync(ct);

// ✅ Project only what's needed
var orderSummaries = await dbContext.Orders
    .Select(o => new OrderSummaryDto
    {
        Id = o.Id,
        Total = o.Total,
        ItemCount = o.Items.Count
    })
    .ToListAsync(ct);

2. AsNoTracking — Always for Read-Only Queries

// ✅ No change tracking overhead
var orders = await dbContext.Orders
    .AsNoTracking()
    .Where(o => o.Status == OrderStatus.Active)
    .ToListAsync(ct);

// ✅ When you need identity resolution without tracking
var orders = await dbContext.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Customer)
    .ToListAsync(ct);

3. Split Queries — Avoid Cartesian Explosions

// ❌ Single query with multiple includes = cartesian product
var orders = await dbContext.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .ToListAsync(ct);  // Items × Payments rows returned

// ✅ Split into separate queries
var orders = await dbContext.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .ToListAsync(ct);  // 3 separate queries, no explosion

4. Compiled Queries — Avoid Repeated Compilation

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

// Usage:
var order = await GetOrderById(dbContext, orderId, ct);

5. Query Tags — Identify Queries in SQL Profiler

var orders = await dbContext.Orders
    .TagWith("GetActiveOrdersForDashboard")
    .AsNoTracking()
    .Where(o => o.Status == OrderStatus.Active)
    .ToListAsync(ct);

// SQL output includes:  -- GetActiveOrdersForDashboard

6. Filtered Includes

// ✅ Only include active items
var orders = await dbContext.Orders
    .Include(o => o.Items.Where(i => !i.IsCancelled))
    .ToListAsync(ct);

7. Slow Query Interceptor

internal sealed class SlowQueryInterceptor : DbCommandInterceptor
{
    private readonly ILogger<SlowQueryInterceptor> _logger;
    private readonly TimeSpan _threshold = TimeSpan.FromMilliseconds(500);

    public SlowQueryInterceptor(ILogger<SlowQueryInterceptor> logger)
    {
        _logger = logger;
    }

    public override ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand command,
        CommandExecutedEventData eventData,
        DbDataReader result,
        CancellationToken ct = default)
    {
        if (eventData.Duration > _threshold)
        {
            _logger.LogWarning("Slow query ({Duration}ms): {Sql}",
                eventData.Duration.TotalMilliseconds,
                command.CommandText);
        }

        return ValueTask.FromResult(result);
    }
}

Anti-Pattern Detection Checklist

| Symptom | Cause | Fix | |---------|-------|-----| | N+1 queries in loop | Lazy loading or missing Include | Add Include or project with Select | | Huge result set | Missing Where or Take | Add filters and pagination | | Cartesian explosion | Multiple Include on collections | Use AsSplitQuery() | | Full entity in API response | No projection | Use Select to DTO | | Repeated query compilation | Hot path without compiled query | Use EF.CompileAsyncQuery | | Unidentifiable slow query | No query tag | Add TagWith("description") | | Tracking overhead on reads | Missing AsNoTracking | Add AsNoTracking() to all read queries |

When to Use

• Performance profiling reveals slow database queries
• API endpoints that load collections with includes
• Background workers processing large datasets
• Any query appearing in slow-query logs

When NOT to Use

• Micro-benchmarking queries that run once at startup — optimize hot paths first
• Premature optimization before profiling confirms a database bottleneck
• Dapper or raw ADO.NET queries — these techniques are EF Core specific
• Read models served by a denormalized view or materialized projection

Gotchas

1. AsSplitQuery trade-off: Splits avoid cartesian explosions but create multiple round-trips. Benchmark both for your data shape.
2. Compiled queries limitations: Cannot use Include with navigation property filters. Cannot use interpolated FormattableString parameters.
3. AsNoTrackingWithIdentityResolution: Slower than AsNoTracking but ensures the same entity instance is reused across navigations — use when the query joins the same entity multiple times.
4. ToQueryString(): Use in development to see the generated SQL — never in production code.

Related Skills

• Debug EF Query (diagnostic techniques)
• Bulk Operation Batching (for write optimization)
• Pagination (combined with query optimization)
• Index Design Strategy (SQL-level index design for the queries EF generates)
• SQL Coding Standards (T-SQL patterns when using stored procedures instead of EF)