How to Find and Fix the N+1 Query Problem in Entity Framework Core

An order history page loads quickly with a few test records. With real customer data, it becomes slow, even though every individual database query looks fast. The missing clue is often the number of database commands one request executes.

Entity Framework Core can hide that cost behind familiar object access. A loop that looks like ordinary in-memory work may actually perform another database round trip for every order.

Introduction

The N+1 query problem occurs when an initial query loads a collection and later code issues another query for each item. For fifty orders, that might mean one command for the orders and fifty more for their items.

The database does not need a single exceptionally slow query for the page to become slow. Network latency, connection activity and repeated command processing accumulate across the request.

The same pattern can also reduce capacity. One page view consumes dozens of opportunities to query the database, multiplying the effect when many customers use the page together.

This article follows an order history endpoint from diagnosis through several fixes. It covers projections, eager loading, split queries and explicit batching, then explains how to verify that the replacement is both correct and efficient.

The goal is a predictable access pattern that returns the data the page needs. Reducing command count is part of that goal, but so are payload size, consistency and end-to-end latency.

Recognise What N+1 Looks Like

Suppose lazy loading is configured and a handler loads recent orders:

var orders = await db.Orders
    .OrderByDescending(order => order.Id)
    .Take(50)
    .ToListAsync(cancellationToken);

foreach (var order in orders)
{
    Console.WriteLine(
        $"{order.Id}: {order.Items.Count} items");
}

If each Items navigation is unloaded, reading it can trigger a separate query. The loop then performs database work even though its body contains no explicit asynchronous query call.

Without lazy loading, accessing that navigation does not automatically fetch missing rows. The collection may be empty, partially populated through relationship fix-up, or populated by earlier loading. Do not assume its contents establish what exists in the database.

Lazy loading is only one route to N+1. An explicit LoadAsync inside the loop produces the same access pattern. A repository call such as GetItemsForOrderAsync(order.Id) can do so too.

Nested loops can make it worse. Loading each order's items and then loading each item's supplier can create another level of repeated access.

Microsoft's efficient querying guidance explains why repeated round trips are costly. The diagnostic question is whether database work grows with the number of parent records, regardless of which abstraction triggers it.

Establish the Endpoint's Data Contract

Before changing the query, identify what the response actually requires. A summary page might show order reference, creation time, status, item count and total value.

It probably does not need every tracked item entity, delivery address revision, payment attempt and internal note. Fetching the entire relationship graph can be much more expensive than computing a few summary values.

A detail page has a different contract. It may genuinely need each line item, its product name and its quantity. That requirement can justify loading or projecting child rows deliberately.

Write down the page size, sort order, filters and whether totals refer to all matching orders or only the current page. These details affect both correctness and query cost.

Also define access boundaries. Every replacement query must preserve customer or tenant filtering. A faster query that accidentally returns another customer's orders is a serious regression.

The data contract gives you a basis for choosing between a projection, eager loading and batching. Without it, the team can spend time optimising data the consumer never needed.

Count Commands Across One Complete Request

Enable the Microsoft.EntityFrameworkCore.Database.Command category at Information level in a development environment or a controlled diagnostic window:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.EntityFrameworkCore.Database.Command": "Information"
    }
  }
}

Correlate the resulting logs with one request or trace identifier. Under concurrent traffic, counting adjacent log lines without correlation can mix commands from unrelated requests.

Look for an orders query followed by repeated child queries with the same shape and different order identifiers. For example, repeated WHERE OrderId = @... commands are a useful clue.

Capture the complete request path, including mapping and response serialisation. Returning tracked entities can leave navigation access until after the main handler has finished its explicit query work.

If a command interceptor or tracing integration is already available, it can provide request-level counts and durations. Be precise about retries: a retried command is real database demand even if it originated from one logical query.

Do not enable sensitive parameter logging merely to count commands. Query shapes, operation names and correlation often provide enough evidence without placing customer data into ordinary logs.

Microsoft's EF Core logging documentation describes filtering and diagnostic options. Choose a collection method that fits the environment and remove temporary noisy logging after the investigation.

Use Query Inspection for the Right Question

