Fixing the N+1 query problem in EF Core

The N+1 problem is the most common performance bug I find in .NET code reviews, and it almost never shows up in testing. It hides until production data grows, then a list endpoint that felt instant with ten rows takes four seconds with two hundred.

What it looks like

Say you're listing orders and each order's customer name.

var orders = db.Orders.ToList();
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name); // lazy load fires here
}

That reads like one query. It isn't. EF Core runs one query for the orders, then one more query per order to load its customer. Two hundred orders means 201 round trips to the database. That's the N+1: one query to get the list, plus N to fill in the details.

The reason it survives testing is that with a handful of rows the extra queries are too fast to notice. The cost is linear in your data, so it only hurts once real customers show up.

Step one: see the queries

You can't fix what you can't see. I turn on query logging in development so every SQL statement EF generates prints to the console.

builder.Services.AddDbContext<AppDbContext>(o =>
    o.UseNpgsql(conn)
     .LogTo(Console.WriteLine, LogLevel.Information)
     .EnableSensitiveDataLogging());

Load the endpoint, watch the output. If you see the same SELECT repeated with only the id changing, you've found an N+1. EnableSensitiveDataLogging shows the parameter values so you can confirm it's the same shape firing over and over. Keep it off in production, it logs real data.

Fix one: Include

The direct fix is to tell EF what related data you need up front, so it fetches everything in one round trip.

var orders = db.Orders
    .Include(o => o.Customer)
    .ToList();

Now there's a single query with a join. For loading one level of related data this is all you need. The trap is over-including: chaining several Include and ThenInclude calls onto a collection produces a cartesian explosion, where the row count multiplies across the joins and you ship megabytes to dedupe in memory. EF warns about this in the logs. When you see it, split into separate queries or use projection.

Fix two: project to exactly what you need

Most list endpoints don't need full entities, they need a few fields. Projecting with Select tells EF to fetch only those columns, and it handles the join for you without loading whole objects.

var rows = db.Orders
    .Select(o => new OrderListItem
    {
        Id = o.Id,
        Total = o.Total,
        CustomerName = o.Customer.Name
    })
    .ToList();

This is my default for anything that renders a table or feeds an API list response. It sidesteps N+1 entirely, moves less data over the wire, and skips change-tracking overhead because you're returning DTOs, not tracked entities. For read-heavy endpoints, projection is usually the single biggest win.

Fix three: split queries for big collections

When you genuinely need a parent with several large child collections, a single join multiplies rows badly. EF Core can run one query per collection and stitch the results together instead.

var orders = db.Orders
    .Include(o => o.LineItems)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .ToList();

This trades one bloated query for a few lean ones. It's the right call when the collections are large, and the wrong call when they're tiny, because then you're paying for extra round trips you didn't need. Measure before reaching for it.

Turn lazy loading off

The root cause of the surprise is lazy loading, where touching a navigation property silently fires a query. I don't enable it. Without it, order.Customer is simply null unless you loaded it, which turns a hidden performance bug into an obvious NullReferenceException you catch in development. Explicit is safer than convenient here. If you inherited a project with lazy loading on, that's the first setting I'd audit.

The habit that prevents it

I treat any foreach over query results that touches a navigation property as a review flag. If the loop reads x.SomethingRelated, either that relation was Included, or the whole thing should have been a projection. Making that a reflex catches almost every N+1 before it ships.

Performance work on EF Core mostly comes down to knowing how many queries your code actually runs. Log them, count them, and the fixes are usually one line. The same discipline pays off at the database layer too, which is where Postgres indexing comes in.