N+1 Query Problem in EF Core and How to Fix It

The N+1 query problem is when code loads N parent rows with one query, then fires one more query per parent to load related data. Loading 100 orders and then their line items in a loop costs 101 round trips instead of one. Fix it with Include, a Select projection, or AsSplitQuery, and prevent most cases by leaving lazy loading disabled.

An endpoint can look fast with five rows and collapse when it returns five hundred. Detecting the pattern requires looking at the query count, then choosing an explicit loading or projection strategy.

What Is the N+1 Problem?

You load 100 orders, then for each order you load its line items. That's 1 query for orders + 100 queries for line items = 101 database round trips.

// ❌ N+1 problem - 101 queries for 100 orders
var orders = await _db.Orders.ToListAsync();

foreach (var order in orders)
{
    // Each iteration triggers a lazy-load query
    var total = order.LineItems.Sum(li => li.Price * li.Quantity);
    Console.WriteLine($"Order {order.Id}: {total}");
}

With lazy loading enabled, accessing order.LineItems triggers a separate query for each order. The SQL output looks like:

-- Query 1: Get all orders
SELECT * FROM "Orders";

-- Query 2..101: Get line items for each order
SELECT * FROM "LineItems" WHERE "OrderId" = @p0;
SELECT * FROM "LineItems" WHERE "OrderId" = @p1;
SELECT * FROM "LineItems" WHERE "OrderId" = @p2;
-- ... 97 more

This is extremely slow, especially with network latency to the database.

Fix 1: Eager Loading With Include

Load related data in a single query:

// ✅ 1 query - eager loading
var orders = await _db.Orders
    .Include(o => o.LineItems)
    .ToListAsync();

foreach (var order in orders)
{
    var total = order.LineItems.Sum(li => li.Price * li.Quantity);
}

SQL:

SELECT o.*, li.*
FROM "Orders" o
LEFT JOIN "LineItems" li ON o."Id" = li."OrderId";

One query. One round trip. All data loaded.

Nested Includes

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

Filtered Includes (EF Core 5+)

var orders = await _db.Orders
    .Include(o => o.LineItems.Where(li => li.Quantity > 0))
    .ToListAsync();

Fix 2: Split Queries

A single query with multiple Includes can produce a cartesian explosion. Use split queries:

var orders = await _db.Orders
    .Include(o => o.LineItems)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .ToListAsync();

This generates a small, fixed number of queries - one base query plus one per included collection - instead of one giant join:

-- Query 1
SELECT * FROM "Orders";

-- Query 2 (joined against the same Orders filter)
SELECT li.* FROM "LineItems" li
INNER JOIN "Orders" o ON li."OrderId" = o."Id";

-- Query 3
SELECT p.* FROM "Payments" p
INNER JOIN "Orders" o ON p."OrderId" = o."Id";

That's 3 round trips regardless of how many orders you load - a fixed cost, unlike N+1. Two caveats: more round trips add latency, and the queries can observe different data if rows change between them (wrap in a transaction if that matters). The split-versus-single-query tradeoff is covered in EF Core query splitting.

Configure globally:

options.UseNpgsql(connectionString, o =>
{
    o.UseQuerySplittingBehavior(
        QuerySplittingBehavior.SplitQuery);
});

Fix 3: Projection

Only load what you need:

// ✅ Most efficient - single query, minimal data
var orderSummaries = await _db.Orders
    .Select(o => new OrderSummaryDto
    {
        OrderId = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.LineItems.Count,
        Total = o.LineItems.Sum(li => li.Price * li.Quantity)
    })
    .ToListAsync();

No N+1 problem because EF Core computes everything in SQL:

SELECT
    o."Id" AS "OrderId",
    c."Name" AS "CustomerName",
    (SELECT COUNT(*) FROM "LineItems" li WHERE li."OrderId" = o."Id") AS "ItemCount",
    (SELECT SUM(li."Price" * li."Quantity") FROM "LineItems" li WHERE li."OrderId" = o."Id") AS "Total"
FROM "Orders" o
JOIN "Customers" c ON o."CustomerId" = c."Id";

Projection is the best approach when you don't need the full entity graph.

Fix 4: Explicit Loading

Load related data on demand for specific entities:

var order = await _db.Orders.FindAsync(orderId);

// Explicitly load the collection
await _db.Entry(order)
    .Collection(o => o.LineItems)
    .LoadAsync();

// Now access without triggering lazy loading
var total = order.LineItems.Sum(li => li.Price * li.Quantity);

Better than lazy loading because you control when the query happens.

Detecting N+1 Problems

Option 1: SQL Logging

options.UseNpgsql(connectionString)
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging();

Watch for repeated similar queries in the console output. More options for this are in how to log SQL queries in EF Core.

Option 2: EF Core Interceptor

public class QueryCountInterceptor : DbCommandInterceptor
{
    private int _queryCount;

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        _queryCount++;

        if (_queryCount > 10)
        {
            Console.WriteLine(
                $"⚠️ {_queryCount} queries executed! Possible N+1.");
        }

        return base.ReaderExecutingAsync(
            command, eventData, result, cancellationToken);
    }
}

Register it as scoped so the counter resets per request. Async queries (ToListAsync and friends) only hit the async interception methods, so override ReaderExecuting as well if any code path uses synchronous execution.

Option 3: MiniProfiler

MiniProfiler highlights duplicate queries automatically:

builder.Services.AddMiniProfiler(options =>
{
    options.RouteBasePath = "/profiler";
}).AddEntityFramework();

Disable Lazy Loading

The simplest way to prevent N+1 - don't enable lazy loading in the first place:

// ❌ Enables lazy loading - invites N+1
options.UseLazyLoadingProxies();

// ✅ Default - no lazy loading
// Accessing unloaded navigation returns null or empty collection
options.UseNpgsql(connectionString);

Without lazy loading, unloaded navigations simply stay unpopulated: reference navigations are null, and collection navigations hold whatever you initialized them to (typically an empty list). This forces you to explicitly load what you need - and makes missing data obvious in testing instead of silently slow in production.

The N+1 problem is one of several EF Core query performance mistakes worth auditing your codebase for.

Quick Reference

• Include: when you need full entities with their children; 1 query (or a few, if split)
• AsSplitQuery: when multiple includes cause a cartesian explosion; one base query plus one per included collection
• Select projection: for read-only scenarios needing specific fields; 1 query, minimal data
• Explicit loading: for loading children of a single entity on demand; 1 extra query per LoadAsync
• Dapper or raw SQL: when you need maximum control; exactly the SQL you write

Summary

The N+1 problem can turn one query into hundreds of network round trips. Prefer a projection for read models, Include for entity graphs, and split queries when one joined result would duplicate too much data. Keep lazy loading disabled by default and verify query counts on representative data with logging, an interceptor, or a profiler.