The N+1 Query Problem

This is the bug the rest of this module exists to prevent. It is the most common EF Core performance problem by a wide margin, and the reason is not that it is subtle. It is that in C# it looks like nothing at all.

The name comes from the query count. One query to fetch a list of N things, then one more query per thing. Two hundred orders means two hundred and one queries, and each one is a separate round trip across a connection.

A round trip costs a millisecond or two on a good day, more across a network, more again if the database is in another region. Multiply by a few hundred and a page that should take 50ms takes several seconds — and every individual query in the log looks fast.

// Lazy loading is enabled, so Customer and Items are virtual navigations.
List<Order> orders = await context.Orders
    .Where(o => o.OrderDate >= from)
    .ToListAsync();                      // 200 orders, 200 different customers

decimal grandTotal = 0m;

foreach (Order order in orders)
{
    // Ordinary property access. Also a database round trip, every time.
    Console.WriteLine($"{order.Id}: {order.Customer.Name}");

    // And another one, for the collection.
    grandTotal += order.Items.Sum(i => i.Quantity * i.UnitPrice);
}

• order.Customer loads one customer. order.Items loads a collection. Both happen on first access, which is inside the loop.
• The count is 1 + 200 + 200 = 401 queries to render one screen.
• In development, with ten orders and a database on the same machine, this takes about 40 milliseconds and nobody notices anything.
• Read the loop again. By the standards of ordinary C#, nothing here is written badly. That is the entire problem.

-- 1. Once
SELECT [o].[Id], [o].[CustomerId], [o].[OrderDate], [o].[Status]
FROM [Orders] AS [o]
WHERE [o].[OrderDate] >= @__from_0;

-- 2. Once per order. 200 times, with a different parameter each time.
SELECT [c].[Id], [c].[Name], [c].[Email]
FROM [Customers] AS [c]
WHERE [c].[Id] = @__p_0;

-- 3. Once per order. 200 more times.
SELECT [i].[Id], [i].[OrderId], [i].[ProductId], [i].[Quantity], [i].[UnitPrice]
FROM [OrderItems] AS [i]
WHERE [i].[OrderId] = @__p_0;

• Every one of those statements is fast. Look at any single line of the log and there is nothing to fix.
• The cost is the count, and the count is invisible unless somebody is counting. This is why query logging in development is not an advanced technique — it is the only way to see this class of bug.
• Each round trip pays network latency and connection overhead regardless of how little data it moves. Four hundred of them against a database in another data centre is seconds, not milliseconds.
• The signature to learn: the same statement repeating in the log with only the parameter changing. Once you know that shape you will spot it in ten seconds.

// Fix 1 — keep the entities, fetch the related data with them.
List<Order> orders = await context.Orders
    .Where(o => o.OrderDate >= from)
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .ToListAsync();

// Fix 2 — ask for the values the screen uses, and nothing else.
public record OrderRow(int Id, string CustomerName, decimal Total);

List<OrderRow> rows = await context.Orders
    .Where(o => o.OrderDate >= from)
    .Select(o => new OrderRow(
        o.Id,
        o.Customer.Name,
        o.Items.Sum(i => i.Quantity * i.UnitPrice)))
    .ToListAsync();

• Fix 1 sends one query with joins. The loop that follows touches the database zero times, and the entities are still tracked, so code that goes on to change and save an order keeps working.
• Fix 2 sends one query for three values per order. No entities, no tracking, and the total is summed by the database instead of by fetching every order item.
• Both take the count from 401 to 1. Which you choose depends on whether anything downstream needs the entity, not on which is faster in the abstract.
• Neither fix requires turning lazy loading off, but turning it off is worth considering: it converts this bug from a silent slowdown into a null reference you find immediately.

SELECT [o].[Id], [c].[Name],
       COALESCE((
           SELECT SUM([i].[Quantity] * [i].[UnitPrice])
           FROM [OrderItems] AS [i]
           WHERE [o].[Id] = [i].[OrderId]), 0.0) AS [Total]
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
WHERE [o].[OrderDate] >= @__from_0;

• One statement, 200 rows, three columns each. The aggregation happens where the data already is.
• Compare that with the 401 statements above. The work did not disappear — it moved to the component that is built for it, and the round trips went with it.
• Log your own version rather than assuming it matches this one. The shape depends on your model, your provider and your EF Core version.