The EF Core N+1 Problem: How to Spot It and Fix It

Picture an order list page. The last 100 orders, each row showing the customer's name and the order total. The code is clean and readable, the tests pass. But the page is slower than you expect. When you look at the SQL log, you see hundreds of queries instead of one.

This is called the N+1 problem. You fetch N records with one query, then run another query for each record. N+1 queries in total.

In this article I explain how N+1 happens, how to spot it and how to fix it, with my own measurements.

How does N+1 happen?

There are two ways. The first is lazy loading. In EF Core lazy loading is off by default. To turn it on you add the Microsoft.EntityFrameworkCore.Proxies package, call UseLazyLoadingProxies() and make navigation properties virtual. Once it's on, this code looks harmless:

var orders = await db.Orders
    .OrderByDescending(o => o.CreatedAt)
    .Take(100)
    .ToListAsync();

foreach (var o in orders)
{
    var name = o.Customer.Name;                       // a query per order
    var total = o.Lines.Sum(l => l.Price * l.Quantity); // another query per order
}

The moment you first touch o.Customer, EF Core goes to the database to fetch the customer. Touching o.Lines fetches the order lines. The loop runs 100 times, so these queries run 100 times too.

The second way shows up without lazy loading: writing a query inside a loop.

foreach (var o in orders)
{
    var lines = await db.OrderLines
        .Where(l => l.OrderId == o.Id)
        .ToListAsync();
}

This is a more obvious mistake, but it's easy to miss when it's hidden inside a helper method.

What does it look like in the log?

LogTo is enough to see the SQL EF Core runs:

optionsBuilder
    .UseNpgsql(connectionString)
    .LogTo(Console.WriteLine, [RelationalEventId.CommandExecuted]);

In the lazy loading example the log starts like this:

Executed DbCommand (0ms)
SELECT o."Id", o."CreatedAt", o."CustomerId"
FROM "Orders" AS o
ORDER BY o."CreatedAt" DESC
LIMIT @p

Executed DbCommand (1ms)
SELECT c."Id", c."Name"
FROM "Customers" AS c
WHERE c."Id" = @p
LIMIT 1

Executed DbCommand (0ms)
SELECT o."Id", o."OrderId", o."Price", o."ProductName", o."Quantity"
FROM "OrderLines" AS o
WHERE o."OrderId" = @p
...

When you see the same query running again and again with only its parameter changing, you've found N+1. Each query takes 0–1 ms. That's exactly the problem: none of them is slow on its own. What's slow is the sum.

Counting queries

Counting queries is more reliable than scanning the log by eye. EF Core's interceptors are a good fit:

public class CommandCounter : DbCommandInterceptor
{
    public int Count;

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result, CancellationToken ct = default)
    {
        Count++;
        return base.ReaderExecutingAsync(command, eventData, result, ct);
    }
}

I add it to the context with AddInterceptors(counter) and check counter.Count after an operation. Put this in an integration test and you can write a rule like "this page must run at most 3 queries". If N+1 ever comes back, the test fails.

I measured it

I wrote the same list six different ways and measured each: the last 100 orders, the customer name and the order lines. The database has 50 customers, 500 orders, 5 lines and 2 payments per order. I warmed each method up 5 times, then ran it 31 times and took the median.

Environment: Intel Core i7-13700H, 32 GB RAM, .NET 10, EF Core 10, Npgsql 10, PostgreSQL 16 in Docker on the same machine.

A few things caught my eye.

Why 147 queries with lazy loading? One order query and 100 line queries, plus the customers. The 100 orders had 46 distinct customers. EF Core doesn't query the same customer twice because it's already tracking it. Hence 1 + 100 + 46 = 147.

An 80× difference even with the database on the same machine. Here each round trip takes under a millisecond. In production the database is usually on another server and each round trip takes at least 1 ms. Then 147 queries means at least 147 ms spent just waiting on the network.

Lazy loading is slower than querying in a loop. It runs more queries, and the cost of proxy objects comes on top.

Fixes

Include

Load related data up front in a single query:

var orders = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Lines)
    .OrderByDescending(o => o.CreatedAt)
    .Take(100)
    .ToListAsync();

147 queries became 1, and 114 ms became 2.78 ms. If you're going to read and modify the orders, this is the right way.

Several collections: AsSplitQuery

When I added the payments with Include too, it was still one query, but the time more than doubled. The reason is that both collections are joined in the same query. 100 orders × 5 lines × 2 payments = 1,000 rows come back; without the payments it was 500. This is called cartesian explosion, and EF Core writes a warning to the log when it happens.

AsSplitQuery() fetches each collection with a separate query. In my measurement it took 4.40 ms with 3 queries. It has a cost too: each query is a separate round trip, and if data changes between them you can get inconsistent results. I covered this in detail in EF Core Query Performance.

Projection with Select

If I'm only going to display the data, projection is my first choice:

var rows = await db.Orders
    .OrderByDescending(o => o.CreatedAt)
    .Take(100)
    .Select(o => new
    {
        o.Id,
        Customer = o.Customer.Name,
        LineCount = o.Lines.Count,
        Total = o.Lines.Sum(l => l.Price * l.Quantity)
    })
    .ToListAsync();

I didn't write Include, but because I access o.Customer.Name, EF Core adds the JOIN itself. The database computes the total too. Only the four values I show on screen reach the application. This was the fastest of all: 1.42 ms.

Should I use lazy loading?

I don't. The problem with lazy loading is that you can't tell from the code whether you're hitting the database. o.Customer.Name looks like an ordinary property read. You only see that it runs a query when you look at the log.

With lazy loading off, if you forget to load a relationship, o.Customer is null and the bug shows up immediately. I think that's better: code that blows up during development rather than a page that quietly gets slower.

Which one when?

Conclusion

N+1 is queries that look fast one by one adding up to a slow page. In my measurement the same list went from 147 queries and 114 ms to 1 query and 1.42 ms. The fix isn't hard. Noticing it is. That's why I think the most important step is making queries visible: keep the SQL log on in development and test the query count of critical pages.