The EF Core N+1 problem: 51 queries from one loop

This report prints every customer in a shop, how many orders they placed and what those orders add up to:

foreach (var customer in db.Customers.ToList())
{
    var total = customer.Orders.Sum(o => o.Total);
    lines.Add($"{customer.Name}: {customer.Orders.Count} zamówienia, razem {total} zł");
}

The database holds 50 customers with three orders each. It compiles, it doesn’t throw, and the first line of output is exactly right:

Klient 1: 3 zamówienia, razem 36.0 zł

(The code and data come from a Polish demo shop: “Klient” is a customer, “zamówienia” are orders, “razem” is the total.) A test comparing these lines to the expected ones passes too. The output isn’t the problem.

The number of times the program asked the database for it is. That number is 51. This is the EF Core N+1 problem, and what makes it nasty is that no line of the report looks like it sends a query at all.

What EF Core actually sent

The model is ordinary. Customer has an Orders collection, and the context turns on lazy loading through Microsoft.EntityFrameworkCore.Proxies:

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public virtual List<Order> Orders { get; set; } = new();
}

protected override void OnConfiguring(DbContextOptionsBuilder options)
{
    options.UseSqlite("Data Source=shop.db").UseLazyLoadingProxies();
}

virtual on Orders plus UseLazyLoadingProxies() makes EF Core generate a subclass of Customer at runtime. Its Orders property queries the database the first time anything reads it.

To see what reached SQLite, I added a DbCommandInterceptor that counts executed commands and turned on LogTo for RelationalEventId.CommandExecuted. The first three commands the report ran:

-- query 1
SELECT "c"."Id", "c"."Name"
FROM "Customers" AS "c"

-- query 2
SELECT "o"."Id", "o"."CustomerId", "o"."Total"
FROM "Orders" AS "o"
WHERE "o"."CustomerId" = @p

-- query 3
SELECT "o"."Id", "o"."CustomerId", "o"."Total"
FROM "Orders" AS "o"
WHERE "o"."CustomerId" = @p

When the report finished, the counter read 51.

One query fetches the customers. That’s the “1”. Then the loop reaches customer.Orders, and instead of handing back an empty list the proxy sends a separate SELECT for that one customer’s orders. Fifty times, because there are fifty customers. That’s the “N”.

Nothing in the report reads like a query. customer.Orders.Sum(...) looks like an in-memory list operation, and once the proxy has loaded the collection, it is one. The query hides inside a property getter.

Here’s why the EF Core N+1 problem survives development. The query count grows with the data, and a dev database rarely has much. I ran the same report against three database sizes, one warm-up pass and twenty measured runs each, and took the median:

Customers	Queries	Without the fix	With Include
5	6	2.0 ms	0.9 ms
50	51	16–19 ms	about 8 ms
500	501	170–200 ms	20–32 ms

Setup: SQLite in a file on the same machine, EF Core 10, .NET SDK 10.0.401. A range is the median across several series, which disagreed with each other more than I’d like; the five-customer row is a single series. The spread doesn’t change the picture, because the gap between columns is many times wider than the gap between runs.

At five customers the difference is a fraction of a millisecond and nobody clicking around the app will notice. At five hundred, the fixed report is six to eight times faster. The code didn’t change. Only the row count in Customers did, which is why this tends to show up after the deploy, when the first large customer imports their data.

And this is SQLite, running inside the app’s own process, so every query skips the network. Against a database server each extra query is a network round trip, which makes the table above the gentlest case there is. I didn’t measure a remote PostgreSQL or SQL Server, so I won’t quote numbers for one, but the direction isn’t in doubt.

Include: one query instead of 51

The shortest fix is telling EF Core up front that the orders will be needed:

foreach (var customer in db.Customers.Include(c => c.Orders).ToList())
{
    var total = customer.Orders.Sum(o => o.Total);
    lines.Add($"{customer.Name}: {customer.Orders.Count} zamówienia, razem {total} zł");
}

One line changed. The log now shows a single command:

SELECT "c"."Id", "c"."Name", "o"."Id", "o"."CustomerId", "o"."Total"
FROM "Customers" AS "c"
LEFT JOIN "Orders" AS "o" ON "c"."Id" = "o"."CustomerId"
ORDER BY "c"."Id"

Counter: 1. The report output is identical, character for character.

The LEFT JOIN returns 150 rows, one per order, repeating the customer’s columns in each. EF Core folds them back into 50 Customer objects with populated collections. The proxies are still there, but the collection is already loaded, so reading Orders sends nothing.

