optimizing-ef-core-queries

SKILL.md

Optimizing EF Core Queries

When to Use

EF Core queries are slow or generating too many SQL statements

Database CPU/IO is high due to ORM inefficiency

N+1 query patterns are detected in logs

Large result sets cause memory pressure

When Not to Use

The user is using Dapper or raw ADO.NET (not EF Core)

The performance issue is database-side (missing indexes, bad schema)

The user is building a new data access layer from scratch

Inputs

Input
Required
Description

Slow EF Core queries
Yes
The LINQ queries or DbContext usage to optimize

SQL output or logs
No
EF Core generated SQL or query execution logs

Workflow

Step 1: Enable query logging to see the actual SQL

// In Program.cs or DbContext configuration:
optionsBuilder
    .UseSqlServer(connectionString)
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging()  // shows parameter values (dev only!)
    .EnableDetailedErrors();

Or use the Microsoft.EntityFrameworkCore log category:

{
  "Logging": {
    "LogLevel": {
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}

Step 2: Fix N+1 query patterns

The #1 EF Core performance killer. Happens when loading related entities in a loop.

Before (N+1 — 1 query for orders + N queries for items):

var orders = await db.Orders.ToListAsync();
foreach (var order in orders)
{
    // Each access triggers a lazy-load query!
    var items = order.Items.Count;
}

After (eager loading — 1 or 2 queries total):

// Option 1: Include (JOIN)
var orders = await db.Orders
    .Include(o => o.Items)
    .ToListAsync();

// Option 2: Split query (separate SQL, avoids cartesian explosion)
var orders = await db.Orders
    .Include(o => o.Items)
    .AsSplitQuery()
    .ToListAsync();

// Option 3: Explicit projection (best - only fetches needed columns)
var orderSummaries = await db.Orders
    .Select(o => new OrderSummary
    {
        OrderId = o.Id,
        Total = o.Items.Sum(i => i.Price),
        ItemCount = o.Items.Count
    })
    .ToListAsync();

When to use Split vs Single query:

Scenario
Use

1 level of Include
Single query (default)

Multiple Includes (Cartesian risk)
AsSplitQuery()

Include with large child collections
AsSplitQuery()

Need transaction consistency
Single query

Step 3: Use NoTracking for read-only queries

Change tracking overhead is significant. Disable it when you don't need to update entities:

// Per-query
var products = await db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

// Global default for read-heavy apps
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

Use AsNoTrackingWithIdentityResolution() when the query returns duplicate entities to avoid duplicated objects in memory.

Step 4: Use compiled queries for hot paths

// Define once as static
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));

// Use repeatedly — skips query compilation overhead
var order = await GetOrderById(db, orderId);

Step 5: Avoid common query traps

Trap
Problem
Fix

ToList() before Where()
Loads entire table into memory
Filter first: .Where().ToList()

Count() to check existence
Scans all rows
Use .Any() instead

.Select() after .Include()
Include is ignored with projection
Remove Include, use Select only

string.Contains() in Where
May not translate, falls to client eval
Use EF.Functions.Like() for SQL LIKE

Calling .ToList() inside Select()
Causes nested queries
Use projection with Select all the way

Step 6: Use raw SQL or FromSql for complex queries

When LINQ can't express it efficiently:

var results = await db.Orders
    .FromSqlInterpolated($@"
        SELECT o.* FROM Orders o
        INNER JOIN (
            SELECT OrderId, SUM(Price) as Total
            FROM OrderItems
            GROUP BY OrderId
            HAVING SUM(Price) > {minTotal}
        ) t ON o.Id = t.OrderId")
    .AsNoTracking()
    .ToListAsync();

Validation

 SQL logging shows expected number of queries (no N+1)

 Read-only queries use AsNoTracking()

 Hot-path queries use compiled queries

 No client-side evaluation warnings in logs

 Include/split strategy matches data shape

Common Pitfalls

Pitfall
Solution

Lazy loading silently creating N+1
Remove Microsoft.EntityFrameworkCore.Proxies or disable lazy loading

Global query filters forgotten in perf analysis
Check HasQueryFilter in model config; use IgnoreQueryFilters() if needed

DbContext kept alive too long
DbContext should be scoped (per-request); don't cache it

Batch updates fetching then saving
EF Core 7+: use ExecuteUpdateAsync / ExecuteDeleteAsync for bulk operations

String interpolation in FromSqlRaw
SQL injection risk — use FromSqlInterpolated (parameterized)