optimizing-ef-core-queries

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();