ToQueryString() is useful when examining how a particular LINQ expression will translate. It is not a complete record of everything the endpoint later executes.

For example, the initial orders query can look excellent while a mapper lazily loads items for every result. Inspecting only that initial query misses the actual problem.

Query tags can make a query easier to recognise in logs:

var query = db.Orders
    .TagWith("OrderHistory.Summary")
    .Where(order => order.CustomerId == customerId)
    .OrderByDescending(order => order.Id)
    .Take(pageSize);

var sql = query.ToQueryString();

Use a stable descriptive tag rather than embedding sensitive values or generating a unique SQL comment for every record. The tag identifies the operation; existing logging correlation identifies the request.

Microsoft's query tags guidance explains how tags appear in generated SQL.

Compare the inspected query with execution logs. If the logs contain additional shapes, find the code path that triggers them rather than assuming the LINQ expression is the whole story.

For split queries, command logging is especially valuable because the operation intentionally consists of several database commands. Inspection and execution diagnostics complement one another.

Establish a Baseline That Exposes Growth

Run the same endpoint with small and larger page sizes, using representative data. A sequence such as 5, 20 and 50 parent records can reveal whether command count grows linearly.

For an illustrative N+1 endpoint, the observations might be 6, 21 and 51 commands. Those are example counts, not measured results for every model. They demonstrate the shape you are looking for.

Record total database duration, end-to-end request time and response size as well. A fix that reduces commands while multiplying transferred rows may not improve the page.

Include orders with no items, a typical number of items and unusually many items. Uniform seed data often hides the relationship shapes that dominate production cost.

Use a fresh context or request for each comparison. Previously tracked entities and loaded navigations can make later runs issue fewer commands, obscuring the original behaviour.

Keep cache state and database conditions in mind. Compare like with like, and repeat enough to distinguish a structural improvement from one unusually fast run.

Project Summary Data in the Database

If the page only needs an item count, express that in the query:

var summaries = await db.Orders
    .Where(order => order.CustomerId == customerId)
    .OrderByDescending(order => order.Id)
    .Take(pageSize)
    .Select(order => new
    {
        order.Id,
        order.CreatedAt,
        order.Status,
        ItemCount = order.Items.Count()
    })
    .ToListAsync(cancellationToken);

On a suitable relational provider, EF Core translates the count into the SQL query rather than loading every collection into memory. Verify translation for your provider and model.

The projection states the response contract clearly. There is no need for a later loop to ask each order for its items.

Apply filtering, ordering and shaping before materialisation. Calling ToListAsync and then performing the projection has already committed to loading whatever the earlier query selected.

A scalar projection also avoids tracking complete order entities. If a projection includes actual entity instances, tracking behaviour can still apply to those entities; “uses Select” does not automatically mean “tracks nothing”.

The result should be a DTO or another deliberately shaped response. Returning it directly reduces the chance that serialisation will traverse an unexpected relationship.

Handle Aggregates and Empty Collections

An item count is straightforward, but totals need careful semantics. Does the order total include discounts, tax, shipping or refunded items? A faster sum is still wrong if it changes the business meaning.

For a nullable aggregate, define the empty-case result explicitly. The following fragment illustrates summing line values where the model stores quantity and unit price:

Total = order.Items
    .Select(item => (decimal?)(item.Quantity * item.UnitPrice))
    .Sum() ?? 0m

Provider translation and decimal behaviour should be verified against the actual database. Domain calculations involving rounding or historical prices may require a different expression or a stored authoritative total.

Do not replace a business calculation with a convenient SQL aggregate merely because it avoids N+1. Preserve the intended meaning, then choose an efficient representation.

For counts or totals filtered by status, include that filter inside the child expression. The result should not depend on whether some unrelated navigation happened to be loaded earlier in the context.

Project Child Data When the Page Needs It

A detail response may need line-item values rather than just a count. A nested projection can describe those values:

