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

An EF Core endpoint can appear healthy with small data, then generate hundreds of database round trips as the result set grows. Diagnose the executed commands before changing the query shape.

The EF Core examples below are integration sketches for an application model. They have not been executed against a database in this review. SQL translation, plans, command counts, and performance must be checked with the deployed EF Core version and relational provider.

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. The extra loads can come from lazy loading or explicit queries in a loop; simply reading a navigation does not always query the database. Check how that navigation is configured and whether it is already loaded.

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() help inspect SQL. ToQueryString() is diagnostic output, not evidence of the commands actually executed. 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. This example assumes a unique long order ID, a non-null UTC DateTime CreatedAt, a required customer, and a mapped items collection. Both queries use this same DTO declaration:

public sealed record OrderSummary(
    long Id,
    DateTime CreatedAt,
    string CustomerName,
    decimal Total,
    int ItemCount);

Application query sketch:

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

The intended translation keeps filtering and aggregation on the database, selects only required columns, avoids full entity materialization, and bounds the response. This DTO contains scalar values, so the example has no returned entity to track; AsNoTracking() documents read intent rather than fixing N+1. 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 sibling 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() uses one query for this graph but may duplicate a great deal of data. Count actual command attempts separately from retries and other work in the same request. 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. For next-page navigation in a stable feed, prefer keyset pagination using the last ordered values as a cursor. Here cursorTime and cursorId are the exact CreatedAt and Id of the last returned row, using the types above. This is the next-page query; the first query has no cursor predicate. Validate the page-size limit before building the query:

if (pageSize is < 1 or > 100)
    throw new ArgumentOutOfRangeException(nameof(pageSize));

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

The order must be unique and the cursor must contain every ordering component. Preserve the stored timestamp precision and keep the filter and authorization scope consistent between pages. The numeric comparison here deliberately uses a long; do not substitute a GUID/string comparison without checking the provider's translation and ordering. Add an index that matches the filter and ordering.

Worked boundary: Suppose the descending keys are (12:00, 42), (12:00, 41), (11:59, 99). A two-row first page ends at (12:00, 41). The next-page predicate excludes both first-page rows and includes (11:59, 99). A time-only cursor would incorrectly skip ID 41 if the first page ended at ID 42. A non-strict ID comparison would repeat the cursor row. These conclusions follow from the predicate; they do not establish a database plan or snapshot guarantee. If an ordering key is edited between requests, a row can move across the cursor.

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);

For this same example, move the Where before ToListAsync and add a result bound:

var recent = await db.Orders
    .Where(o => o.CreatedAt >= from)
    .OrderByDescending(o => o.CreatedAt)
    .ThenByDescending(o => o.Id)
    .Take(100)
    .AsNoTracking()
    .ToListAsync(token);

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.

Worked regression design: Use isolated, seeded requests that return 10 and 100 orders. Reset the counter before each request, execute serialization too if it can trigger loading, and count only the commands belonging to that operation. The classic one-parent-query plus one-items-query-per-order pattern predicts 11 and 101 commands. A deliberately selected one-command projection or fixed three-command split shape should remain bounded at both sizes. Those numbers are test expectations for those shapes, not measurements from this review.

Assert the returned IDs, customer names, totals, and item counts as well as the command ceiling. An empty or incorrectly filtered response can pass a query-count test. Exercise cold requests, duplicate timestamps at page boundaries, no children, and the largest supported page. Keep retries/failed attempts visible so a test does not misclassify retry traffic as N+1. A shared global counter can mix concurrent requests; correlate or isolate it before interpreting a failure.

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.

Practice answer

Question: A 100-order response executes 101 commands. Would you add Include, no-tracking, or a cache first?

Answer: I would first confirm that the 100 repeated commands load one relationship per order within the request, rather than being retries or unrelated traffic. For a summary response, I would project the required customer name, total, and item count, inspect the executed SQL, and verify identical response data. If the workflow needs the entity graph, I would compare a deliberate Include shape with split queries. I would then repeat the 10-row and 100-row tests and compare command count, reads, payload, memory, and latency. No-tracking and caching can be useful separately, but neither proves the repeated loads are gone.

Primary references

• EF Core efficient querying
• Single versus split queries
• Tracking and no-tracking queries
• Pagination
• ToQueryString API