Fixing the N+1 Query Problem in Entity Framework Core

Early in my career I shipped a page that listed 50 orders. It worked beautifully in development, then crawled in production. The database logs told the story: loading that one page executed 51 queries — one to fetch the orders, then one more per order to fetch its customer. That's the N+1 query problem, and it remains the single most common performance bug I find in Entity Framework Core codebases.

What N+1 actually looks like

Here's the innocent-looking code:

csharp
var orders = await _db.Orders
    .Where(o => o.Status == OrderStatus.Open)
    .ToListAsync();

foreach (var order in orders)
{
    // Each access to order.Customer triggers a separate query
    Console.WriteLine($"{order.Id}: {order.Customer.Name}");
}

If lazy loading is enabled (via UseLazyLoadingProxies and virtual navigation properties), every order.Customer access that isn't already loaded fires a SELECT against the database. One query becomes N+1. With nested navigations — orders → customer → region — it compounds fast.

If lazy loading is not enabled, the same code throws a NullReferenceException or just gives you null customers, which is how many teams discover navigation properties in the first place.

How to spot it before your users do

You can't fix what you can't see, so wire up visibility first:

csharp
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
        // Development only:
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging());

Watch the console while you click through your app. If one page load scrolls a wall of near-identical queries, you've found one. In production, database monitoring (or tools like MiniProfiler) will show high query counts per request even when each query is individually fast — the signature of N+1.

Fix 1: Eager loading with Include

The direct fix is telling EF Core up front which related data you need:

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

One round trip, one JOIN. For nested relationships, chain ThenInclude:

csharp
var orders = await _db.Orders
    .Include(o => o.Customer)
        .ThenInclude(c => c.Region)
    .Include(o => o.Lines)
    .ToListAsync();

Include is the right tool when you genuinely need the full entities — for example, when you're about to modify them and want change tracking.

Fix 2 (usually better): Project with Select

Most read paths don't need tracked entities; they need a handful of columns for a screen or an API response. Projection fetches exactly that in one query:

csharp
var orderSummaries = await _db.Orders
    .Where(o => o.Status == OrderStatus.Open)
    .Select(o => new OrderSummaryDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,   // translated into the JOIN
        LineCount = o.Lines.Count(),      // translated into a subquery
        Total = o.Lines.Sum(l => l.Price * l.Quantity),
    })
    .AsNoTracking()
    .ToListAsync();

Notice there's no Include — navigations referenced inside Select are translated straight into SQL. You get less data over the wire, no change-tracking overhead, and no possibility of a lazy-load ambush later, because the DTO has no navigation properties to trip on.

My default for queries that feed UI or API responses: projection + AsNoTracking. I reach for Include only when I need the entity graph for updates or domain logic.

The cartesian explosion: when Include backfires

Including multiple collection navigations produces a JOIN that multiplies rows:

csharp
// 100 orders × 10 lines × 5 shipments = 5,000 rows transferred
var orders = await _db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Shipments)
    .ToListAsync();

Each order's data is duplicated across every combination of lines and shipments. The fix is split queries, which fetch each collection with its own SELECT:

csharp
var orders = await _db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Shipments)
    .AsSplitQuery()
    .ToListAsync();

Three tidy queries instead of one exploding one. The trade-off: multiple round trips and no single consistent snapshot (another transaction could modify data between the queries). For most listing screens that's fine; for financial reads, wrap it in a transaction or accept the single query.

Should you just disable lazy loading?

In my opinion: yes, in almost every new project. Lazy loading turns a compile-time visible decision ("what data does this feature need?") into invisible runtime behavior. Without proxies, forgetting to load a navigation fails loudly and immediately in development instead of quietly multiplying queries in production. Every team I've been on that dropped lazy loading stopped having N+1 regressions almost entirely.

Checklist for review

  • Any foreach touching a navigation property is a suspect — check what was loaded.
  • Read-only queries: Select projection + AsNoTracking.
  • Entity-graph loads: explicit Include/ThenInclude.
  • More than one collection Include: consider AsSplitQuery().
  • Query logging on in development; query counts monitored in production.

The N+1 problem isn't really an EF Core flaw — it's what happens when the abstraction hides the database too well. Make the queries visible, be explicit about what each feature loads, and this entire bug class disappears.

Where to go next