var details = await db.Orders
    .Where(order => order.CustomerId == customerId)
    .OrderByDescending(order => order.Id)
    .Take(pageSize)
    .Select(order => new
    {
        order.Id,
        order.CreatedAt,
        Items = order.Items
            .OrderBy(item => item.Id)
            .Select(item => new
            {
                item.Id,
                item.ProductName,
                item.Quantity
            })
            .ToList()
    })
    .ToListAsync(cancellationToken);

The nested ToList here is part of the expression EF Core translates; it is not a separate application loop issuing queries manually. The exact command shape remains provider- and version-dependent.

Inspect generated commands and measure payload size. A projection can still return a large number of child rows if the page includes large orders.

If child collections can be very large, consider separate child pagination or a dedicated detail endpoint. A parent page size of fifty does not bound the number of included items to fifty.

A deliberate response model makes those limits easier to discuss. It also avoids accidentally exposing entity properties that were never intended to be part of the API.

Use Eager Loading When Entities Are Required

Sometimes the application genuinely needs related entities, for example to perform a domain operation involving the order and its items.

Eager loading makes the relationship requirement explicit:

var orders = await db.Orders
    .Where(order => order.CustomerId == customerId)
    .OrderByDescending(order => order.Id)
    .Take(pageSize)
    .Include(order => order.Items)
    .ToListAsync(cancellationToken);

Later access to those loaded collections does not need an individual lazy-loading query for each order.

However, loading entities usually transfers more columns and may incur tracking overhead. If the endpoint is read-only and only needs a few values, projection can remain the clearer choice.

Nested relationships use ThenInclude when appropriate. Every added relationship should correspond to a real requirement, because it changes row counts and payload shape.

Microsoft's eager loading documentation covers relationship loading and filtered includes.

Avoid adding broad includes to a shared repository method to fix one page. That can make every other caller load the same expensive graph. Query-specific methods or projections keep the cost close to the consumer that requires it.

Avoid Replacing N+1 With a Huge Join

Including several sibling collections can produce a multiplicative result. Suppose an order has ten items and four payment attempts. Joining both collections can produce forty combinations for that order.

The database and EF Core can still reconstruct the correct object graph, but repeated parent and child columns increase transferred rows and processing.

A large text or binary column on the parent can make duplication especially expensive. Excluding that column with a projection may help even when command count is already low.

This is why “one SQL query” is not a universal optimisation target. One enormous joined result can be slower than a few well-designed commands.

Inspect actual rows, not just the number of resulting order objects. Fifty materialised orders might have required thousands of joined rows.

The practical question is whether the chosen shape minimises unnecessary work while meeting consistency and data requirements. Command count is a clue, not a complete performance model.

Choose Split Queries With Clear Trade-Offs

AsSplitQuery lets EF Core fetch included collections through separate queries:

var orders = await db.Orders
    .Where(order => order.CustomerId == customerId)
    .OrderByDescending(order => order.Id)
    .Take(pageSize)
    .Include(order => order.Items)
    .Include(order => order.PaymentAttempts)
    .AsSplitQuery()
    .ToListAsync(cancellationToken);

The number of commands is driven by the included collection shape, rather than issuing another query for every parent. The exact generated commands depend on the query and provider.

This can reduce duplication from sibling collection joins. It also introduces additional round trips and can require buffering.

There is a consistency trade-off because data can change between commands. An order might be read before another transaction adds or removes an item.

If the operation requires a consistent snapshot across those reads, investigate an appropriate transaction isolation level for the database. That choice can have locking or version-storage costs and should reflect the actual requirement.

Microsoft's single and split query guidance explains these trade-offs. Neither mode should become an unexamined global cure.

Keep Pagination Ordering Fully Unique

Pagination should use a deterministic, fully unique order. Sorting only by creation time leaves ties that can return inconsistent page boundaries.

For example, order by CreatedAt and then by Id. On EF Core versions before 10, unique ordering is particularly important when split queries combine Skip or Take, because separate queries can otherwise select different tied rows.

Even where a newer version handles a specific translation differently, stable ordering remains important for understandable pagination.

For large sequential browsing, keyset pagination can avoid some costs of large offsets. The continuation condition must use the same ordering fields as the query.

Do not change pagination semantics accidentally while fixing N+1. Verify which records appear on neighbouring pages, especially when several records share a timestamp.

