The N+1 query problem in EF Core: the most expensive habit in .NET

There's a category of bug that never trips a test, never throws, never shows up in a code review diff as anything alarming, and still manages to take down a page in production. The N+1 query problem is the patron saint of that category. It's the most expensive habit in EF Core precisely because it doesn't look like a mistake. It looks like a foreach.

The shape is always the same. You load a collection, then you touch a related property inside the loop. EF Core dutifully runs one query to get the collection, and then one more query for every single row to fetch the related data. One plus N. With ten orders on your dev machine, that's eleven cheap queries and nobody notices. With ten thousand orders on a Tuesday afternoon, it's ten thousand and one round trips to the database, and the page hangs.

The loop that looks fine

Here's the kind of code that ships. We're building an order summary, and each summary needs the customer's name and the line-item count.

public async Task<List<OrderSummary>> GetOrderSummariesAsync()
{
    var orders = await _db.Orders
        .Where(o => o.Status == OrderStatus.Completed)
        .ToListAsync();

    var summaries = new List<OrderSummary>();
    foreach (var order in orders)
    {
        summaries.Add(new OrderSummary
        {
            OrderId = order.Id,
            // each of these touches a navigation property...
            CustomerName = order.Customer.Name,
            LineItemCount = order.Items.Count
        });
    }

    return summaries;
}

If lazy loading is on, order.Customer.Name quietly issues a query. So does order.Items.Count. For 40 completed orders that's the initial query plus two per order: 81 queries to render one page. Nothing about this code reads as a performance bug. It reads as a foreach over a list, which is the most ordinary thing in the language.

The SQL it actually generates

This is the part you don't see unless you go looking. The first query is fine:

SELECT o.id, o.status, o.customer_id, o.created_at
FROM orders AS o
WHERE o.status = 2;

Then, for every row that comes back, EF Core fires these:

-- repeated once per order
SELECT c.id, c.name, c.email
FROM customers AS c
WHERE c.id = @__p_0;

-- repeated once per order
SELECT count(*)
FROM order_items AS i
WHERE i.order_id = @__p_1;

The parameter changes each time. The plan is the same. The database is now doing the join you should have asked for once, except it's doing it in a slow drip controlled by your application's round-trip latency. At 1 ms per round trip, 81 queries is roughly 80 ms of pure waiting before any work happens. At 5 ms (a hop across an availability zone), it's 400 ms, and that's the whole page budget gone on nothing.

Here is the same pattern as Katabench's database grader draws it, for a lighter page with one navigation per order instead of our two: 41 round-trips where one belongs.

The N+1 starter

… ×41 round-trips

✗ Timeout over budget

The set-based rewrite

1 round-trip

✓ Passed 12 ms

Why every test passed

This is the cruel part, and it's worth saying plainly: N+1 passes your unit tests because your unit tests have three rows in them. Your test fixture seeds two customers and a handful of orders. One plus N where N is 4 is five queries, and five fast queries against an in-memory or local database return in single-digit milliseconds. The assertion checks the output is correct, and it is. Correctness was never the problem. The query count was, and nothing in the test looks at the query count.

The test measures the answer. Production measures the answer and how many round trips it cost to get there. If your feedback loop only checks the first one, you'll only ever fix the first one.

The fix: ask for everything once

The repair is to tell EF Core up front what related data you need, so it composes a single query instead of dribbling out N of them. There are two clean ways.

Eager load with Include when you need the full entities:

var orders = await _db.Orders
    .Where(o => o.Status == OrderStatus.Completed)
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .ToListAsync();

Project to exactly what you need, which is usually better, because you only pull the columns the page uses and you skip materializing entities you'll throw away:

public async Task<List<OrderSummary>> GetOrderSummariesAsync()
{
    return await _db.Orders
        .Where(o => o.Status == OrderStatus.Completed)
        .Select(o => new OrderSummary
        {
            OrderId = o.Id,
            CustomerName = o.Customer.Name,
            LineItemCount = o.Items.Count
        })
        .ToListAsync();
}

That projection translates to one round trip with a join and a correlated count, computed in the database where the data already lives:

SELECT o.id AS order_id,
       c.name AS customer_name,
       (SELECT count(*)
        FROM order_items AS i
        WHERE i.order_id = o.id) AS line_item_count
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.id
WHERE o.status = 2;

81 queries to 1. Same output, same correctness, and the page time stops scaling with the number of orders. The work moved from your application's round-trip loop into the query planner, which is exactly where it belongs.

A word of caution on the other direction. Stacking many Include calls onto one-to-many navigations can produce a cartesian explosion, where the row count multiplies and you ship far more data than you need. EF Core has AsSplitQuery() for that, and projecting only the fields you use sidesteps it entirely. The instinct to build is this: know what one query you want, then write the LINQ that produces it, not the LINQ that reads nicely and produces forty.

How you actually learn to see this

The reason N+1 is so persistent isn't that it's hard to fix. The fix is a one-line Include or a Select. It's that it's invisible at the moment you write it. You can't fix a query count you can't see, and the C# gives you no hint. order.Customer.Name looks identical whether it's a field access or a database round trip.

So the skill to build is the ability to look at LINQ and see the SQL behind it. That's a muscle, and you build muscle with reps against real feedback. Katabench's database track runs your LINQ against a real PostgreSQL database and shows you every SQL query your code generates, so an N+1 isn't a warning in a blog post. It's 81 queries on screen where one belongs, next to your green-looking output. You write the projection, the count drops to 1, and you feel the difference.

After enough of those reps, you stop writing N+1 in the first place, because you've trained yourself to ask "what query is this?" before you commit. If that's the instinct you want, the grading model is built around showing you exactly what production would, and you can see the full setup on the pricing page. The cheapest N+1 is the one you never shipped.