EF Core: N+1 Problem

Problem

The N+1 problem happens when your code executes:

1. one query to load a parent list
2. one extra query per parent item to load related data

If you load 100 orders and then access order.Items inside a loop, you can end up with 101 SQL queries instead of 1 to 2 optimized queries.

Why this hurts:

• higher latency from many database round-trips
• more load on your database server
• slower API responses under traffic
• performance regressions that are hard to notice in local testing

A common cause is lazy loading or accidental navigation-property access in loops.

// Example that can trigger N+1
var orders = await db.Orders
    .Where(o => o.CreatedAt >= fromDate)
    .ToListAsync();

foreach (var order in orders)
{
    // If lazy loading is enabled, this may run one query per order
    Console.WriteLine($"Order {order.Id} has {order.Items.Count} items");
}

Solution

Use one of these patterns depending on your read scenario:

1. Eager loading with Include/ThenInclude when you need full related entities.
2. Projection with Select for API/read models to fetch only needed fields.
3. Disable lazy loading unless you have a very specific use case.
4. Use AsSplitQuery() for large include graphs to avoid huge cartesian explosions.
5. Observe generated SQL with EF logging so N+1 is visible early.

Preferred for APIs: projection

var result = await db.Orders
    .Where(o => o.CreatedAt >= fromDate)
    .Select(o => new OrderSummaryDto
    {
        OrderId = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Items.Count,
        Total = o.Items.Sum(i => i.UnitPrice * i.Quantity)
    })
    .AsNoTracking()
    .ToListAsync();

This typically becomes a single SQL query and avoids loading unnecessary entity state.

Description

1) Eager loading example (.NET)

var orders = await db.Orders
    .Where(o => o.CreatedAt >= fromDate)
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .AsNoTracking()
    .ToListAsync();

This prevents one-query-per-order behavior for Customer and Items.

2) Split query for large relationship graphs

var orders = await db.Orders
    .Where(o => o.CreatedAt >= fromDate)
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .AsNoTracking()
    .ToListAsync();

AsSplitQuery() trades one massive join for multiple coordinated queries, which can reduce duplicated result rows in complex graphs.

3) Detecting N+1 in practice

Enable EF Core SQL logging in development so repeated query patterns are obvious:

builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.EnableSensitiveDataLogging(); // dev only
    options.LogTo(Console.WriteLine, LogLevel.Information);
});

What to watch for:

• same SELECT shape repeated many times with different parameter values
• query count scaling linearly with returned parent rows
• slow endpoints that iterate over navigation properties

4) Practical rule of thumb

• For list endpoints: prefer projection to DTOs.
• For aggregate/business behavior: load only relationships truly needed.
• For write workflows: avoid over-fetching entities you do not modify.

Summary

The EF Core N+1 problem is a query-shape issue, not just a “database is slow” issue.

You can eliminate it by:

• avoiding lazy-loading surprises
• using projection for read models
• using Include intentionally
• applying AsSplitQuery() for heavy include trees
• monitoring SQL in development and test environments

A few deliberate query choices can reduce dozens or hundreds of round-trips to a predictable, efficient query plan.

References

• Microsoft Docs: Loading Related Data
• Microsoft Docs: Single vs. Split Queries
• Microsoft Docs: Efficient Querying
• Microsoft Docs: Logging, Events, and Diagnostics