Use Explicit Batching When It Fits the Boundary

Sometimes a repository or external API makes a direct nested projection awkward. A deliberate two-stage load can still avoid N+1.

First fetch a bounded page of parent identifiers and required parent data. Then fetch child rows for that set of identifiers in one batched query and group them in memory.

var orderIds = page.Select(order => order.Id).ToArray();

var items = await db.OrderItems
    .Where(item => orderIds.Contains(item.OrderId))
    .Select(item => new
    {
        item.OrderId,
        item.Id,
        item.ProductName,
        item.Quantity
    })
    .ToListAsync(cancellationToken);

var itemsByOrder = items.ToLookup(item => item.OrderId);

This keeps command count bounded for the page and makes the relationship assembly explicit. Preserve the parent page's ordering when building the response.

Handle an empty page without unnecessary database work. Keep batches within sensible limits because providers and databases have parameter, statement-size and translation constraints.

For a very large identifier set, use bounded chunks or an appropriate provider-specific bulk input mechanism. Splitting into one-element chunks simply recreates N+1.

The two queries can observe changes between reads, just like other multi-command approaches. Consider whether that is acceptable for the endpoint.

Authorisation must remain intact across both stages. Deriving child identifiers from an already authorised parent page is useful, but do not allow a separate untrusted input to bypass the boundary.

Understand Lazy Loading and Serialisation

Lazy loading can make local object code convenient, but it moves database access into property access. That reduces the visibility of performance costs during review.

A serializer, mapper or template may traverse relationships the handler never explicitly requested. It can also follow cycles or large graphs unless the response shape is controlled.

Returning entity objects from an API therefore couples the response to persistence behaviour. A small response contract change may suddenly cause additional database commands.

Microsoft's lazy loading documentation describes the supported mechanisms and warns about extra round trips.

You do not necessarily need to remove lazy loading from an entire application to fix one endpoint. Project that endpoint into a DTO and make its query boundary explicit.

If the team decides to disable lazy loading more broadly, verify correctness as well as speed. Code that relied on automatic loading may now see unloaded collections and produce incomplete results.

A missing query is only a performance improvement when the response still contains the intended information.

Know What AsNoTracking Can and Cannot Fix

AsNoTracking can reduce change-tracking work for read-only queries. It does not remove a query issued inside a loop.

An endpoint can execute fifty-one commands with or without tracking. The round-trip pattern remains until the data access shape changes.

Tracking can also make diagnosis confusing because relationship fix-up may populate navigations from previously loaded entities. A repeated test inside one context can appear faster for reasons that will not hold for a fresh request.

Filtered includes have related caveats in tracking queries: previously tracked entities can affect what appears in a navigation. Use a fresh context or an appropriate no-tracking query when evaluating the exact filtered result.

No-tracking with identity resolution is another available behaviour when duplicate entity identity matters without normal context tracking. Choose it for that requirement, not as an assumed solution to N+1.

Keep the fixes separate in measurement. First remove repeated queries, then assess tracking and allocation overhead if they remain material.

Avoid Hiding the Problem With Concurrency or Caching

Launching child queries concurrently can reduce one request's elapsed time while leaving the database demand unchanged. It can even make pressure worse by sending the commands in a burst.

Running concurrent operations on one DbContext is unsupported. Creating separate contexts avoids that particular violation but still does not repair the N+1 access pattern.

Caching may hide repeated reads for popular records, but cold-cache requests and cache expiry still expose the original design. Invalidation and memory costs add another set of concerns.

Compiled queries do not remove round trips either. They address a different part of query overhead and should follow evidence that this overhead matters.

Increasing command timeout merely allows the slow pattern to continue longer. It is a mitigation for a different question, not a structural fix.

Improve the access pattern first. Then evaluate caching, query compilation or other tuning against the remaining bottleneck.

Inspect Indexes and Execution Plans After Shaping

A projection can remove dozens of commands while leaving one inefficient query. The database may still scan many rows to find one customer's recent orders.

Review indexes that support filtering, joins and ordering. An index suited to CustomerId and the pagination order may matter more than a generic index added without examining the query.

