How to Diagnose and Fix EF Core N+1 Queries in Production

The N+1 query problem is not an Entity Framework Core trivia question. It is a production failure pattern: an endpoint appears healthy with small data, then generates hundreds of database round trips when traffic or result size grows.

A practical definition

N+1 happens when an application executes one query to load a collection and then one additional query for each row, usually while traversing a navigation property. With 500 orders, the endpoint may execute 501 commands.

Start with evidence

Do not begin by adding Include everywhere. Capture the database-command count for one request, normalized SQL shape, command duration, rows returned, response payload, and trace ID. In development, EF Core logging and ToQueryString() reveal generated SQL. In production, use structured command telemetry or a DbCommandInterceptor, and avoid recording sensitive parameter values.

A convincing N+1 signal is a burst of nearly identical SQL commands with different parameter values inside one request trace. A single slow command is a different problem and needs execution-plan and index analysis.

Prefer a projection for read models

For a read-only endpoint, define the exact response shape in the query:

var rows = await db.Orders
    .Where(o => o.CreatedAt >= from)
    .OrderByDescending(o => o.CreatedAt)
    .Select(o => new OrderSummary(
        o.Id,
        o.Customer.Name,
        o.Total,
        o.Items.Count))
    .Take(100)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

This keeps filtering and aggregation on the database, selects only required columns, avoids full entity materialization, and bounds the response. Inspect the generated SQL and test with representative data rather than assuming every LINQ expression translates optimally.

Why Include can make things worse

Include is appropriate when the application really needs an entity graph, but multiple collection Includes can multiply rows. An order with ten items and five adjustments can produce fifty joined rows before EF reconstructs the graph.

AsSplitQuery() can trade the huge join for several smaller commands. That may reduce transferred rows, but it adds round trips and can observe changes between commands. AsSingleQuery() keeps one round trip but may duplicate a great deal of data. Neither is universally correct; measure with the actual model, provider, and data volume.

For a public API summary, projection is usually cleaner. For a write workflow that needs aggregate behavior, an included tracked graph may be justified.

Choose tracking deliberately

Use AsNoTracking() for queries that will not update loaded entities. Tracking costs memory and change-detection work. If a no-tracking graph repeats references and reference identity matters, AsNoTrackingWithIdentityResolution() is available, but benchmark its allocations.

Do not add no-tracking mechanically to a query whose result will later be modified. Optimizations must preserve the intended unit of work.

Use pagination that scales

Skip/Take offset pagination becomes more expensive at deep pages because the database locates and discards earlier rows. A stable feed should prefer keyset pagination using the last ordered values as a cursor:

var page = await db.Orders
    .Where(o => o.CreatedAt < cursorTime ||
        (o.CreatedAt == cursorTime && o.Id.CompareTo(cursorId) < 0))
    .OrderByDescending(o => o.CreatedAt)
    .ThenByDescending(o => o.Id)
    .Select(o => new OrderSummary(o.Id, o.Customer.Name, o.Total))
    .Take(pageSize)
    .AsNoTracking()
    .ToListAsync(cancellationToken);

The order must be unique and the cursor must contain every ordering component. Add an index that matches the filter and ordering.

Indexes still matter

EF Core produces SQL; the database executes it. A projection cannot compensate for a missing index on a selective predicate or sort. Inspect the actual execution plan, logical reads, join strategy, and cardinality estimates. Derive indexes from real predicates and ordering rather than guessing from entity names.

Keep IQueryable server-side

A premature ToListAsync moves later filtering into application memory:

var orders = await db.Orders.ToListAsync(token);
var recent = orders.Where(o => o.CreatedAt >= from);

Compose the IQueryable and execute once at the end. If an operation intentionally crosses into in-memory processing, bound the input first and document why the provider cannot perform that step.

A production diagnosis sequence

1. Reproduce with realistic row counts and relationships.
2. Trace one request and count database commands.
3. Group normalized SQL to find repeated shapes.
4. Inspect ToQueryString() and the actual database execution plan.
5. Define the minimal response data shape.
6. Choose projection, Include, or split queries deliberately.
7. Add bounding and keyset pagination where appropriate.
8. Add or correct indexes from predicates and ordering.
9. Load test and compare command count, logical reads, p95 latency, payload, and memory.
10. Add a regression signal.

Prevent the regression

Add an integration test or diagnostic interceptor that asserts a reasonable command-count ceiling. A split query may legitimately use three commands, but the count should not grow linearly with returned rows. Pair this with performance telemetry because a low query count can still hide one expensive SQL statement.

Common interview traps

• AsNoTracking() does not remove N+1 round trips.
• Include can replace N+1 with a cartesian explosion.
• Compiled queries do not fix slow SQL or missing indexes.
• Caching can hide the symptom while cold requests remain dangerous.
• Parallel queries on one DbContext are unsafe; separate contexts can still overload the database.

A strong senior answer starts with evidence, explains the intended data shape, shows the SQL and index implications, and closes with a regression guard. That demonstrates production reasoning rather than memorized EF Core switches.