Querying Efficiently with EF Core: Tracking, Projections, and N+1

The bug report says the tasks page is slow “when there are a lot of projects”, and it loads instantly with the test data. The log holds the answer: one query for the projects, then a query for tasks per project, fifty-one round trips where one would do. Local data is small enough that N+1 hides. Production data is where it bills you. The fix is usually a one-line change that was visible in the SQL all along.

The EF Core N+1 problem and its siblings are what this page covers: what the pattern is, how to find it in logs rather than hunches, and the tool set for each shape of slow query, from tracking overhead to cartesian explosions to unbounded lists. The queries build on the model from the EF Core code-first guide and the relationships from the relationships guide.

The examples assume .NET SDK 10.0 with EF Core 10. One idea carries the page: LINQ over DbSet is SQL until it runs. You can always ask to see it, the translation rules being the IQueryable side of the LINQ guide’s in-memory/translated split.

Several query-tuning sessions that started with guesses and ended with logs taught me this: EF Core performance problems are almost never mysteries. The framework prints the SQL. Slow queries announce themselves in counts and shapes, and every fix below maps to a specific pattern in that output.

What the EF Core N+1 problem looks like

The pattern has two halves: a query that loads a list, then code that touches a navigation property on each item, which the context fills with one extra query per row:

WRONG: fifty-one queries.

var projects = await context.Projects.ToListAsync(ct);

foreach (var project in projects)
{
    // one SQL query PER project, triggered by the navigation access
    Console.WriteLine($"{project.Name}: {project.Tasks.Count} tasks");
}

query 1:  SELECT ... FROM "Projects"
query 2:  SELECT ... FROM "Tasks" WHERE "ProjectId" = @p0
query 3:  SELECT ... FROM "Tasks" WHERE "ProjectId" = @p1
...
query 51: SELECT ... FROM "Tasks" WHERE "ProjectId" = @p49

The count is what makes it dangerous: with N projects it is N+1 queries. The database round trip, not the row count, dominates the cost. The same shape appears with lazy loading, with .Count() in a loop, and with any navigation access hidden inside a method called per row.

RIGHT: one query, with the relationship stated up front.

var projects = await context.Projects
    .Include(p => p.Tasks)
    .ToListAsync(ct);

The SQL becomes one statement joining the two tables (or two statements with split queries, covered below). The fix is not tuning; it is telling EF Core what you will need before you start iterating.

Finding N+1 in your logs, not your guesses

You do not profile blind. EF Core logs every command it sends, and the switch is one line in development configuration:

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

From then on, every request prints its SQL with parameters. Reading the log is the diagnosis skill: a healthy endpoint has a small, stable number of statements. An N+1 endpoint has the same statement shape repeating with incrementing parameters. In code, the same switch is optionsBuilder.LogTo(Console.WriteLine) on the context, and request-scoped log enrichment, the pattern from the Serilog guide, ties each logged query to the request that caused it. Teams that instrument this once stop arguing about which endpoint is slow.

Include vs projection: choose your shape

Include fixes N+1 by loading whole related entities, and it is the right tool when you will actually work with those entities. The better tool, most of the time, is a Select projection: state exactly the columns the response needs. EF Core generates SQL that fetches only those:

var rows = await context.Projects
    .Select(p => new ProjectSummary(
        p.Id,
        p.Name,
        p.Tasks.Count))          // becomes a COUNT in SQL, not a loop
    .ToListAsync(ct);

public record ProjectSummary(Guid Id, string Name, int TaskCount);

SELECT "p"."Id", "p"."Name", (
    SELECT COUNT(*) FROM "Tasks" "t" WHERE "t"."ProjectId" = "p"."Id")
FROM "Projects" "p"

The projection version loads no tracked entities, transfers only the needed columns, and computes the count inside the database. The eager loading documentation covers Include in full depth. The working rule is: entities you will modify come back tracked via Include, everything a response needs comes back through a projection.

Include vs split queries

