EF Core in Production: The N+1 Trap and How to Spot It

What N+1 looks like

You query 100 orders, then iterate to access each order's Customer navigation property. EF Core fires 1 query for the orders + 100 lazy-loaded customer queries = 101 round-trips.

var orders = await db.Orders.ToListAsync();   // 1 query
foreach (var o in orders)
    Console.WriteLine(o.Customer.Email);       // 100 queries

This is invisible in dev (everything fast on localhost) and catastrophic in production (latency on every row).

Three fixes — pick by query shape

Fix 1: Eager loading with Include

Simplest. Best for 1-to-1 or small 1-to-many.

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

Generates one SQL statement with a JOIN. But beware Cartesian explosion on multiple Includes.

Fix 2: Split queries

Multiple Includes on collections create a Cartesian product. EF Core's AsSplitQuery() runs separate queries and stitches them in memory.

var orders = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.LineItems).ThenInclude(li => li.Product)
    .AsSplitQuery()
    .ToListAsync();

Always set this when you Include 2+ collection navigations.

Fix 3: Projection — usually the right answer

Most of the time you don't need the entity. Project to the shape you actually return.

var orders = await db.Orders
    .Select(o => new OrderListItem(
        o.Id,
        o.CustomerName,
        o.Total,
        o.LineItems.Count
    ))
    .ToListAsync();

EF Core translates this into one efficient SQL query that returns only the columns you need. No tracking, no Cartesian, no navigation lazy-load surprises.

Detect N+1 in development

Enable EF Core's SQL logging and look for repeated identical SELECT shapes.

builder.Services.AddDbContext<AppDb>(opt => opt
    .UseNpgsql(connString)
    .LogTo(Console.WriteLine, LogLevel.Information));

In tests, install MiniProfiler.EntityFrameworkCore and assert query counts:

Assert.That(profiler.GetCommandCount(), Is.LessThan(3));

AsNoTracking for read-only queries

Anything you display and don't update should be tracked-off. Saves memory and CPU.

var products = await db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

For the whole context, you can set the default:

optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

The thing nobody warns you about

The Activity API in .NET 9 adds OpenTelemetry traces per EF query automatically. Wire it into your APM (Datadog, Honeycomb, Application Insights) and N+1 shows up as a sawtooth pattern in your trace waterfall. Best leading indicator there is.