The N+1 Query Problem in EF Core (And How to Spot It Before Production Does)

#efcore #dotnet #csharp #performance

A page that felt fine in testing is slow in production. Same code, same query, but now it crawls. You open the SQL log and there it is — not one query, but two hundred. One to load the list, then one more for every single row in it. That's the N+1 query problem, and it's one of the most common performance bugs in EF Core.

What is the N+1 query problem? It's when your code runs one query to get a list, then one extra query per item in that list to load something related. Ten items, eleven queries. A thousand items, a thousand and one. The database was never the problem. The number of trips to it was.

How It Sneaks In
The code that causes it looks completely normal.

var orders = await _db.Orders.ToListAsync();
foreach (var order in orders)
{
  Console.WriteLine(order.Customer.Name);
}

One query to load the orders. Looks fine. But order.Customer wasn't loaded with the orders, so the first time you touch it, EF Core quietly goes back to the database and fetches that customer. Once per order, inside the loop. Load 200 orders, and you've just fired 201 queries without writing a single extra line that looks like a query.

This is lazy loading doing exactly what it was told. Every time you reach for a related thing that isn't already loaded, EF Core fetches it on the spot. In a loop, that turns into hundreds of trips.

Why It Hides Until Production
The reason this one bites so often is that it passes every check on the way in.

With ten test rows, eleven queries run in a few milliseconds and nobody notices. The code reads cleanly. It reviews cleanly. The page loads fine on your machine. Then real data shows up — a customer with two thousand orders instead of ten — and the same code fires two thousand queries for one page load. Nothing changed in the code. The data got bigger, and the hidden cost got bigger with it.

How to Fix It
The fix is to tell EF Core up front what related data you need, so it loads everything in one trip instead of many.

Use Include to load the related data with the main query:

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

Now the customer comes back with the order in the same round trip. The loop touches order.Customer and it's already there — no extra queries. One query instead of 201.

Or project only what you need:

var orders = await _db.Orders
.Select(o => new {
o.Id,
CustomerName = o.Customer.Name
})
.ToListAsync();
If you only need the customer's name, don't load the whole customer. Pull just the fields you'll use into a shape you control. One query, and less data over the wire on top of it.

Both fix the N+1. Include when you need the full related object, projection when you only need a few fields.

How to Actually Spot It
You can't fix what you can't see, and N+1 is invisible in the C# — it only shows up in the SQL. So make the SQL visible.

Log the queries EF Core runs. Turn on EF Core logging in development and watch the output while you click through a page. If one page load produces a wall of near-identical queries that differ only by an ID, that's N+1.

Watch the query count, not just the time. In development a page can feel fast and still be running fifty queries. The count is the warning sign, long before the clock is. If a single request is firing dozens of queries, something is looping over the database.

Test with realistic data. Most N+1 bugs get through because they're tested against a handful of rows. Seed a dev database with volumes closer to production — thousands of rows, customers with long histories — and the slow pages show themselves before your users find them.

Our Take
At Qodors, when a .NET app is slow for no obvious reason, N+1 is one of the first things we look for, and it's there more often than not. It's rarely a crash or an error. It's a page that got slower as the app got more real, running code that looks perfectly normal.

It ties into something we've written about before. Pull the whole table and filter in memory instead of in the database, and you get a related problem — too much work happening in the app instead of the database. N+1 is the opposite: too many small trips instead of one good one. Both come down to the same habit, letting the code decide how to talk to the database instead of deciding it on purpose.

The fix isn't clever. Load what you need, when you ask for the list, in one query. Then look at your SQL logs often enough that the next N+1 shows up in development, not in a support ticket.

Quick Checklist
Touching a related property inside a loop is the classic N+1 setup — check for it
Use Include to load related data in the same query when you need the full object
Use Select projection when you only need a few fields
Turn on EF Core query logging in development and watch for walls of near-identical queries
Count queries per page load, not just response time — the count warns you first
Test against production-sized data, not ten rows, or N+1 stays hidden until launch

The N+1 query problem isn't hard to fix. It's hard to see. Make your SQL visible in development, and the bug that used to reach production shows up in development instead.