One Include joins cleanly, but several collections in one query produce a cartesian product: projects, each with tasks and each with tags, multiplies rows in a single result set. EF Core’s answer is AsSplitQuery, which sends one statement per included collection instead of one giant duplicated JOIN:

var projects = await context.Projects
    .Include(p => p.Tasks)
        .ThenInclude(t => t.Tags)
    .AsSplitQuery()               // one SELECT per collection
    .ToListAsync(ct);

The trade-off is round trips versus row duplication, and the single and split query documentation works through it with examples. For a projection-based API, the same problem disappears earlier: projections rarely need the cartesian shape at all.

Tracking vs AsNoTracking

By default, every entity a query returns is tracked. The context keeps a copy of its values so SaveChangesAsync can write back the differences. For read-only endpoints that is pure overhead, and AsNoTracking switches it off:

var projects = await context.Projects
    .AsNoTracking()
    .ToListAsync(ct);

The tracking documentation covers the semantics in depth. The common team habit: read-side queries and projections skip tracking (projections already skip it), write-side workflows use tracked entities deliberately.

Pagination: lists never grow forever

Every list endpoint gets Skip/Take, and every paginated query gets an explicit OrderBy, because paging without a stable order returns overlapping or missing rows between pages:

int page = 0, size = 20;

var tasks = await context.Tasks
    .OrderBy(t => t.CreatedAtUtc)
    .ThenBy(t => t.Id)              // tiebreaker makes order stable
    .Skip(page * size)
    .Take(size)
    .ToListAsync(ct);

Skip/Take translates to SQL OFFSET/LIMIT, and the page count endpoint, if you need one, runs a separate CountAsync. Deep pages (page 10,000) get slower as the database skips rows. High-volume feeds move to keyset pagination, filtering on “older than the last seen id and timestamp”, which stays constant-cost per page.

ExecuteUpdate and ExecuteDelete: bulk changes without loading

Marking every task in a project done used to mean loading them all, mutating each, and saving. Since EF Core 7, ExecuteUpdate sends one UPDATE, and ExecuteDelete sends one DELETE, with no tracked entities and no round trip per row:

await context.Tasks
    .Where(t => t.ProjectId == projectId)
    .ExecuteUpdateAsync(s => s.SetProperty(t => t.Done, true), ct);

// one SQL statement:
// UPDATE "Tasks" SET "Done" = 1 WHERE "ProjectId" = @p0

Two caveats: these statements run immediately against the database (they bypass the change tracker, so pending in-memory changes to those rows are not included), and no cascade rules loaded from EF Core apply to rows it never saw. The database’s own foreign key rules still apply.

How real systems do this

• SQL logging on in development and staging, query counts alarmed in production. The log level from the diagnostic section stays on outside production. In production, APM query counters and slow-query alerts catch the same shapes the logs show locally.
• Projections at the read boundary, tracked loads inside the write path. List and detail endpoints project into response records. Commands load tracked entities, mutate, and save. The two styles rarely mix inside one method.
• Reviews count the queries. For a changed endpoint, reviewers ask how many statements it should send. A quick local run with command logging answers it, and regressions stop at the pull request.

A case I remember well: an endpoint passed every local test at 40 projects and 200 tasks, and timed out at a customer with 40,000 tasks. This is because a per-task Count query hid inside a formatting helper three layers deep. Nothing in the code looked like a query; the log made it fifty thousand identical SELECTs in one request. The fix was one Include. The lesson: performance bugs hide in helpers that dereference navigations, and the only real test data is data shaped like production.

Decision framework: which tool for which slowness

1. Repeated statements with incrementing parameters? N+1: state the relationship up front with Include or a projection.
2. Is the result read-only? AsNoTracking(), or better, a projection that never tracks at all.
3. Multiple collection Includes in one query? Weigh AsSplitQuery against a projection. If the client only needs counts or names, project and skip the cartesian entirely.
4. Unbounded list endpoint? Order, then Skip/Take; consider keyset pagination once users reach deep pages.
5. Updating many rows without reading them? ExecuteUpdate/ExecuteDelete, remembering they write immediately and skip the change tracker.