Child counts and joins need efficient access to their foreign keys. Check the actual schema rather than assuming the desired index exists because the model has a relationship.

Execution plans help distinguish a good query shape with missing support from a fundamentally overbroad query. Provider-specific tools show row estimates, scans, joins and other database work.

Do not prescribe a universal index definition without considering write cost and existing indexes. Every additional index must be maintained when data changes.

After an index change, repeat the endpoint measurement. The useful outcome is lower total cost under the real access pattern, not merely seeing an index name in a plan.

Work Through an Order History Investigation

Suppose the endpoint returns twenty orders. Logs show one parent query and twenty similarly shaped item queries. Each item query is quick, but request latency rises noticeably when the page size increases.

Trace the command timing through the request. The repeated commands occur during a mapper that reads order.Items.Count after the parent list is materialised.

The response only needs item counts. Replace entity loading and mapper access with a database projection containing the count. Preserve customer filtering and the original stable sort.

Repeat the same requests with a fresh context. Command count should no longer grow by one for every order. Confirm this from actual logs rather than assuming the LINQ change translated as intended.

Now compare response values for orders with zero, one and many items. Include a record that another user must not see. Performance and correctness belong in the same verification.

If latency remains high, inspect the remaining SQL plan and payload. The original N+1 problem may be solved while a missing index or another dependency still dominates the request.

Add a Meaningful Regression Check

A useful regression test measures the endpoint's database command pattern using the actual relational provider or a sufficiently representative test database.

An in-memory substitute cannot validate SQL translation, joins or round trips in the same way. Microsoft’s testing strategy guidance explains important differences between test doubles and database-backed tests.

Seed multiple parents and varied child counts. Execute the complete response path so mapping and serialisation are included.

Assert the intended contract and a reasonable command budget. Where exact command count depends on provider or split-query configuration, test that increasing parent count does not introduce one additional child command per parent.

Avoid making the test fail because an unrelated health check or background task issued a query. Scope the interceptor or tracing measurement to the operation under test.

Keep a realistic performance check separate from a deterministic correctness test. Exact millisecond assertions are often fragile, while bounded command growth and correct results are stable properties.

Monitor for Recurrence

Record request latency alongside database command counts for important endpoints where the observability tooling supports it. A rising count can reveal a regression before a single query becomes noticeably slow.

Use low-cardinality operation names for metrics. Request identifiers and customer identifiers belong in appropriately controlled traces or logs, not unbounded metric labels.

Review DTO changes that add related information. A new “latest payment status” field can be implemented as part of the query or as a hidden repository call in every row mapper; the difference deserves attention.

Keep diagnostic examples in the team’s review guidance. Developers are more likely to recognise N+1 when they know that a navigation property, serializer or repository loop can trigger it.

Performance ownership should follow the endpoint as it evolves. A fixed query can become expensive again when page size, included relationships or consumer requirements change.

Review Query Boundaries When Requirements Grow

A common regression begins with an innocent new column. The order page gains a supplier name, latest payment status or delivery estimate, and the existing mapper obtains it through a repository call for each row.

Treat that change as a query design decision. Determine whether the new value belongs in the existing projection, a bounded secondary query or a separate endpoint whose cost is visible to the caller.

Ask whether the value is needed for every row immediately. A large optional detail panel may be better loaded when opened, provided that interaction has a clear request contract and does not itself repeat one request per nested item.

Keep performance examples representative as the feature changes. If the page now displays payments as well as items, seed realistic variation in both collections. Otherwise a former single-collection test can miss the row multiplication introduced by the new requirement.

These reviews help preserve the original improvement. The application should have one understandable place where the response's database needs are expressed, rather than accumulating hidden data access in formatters, templates and serialisers.

Summary

Diagnose N+1 by counting database commands across a complete request and checking how that count changes as parent records increase.

Project summary data in the database, load related entities deliberately, or use bounded batching where it fits the boundary. Choose single or split queries according to payload shape, latency and consistency requirements.

Verify the returned data, access controls, command growth and end-to-end performance together. The best fix creates a predictable database access pattern that serves the page's actual needs.