If your ASP.NET Core API feels slow under real traffic even though every individual query looks fine in isolation, there's a good chance you're looking at the N+1 query problem: one initial query to fetch a list of records, followed by a separate database round trip for every related entity you touch afterward. Fix it by replacing lazy-loaded or manually looped queries with eager loading (Include/ThenInclude), targeted projections (Select), or explicit loading — chosen based on what the endpoint actually needs to return.
That's the one-line answer. The rest of this article walks through how a problem like this typically gets found in a production-like system, what the profiling data tends to look like, the tradeoffs between the different fixes, and why the "obvious" fix (just add .Include() everywhere) can quietly introduce a new performance problem called cartesian explosion if you're not careful.
This isn't a toy example with two tables and three rows of seed data. It's the kind of scenario that shows up in a mid-sized order-management or catalog system once you have real relational depth — orders with line items, line items with products, products with categories — and a dashboard endpoint that needs to show all of it at once.
The Symptom: A Slow Dashboard Endpoint
The scenario: an ASP.NET Core Web API exposing GET /api/orders/recent, returning the last 50 orders for a customer dashboard, including each order's line items and the product name for each line item. Functionally it worked. Under light load in a dev environment, it responded in under 200ms. In staging, with a database sized closer to production (tens of thousands of orders, hundreds of thousands of line items), the same endpoint was taking 1.8 to 2.4 seconds per request.
Nothing in the code looked wrong at first glance:
[HttpGet("recent")]
public async Task<IActionResult> GetRecentOrders(int customerId)
{
var orders = await _dbContext.Orders
.Where(o => o.CustomerId == customerId)
.OrderByDescending(o => o.CreatedAt)
.Take(50)
.ToListAsync();
var result = orders.Select(o => new OrderDto
{
OrderId = o.Id,
CreatedAt = o.CreatedAt,
Items = o.LineItems.Select(li => new LineItemDto
{
ProductName = li.Product.Name,
Quantity = li.Quantity,
UnitPrice = li.UnitPrice
}).ToList()
});
return Ok(result);
}This compiles fine and produces correct results. The problem is invisible in the C# — it only shows up once you look at what EF Core is actually sending to SQL Server.
Diagnosing It: Turning On Logging Before Touching Any Code
Before changing anything, we needed evidence, not a guess. The fastest way to see what EF Core is actually doing is to enable query logging in the DbContext configuration and watch the console or log output while hitting the endpoint locally against a realistically sized dataset.
services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(connectionString)
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging()); // dev/staging only, never productionEnableSensitiveDataLogging includes actual parameter values in the log output, which is useful for debugging but should never be turned on in a production environment since it can leak customer data into logs.
Hitting the endpoint once and counting the queries in the log told the whole story: one query for the 50 orders, then one query per order to load its LineItems (50 queries), and then, because Product.Name was also being accessed lazily, one query per line item to load its Product. With roughly 4–6 line items per order, this particular dataset resulted in hundreds of SQL commands for a single API request — the exact number depends on how many related rows exist and how the relationships are configured, but the endpoint needs related data spanning orders, line items, and products, and was instead issuing a separate round trip for nearly every row involved.
Another useful diagnostic tool here is ToQueryString(), which lets you print the actual SQL EF Core generates for a given IQueryable without executing it — handy for confirming exactly what a specific line of LINQ compiles to before you run it against a real database:
var query = _dbContext.Orders.Where(o => o.CustomerId == customerId);
Console.WriteLine(query.ToQueryString());In a system with proper observability, you wouldn't necessarily catch this from console logging — you'd more likely see it first in an APM tool like Application Insights or a profiler like MiniProfiler, where a single API request shows up with an unusually high SQL call count and each individual query is fast, but the cumulative time is not. That pattern — many fast queries, one slow request — is close to a fingerprint for N+1. If you're triaging a live production incident, that's the signal to look for in your telemetry before you start reading code.
Why This Happens: Lazy Loading and Deferred Execution
The root cause was lazy loading — a feature where navigation properties (like Order.LineItems or LineItem.Product) are only loaded from the database the moment your code actually accesses them, not when the parent entity is first queried. EF Core supports lazy loading through proxy objects, but it has to be explicitly enabled, usually via the Microsoft.EntityFrameworkCore.Proxies package and a UseLazyLoadingProxies() call, combined with virtual navigation properties on your entity classes.
The orders.Select(...) block in the original code looks harmless because it's just LINQ-to-Objects running over an in-memory list at that point — the orders themselves were already loaded by ToListAsync(). But every time that projection touches o.LineItems or li.Product, it triggers a brand-new database query against the still-attached DbContext, because those properties weren't part of the original query. Multiply that by 50 orders and several line items each, and you get the flood of queries we saw in the log.
This is the core mechanic behind N+1: one query to get a list (the "1"), and then N additional queries triggered by iterating over that list and touching related data that was never eagerly fetched.
Fix #1: Eager Loading with Include and ThenInclude
The first and most direct fix is eager loading — telling EF Core up front, as part of the original query, to fetch related entities as part of the same LINQ query, using Include for a direct navigation property and ThenInclude for a navigation property one level deeper.
var orders = await _dbContext.Orders
.Where(o => o.CustomerId == customerId)
.OrderByDescending(o => o.CreatedAt)
.Take(50)
.Include(o => o.LineItems)
.ThenInclude(li => li.Product)
.ToListAsync();By default, EF Core uses a single-query loading strategy for this shape, translating the eager-loaded relationships into one SQL statement that retrieves orders, their line items, and each line item's product together. This alone took the query count from hundreds down to one, and cut per-request latency dramatically in our staging environment because the database no longer had to service hundreds of separate connections and query plans per API call.
There's a catch here that a lot of tutorials skip: the classic cartesian-explosion problem occurs when a query includes multiple sibling collection navigations off the same parent. For example, if an order has both LineItems and Notes, a single SQL query joining both collections can multiply the number of rows returned. If an order has 6 line items and 4 notes, the joined result can contain roughly 24 rows for that order, because each line item row gets combined with each note row. As the number of child rows on either side grows, the duplicated data in the result set can become substantial.
Our example has a different shape: Order → LineItems → Product. That's one collection navigation (LineItems) followed by a reference navigation (Product — many-to-one from the line item's perspective), not two sibling collections, so it doesn't create the classic cross-product explosion; each line item row just gets its product columns joined on, without additional row duplication. If the object graph later grows to include multiple sibling collections — tags, notes, shipments, and so on, all hanging off the same order — that's when it's worth inspecting the generated SQL and considering AsSplitQuery().
Fix #2: Query Splitting with AsSplitQuery for Deeper Graphs
AsSplitQuery() tells EF Core to translate a query with multiple Include calls into several separate SQL queries instead of one big join, avoiding the row multiplication problem entirely at the cost of multiple round trips.
var orders = await _dbContext.Orders
.Where(o => o.CustomerId == customerId)
.OrderByDescending(o => o.CreatedAt)
.Take(50)
.Include(o => o.LineItems)
.ThenInclude(li => li.Product)
.Include(o => o.Notes)
.AsSplitQuery()
.ToListAsync();This generates one query for orders with their line items and products, and a second query for the notes collection, joining them back together in memory by primary key. This trades "one query with potentially inflated row counts" for "a few queries with clean, non-duplicated row counts." For our dashboard endpoint specifically, we didn't need AsSplitQuery because we only had a single collection navigation (LineItems) with a reference navigation off of it — no sibling collections — but it's worth knowing about before you reach for it reflexively on every Include, and it's genuinely useful once your object graph has more than one collection navigation hanging off the same parent.
The general guidance we settled on: a single Include/ThenInclude chain with no sibling collection navigations is usually fine as one query. The moment you're including two or more separate one-to-many collections off the same root entity, measure both approaches, because the right answer depends on your actual data shape — how many child rows per parent, on average — not on a blanket rule.
Fix #3: Projection with Select to Avoid Overfetching Entirely
Eager loading solved the round-trip problem, but it still pulled full Order, LineItem, and Product entity objects into memory, which adds CPU and memory overhead for data that, in a read-only dashboard endpoint, was never going to be modified or saved back. Since the DTO only needed a handful of scalar fields across two levels of nesting, we replaced the Include chain with a direct projection:
var result = await _dbContext.Orders
.Where(o => o.CustomerId == customerId)
.OrderByDescending(o => o.CreatedAt)
.Take(50)
.Select(o => new OrderDto
{
OrderId = o.Id,
CreatedAt = o.CreatedAt,
Items = o.LineItems.Select(li => new LineItemDto
{
ProductName = li.Product.Name,
Quantity = li.Quantity,
UnitPrice = li.UnitPrice
}).ToList()
})
.AsNoTracking()
.ToListAsync();Because the entire shape — including the nested LineItems.Select — is expressed inside a single Select that EF Core can translate to SQL, this compiles down to one query that only selects the specific columns needed, with no separate Include required and no unused columns or entities loaded into memory. Worth calling out directly: since this projection returns OrderDto/LineItemDto instances rather than the underlying Order, LineItem, or Product entity types, EF Core never tracks the results here in the first place — change tracking only applies to entity types that are part of the model, and none appear anywhere in this result set. The AsNoTracking() call is effectively a no-op on this specific query; it's included mainly as an explicit, self-documenting signal that the query is read-only, in case it's later changed to return entities directly. AsNoTracking() earns its keep on queries that materialize actual entities (for example, the Include-based version in Fix #1), where it skips real change-tracking setup work.
Eager loading was the change that produced the largest latency win, since it eliminated the hundreds of extra round trips; the projection step on top of it primarily reduced memory and serialization overhead rather than wall-clock latency, since the API was no longer materializing full entity graphs before serializing exactly the DTO shape it needed to return.
Measured Results
To be clear about what follows: this is an illustrative scenario meant to walk through the diagnostic process and the available fixes, not a benchmark run against a live production system with numbers captured from an actual profiler session.
Eliminating an N+1 pattern can substantially reduce request latency because it removes many individual database commands and their associated network and database overhead. However, the size of the improvement depends on factors such as application-to-database latency, connection pooling, indexes, query execution plans, database workload, result-set size, and the number of related rows involved.
For that reason, query count is a useful diagnostic metric, but it should be considered alongside database execution time, rows returned, CPU usage, memory consumption, and end-to-end request latency. Going from hundreds of SQL commands to one or a few is a strong indication that the N+1 pattern has been addressed, but it does not by itself prove that the resulting query is optimal.
How It Works Under the Hood
EF Core builds queries by translating LINQ expression trees into SQL at the point a query is actually executed (methods like ToListAsync, FirstOrDefaultAsync, or enumeration), a process called deferred execution. Lazy loading defers the loading of navigation properties even further, past the point of the initial query execution, hooking into property getters on proxy classes to trigger new queries on demand. Eager loading and projections both push all the data-fetching logic into the original query's expression tree, so the LINQ-to-SQL translator can generate a single (or a small, predictable number of) SQL statement upfront, rather than discovering additional data needs one property access at a time during in-memory iteration.
Common Errors and Troubleshooting
Assuming `AsNoTracking()` prevents lazy loading. AsNoTracking() is not an N+1 prevention mechanism. On EF Core 7 and earlier, lazy loading wasn't supported at all for no-tracking or detached entities and threw an InvalidOperationException when attempted. As of EF Core 8, Microsoft added explicit support for lazy-loading navigations on no-tracking query results — but whether it actually fires still depends on how lazy loading is configured and on the entity retaining a live reference to the DbContext that ran the query; it stops working once that context is disposed or the entity is detached. Given how version- and configuration-sensitive this is, don't rely on AsNoTracking() as a strategy for controlling N+1 behavior either way — make the related data explicit through projection, eager loading, or explicit loading instead.
Forgetting `ThenInclude` and assuming `Include` cascades automatically. Include(o => o.LineItems) alone will not load LineItem.Product. Each additional level of the graph needs its own ThenInclude, chained off the previous one. A common mistake is calling .Include(o => o.LineItems).Include(li => li.Product) — the second Include there won't compile against Order, because Product isn't a navigation property of Order; it needs to be ThenInclude off the LineItems include.
Assuming more `Include` calls are always faster than N+1. As covered above, stacking multiple Include/ThenInclude chains with several sibling one-to-many collections can produce a cartesian explosion that's slower than a modest number of N+1 queries would have been. If you add an Include and latency gets worse, check the generated SQL with ToQueryString() and look at the row count being returned before assuming the fix made things better.
Best Practices for Preventing N+1 Regressions
Disable lazy loading proxies by default in new projects, and make eager loading or projection an explicit, visible decision in the code rather than something that happens implicitly through property access — this alone prevents most N+1 bugs before they're written. For any endpoint returning a list with related data, write the query as a projection into a DTO whenever the caller doesn't need the full entity graph, since this handles both round-trip count and overfetching in a single fix.
Use AsNoTracking() for read-only queries that materialize entities when change tracking is not required — it can meaningfully reduce change-tracking overhead on those queries, but it is not itself a solution to N+1, and it's generally unnecessary (though harmless) on projections that return only DTOs or scalar values, since those are never tracked regardless.
During development, keep query logging enabled in local and staging environments (never with sensitive data logging in anything resembling production) and periodically eyeball the query count for list-returning endpoints, especially after code review changes that touch LINQ queries. Where your team has the tooling for it, an EF Core interceptor that logs or counts executed commands per request, or an integration test that asserts a maximum query count for a given endpoint, catches N+1 regressions in CI before they reach a customer-facing environment — this is one of the more underused guardrails available and is worth setting up once you've fixed the first instance of this problem, so you don't have to rediscover it in production a second time.
Conclusion
The N+1 query problem in EF Core comes from letting related data load one round trip at a time — usually through lazy loading — instead of describing everything you need up front in a single query. Fixing it comes down to three tools used deliberately: Include/ThenInclude for eager loading full entity graphs, AsSplitQuery when eager loading would otherwise inflate row counts across multiple sibling collections, and Select projections when you only need a subset of fields and no tracked entities at all. In the case study here, the important improvement is the elimination of hundreds of unnecessary database commands rather than any particular millisecond or percentage figure; the targeted projection on top of that reduces the amount of data and entity state that has to be materialized for a read-only endpoint. If you're dealing with this in your own application, start by turning on query logging or checking your APM tool for request-level SQL call counts — that single piece of evidence will tell you more than any amount of guessing at the code.