Repeating one customer’s columns is cheap. Include several collections at once and the JOIN’s row count multiplies, which is what AsSplitQuery() is for. Appended after Include, it gave me exactly two queries: customers, then all their orders through an INNER JOIN. Two isn’t N+1. The count still doesn’t depend on how many customers exist, and that’s the measure that matters. At 500 customers the split version’s median landed between 16 and 19 ms, in the same range as plain Include.

A projection: fetch only what the report prints

The report doesn’t need Order objects. It needs two numbers per customer, so ask for those:

var rows = db.Customers
    .Select(c => new { c.Name, Count = c.Orders.Count, Total = c.Orders.Sum(o => o.Total) })
    .ToList();

EF Core translates the projection into one query and lets the database do the counting and summing:

SELECT "c"."Name", (
    SELECT COUNT(*)
    FROM "Orders" AS "o"
    WHERE "c"."Id" = "o"."CustomerId") AS "Count", (
    SELECT COALESCE(ef_sum("o0"."Total"), '0.0')
    FROM "Orders" AS "o0"
    WHERE "c"."Id" = "o0"."CustomerId") AS "Total"
FROM "Customers" AS "c"

ef_sum is a function the SQLite provider registers for summing decimal, since SQLite has no decimal type of its own.

One query, 50 rows instead of 150, nothing to track. The median stayed under a millisecond in every series at 50 customers and around 2 ms at 500. Same query count as Include, roughly a tenth of the time. Under Include, most of the time doesn’t go to the database at all. It goes to building 200 tracked entities and their proxies, and the projection builds none.

There’s a middle option: Include plus AsNoTracking(). Entities still get built, but the context doesn’t track them. In my runs that cut the median about in half compared with plain Include: from about 8 to a little over 3 ms at 50 customers, and from 32 to 16 ms at 500 in the same series.

Which one to pick depends on what happens next. If the code modifies orders and saves them with SaveChanges, you need tracked entities and Include is right. If it only reads them to display something, the projection wins.

The same problem in an API endpoint

A foreach is easy to spot in a code review. It’s harder when you don’t write the loop at all. An endpoint that returns a list of entities hands it to the JSON serializer, and the serializer walks every property of every object, Orders included.

I checked that with the same proxy-enabled context: customers loaded without Include, passed to JsonSerializer.Serialize. With default options it throws:

System.Text.Json.JsonException: A possible object cycle was detected. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 64. Consider using ReferenceHandler.Preserve on JsonSerializerOptions to support cycles. Path: $.Orders.Customer.Orders.Customer.Orders...

Order has a Customer and Customer has Orders, so the serializer bounces between them while the proxies load more on the way. The counter read two queries by the time it threw.

The usual reaction is setting ReferenceHandler.IgnoreCycles. The exception goes away, the response starts with [{"Id":1,"Name":"Klient 1","Orders":[{"Id":1,"Total":11.0,..., and the counter reads 51. Same N+1 as the report, except the loop now lives in System.Text.Json, which you don’t read.

So an endpoint shouldn’t return entities. Return objects built with a Select projection like the one above. They have no navigation properties, so there’s nothing for the serializer to lazy-load.

Turning lazy loading off isn’t a fix

It’s tempting to delete UseLazyLoadingProxies() and call it done. I checked what the original loop does then, without Include. One query, no error, and this output:

Klient 1: 0 zamówienia, razem 0 zł

Zero orders, zero total. Without lazy loading an unloaded collection is empty, and the report happily sums nothing. You trade slow code for fast code that reports wrong numbers, which is a worse deal. The fix is saying which data you need, through Include or a projection.

Catching it before production does

The query counter I used for this post is a few lines and works in a test too. It derives from DbCommandInterceptor, overrides ReaderExecuted and bumps a Count field. The test registers it with AddInterceptors, runs the report and asserts Assert.Equal(1, counter.Count).

That test guards something a first unit test that compares output never would. The 51-query report produces correct output, so an output test passes. The query-count test fails.

The other way needs no code. Turn on LogTo in development and read the log when a page or endpoint feels slow. A run of identical queries that differ only in @p is the signature of N+1. By default EF Core hides parameter values, so each line looks the same:

Executed DbCommand (0ms) [Parameters=[@p='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']

Add EnableSensitiveDataLogging() to the context options and the same log shows @p='1', @p='2' and so on, up to @p='50' in the last query. Then you can count the N straight off the log. Keep that option local, since it writes query data into your logs, and in a real app that data is your customers’.

A debugger won’t show any of this, because every step of the loop looks fine. It only shows up in the log, which makes it a good companion to when a debugger beats print statements.

Every navigation property read inside a loop is a potential query. Inside a loop, read only what you loaded beforehand.

EF Core, loading related data and projections are part of the ASP.NET Core course; the chapters are listed in the curriculum.