Finding the N+1 queries hiding in your EF Core code

Entity Framework Core is excellent. It also quietly writes some of the worst SQL in production, and it does it while your code looks completely innocent.

The classic culprit is the N+1 query: one query to load a list, then N more queries — one per row — to load something related. It never shows up in development against ten rows. It shows up at 3am when the table has half a million.

What it looks like

var orders = await _db.Orders
    .Where(o => o.CreatedAt >= since)
    .ToListAsync();

foreach (var order in orders)
{
    // Each access to .Customer triggers a separate round-trip to the database.
    Console.WriteLine($"{order.Id}: {order.Customer.Name}");
}

Load 500 orders and you've just issued 501 queries: one for the orders, plus one per order to fetch its customer. The C# reads like a simple loop. The database sees a storm.

Prove it before you fix it

Never guess at performance. Make EF Core show you its work by logging the SQL it generates:

options.UseSqlServer(connectionString)
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging(); // dev only — never in production

If you see the same SELECT ... FROM Customers WHERE Id = @p repeated dozens of times with different parameters, that's your N+1. In production, the same signal shows up as a suspiciously high query count per request in your telemetry.

Fix 1 — load what you need up front

If you genuinely need the related data, tell EF Core to fetch it in the same trip with Include:

var orders = await _db.Orders
    .Where(o => o.CreatedAt >= since)
    .Include(o => o.Customer)
    .ToListAsync();

One query. Be aware that multiple Includes with collections can produce a large cartesian join — that's what AsSplitQuery() is for:

var orders = await _db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Lines)
    .AsSplitQuery()   // one query per Include instead of one giant join
    .ToListAsync();

Fix 2 — project to exactly what you need

Most of the time you don't want the whole entity graph — you want three fields for a screen. Projecting with Select is almost always the fastest option, because the database returns only those columns and EF Core tracks nothing:

var rows = await _db.Orders
    .Where(o => o.CreatedAt >= since)
    .Select(o => new OrderRow(o.Id, o.Customer.Name, o.Total))
    .ToListAsync();

This is the version I reach for by default for read paths. If you're rendering data, not mutating it, you rarely need tracked entities at all.

Fix 3 — turn off tracking on read paths

When you do load full entities just to read them, tell EF Core to skip change tracking. It saves memory and CPU on every read:

var orders = await _db.Orders
    .AsNoTracking()
    .Include(o => o.Customer)
    .ToListAsync();

You can make this the default for a context and opt into tracking only where you actually write.

The rule of thumb

Loading data is easy. Loading the right data in the right number of round-trips is the job.

Treat query count as a first-class metric. The difference between 1 query and 501 is invisible in your IDE and very visible to your customers — and an ORM will happily let you ship either one.