When NOT to optimize

• When the log shows a small, stable query count. Endpoints that send one to three statements are not the bottleneck. Optimizing them spends review time where profiling sees nothing.
• When you will modify what you load. AsNoTracking on a load-modify-save workflow forces manual attach and state setting, which is slower to write and easier to get wrong than tracking’s default.
• When the projection becomes a schema of its own. A projection with fifteen columns selected in five places duplicates shape logic. An entity load with tracking is sometimes the simpler correct tool.

Common mistakes

• Touching a navigation in a loop. Any navigation access inside foreach is a candidate N+1. Check the log for statement counts before and after.
• Lazy loading left on. The same N+1, but each query is invisible in the source; keep it off, include deliberately.
• Loading full entities for one or two columns. Select the two columns. The transfer and materialization costs dominate with wide rows and big lists.
• Pagination without ordering. Skip/Take without an OrderBy (plus a tiebreaker) gives pages that shuffle between requests. Stable order is part of the contract.
• SaveChanges inside a loop. Adding and saving per row is one transaction per item. Add all, save once, or move to ExecuteUpdate when no business logic needs the rows.
• Trusting test data. N+1 hides under small datasets. Reproduce counts and relationships at production scale in a local database before declaring a fix complete.

Key takeaways

• N+1 is one list query plus one query per item. The fix is Include or, better, a projection that states the required columns.
• Turn on EF Core command logging in development and read the SQL. Repeated statement shapes with incrementing parameters are the fingerprint.
• Projections give the database only the columns you need, skip tracking, and compute aggregates inside SQL.
• Multiple collection Includes bloat single queries; AsSplitQuery trades one duplicated JOIN for one statement per collection.
• AsNoTracking for read-only results; tracking for load-modify-save; AsNoTrackingWithIdentityResolution when shared instances matter without saving.
• Order before Skip/Take, prefer keyset pagination for deep pages, and use ExecuteUpdate/ExecuteDelete for bulk changes that should not load rows.

FAQ

What is the N+1 problem in EF Core?

One query loads a list of entities, and then one additional query runs per entity when its related data is accessed. So a request performs N+1 database round trips instead of one or two. It appears with lazy loading and with navigation or Count calls inside loops. The fix is to state the relationship up front, with Include or a Select projection.

How do I detect N+1 queries in EF Core?

Log the SQL EF Core sends. Set the logging level for Microsoft.EntityFrameworkCore.Database.Command to Information in appsettings, or call LogTo on the DbContext options. An N+1 pattern shows the same SELECT shape repeating with incrementing parameters. Counting statements per request before and after a change verifies the fix.

When should I use AsNoTracking in EF Core?

For queries whose results you will only read: lists, details, reports, projections. Tracking adds snapshot and comparison overhead that save-never queries do not need. Keep default tracking for load-modify-save workflows, because change detection is the feature you are using there.

What is the difference between Include and split queries?

Include loads related data in one statement, and multiple collection Includes multiply rows into a cartesian result. AsSplitQuery sends one statement per included collection instead, avoiding duplicated data at the cost of extra round trips and slightly weaker consistency between statements. Use single queries for one collection, split queries when several collections bloat the row count.

How do I update many rows at once in EF Core?

Use ExecuteUpdateAsync or ExecuteDeleteAsync, available since EF Core 7. They translate your Where and SetProperty expressions into a single UPDATE or DELETE statement without loading entities. Remember that they run immediately, bypassing the change tracker, so in-memory pending changes to the same rows are not included.

Read the SQL, then fix the count

EF Core performance work is a loop. Turn on command logging, read what a request sends, fix the shape, count again. The tools map one to one onto the patterns, Include and projections for N+1, split queries for cartesian JOINs, AsNoTracking for read-only loads, ordering plus paging for lists, and ExecuteUpdate for bulk writes. The testing track comes next in this series: unit testing in .NET with xUnit, and every guide in the series lives on the .NET series index. The rule of thumb: never argue about slow queries from memory; log them, count them, and fix the count.

Last updated on 13 November 2026