EF Core Query Performance and N+1 Diagnosis

Start with evidence, not folklore

EF Core performance interviews test whether you can turn a slow endpoint into a measured diagnosis. Use AsNoTracking is not a complete answer. You need to identify query count, SQL shape, rows read, payload size, database plan, and application materialization cost.

Recognizing N+1 queries

N+1 occurs when code loads a list and then issues one additional query per item, often through explicit or lazy loading.

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

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);
}

If lazy loading is enabled and Customer was not included, accessing Customer can produce one query per order. The visible symptom is usually many nearly identical commands with different parameter values.

Instrument first

Enable command logging in a safe environment and use structured observability in production. Record request route, trace ID, command duration, and a normalized query identifier. Do not log sensitive parameter values by default. Add a DbCommandInterceptor or OpenTelemetry instrumentation to count database commands per request. A sudden endpoint query-count increase is a stronger N+1 signal than a single slow query.

During local diagnosis, call ToQueryString() on an IQueryable to inspect generated SQL without executing it. In a profiler, look for repeated commands. At the database, inspect the actual execution plan and logical reads for the representative query.

Projection is usually the default read model

If the endpoint returns a summary, project only required columns.

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

Projection often translates navigation access and aggregates into one SQL query. It avoids materializing full entities and large unused columns. Verify the generated SQL because translation details depend on provider and model.

Include is not a universal fix

Include eagerly loads related entities, but including several collections can create a cartesian explosion. If an order has ten items and five adjustments, a single joined result can repeat order data fifty times. The database may return far more rows than the object graph contains.

AsSplitQuery asks EF Core to load included collections using multiple SQL queries. It can reduce duplicated rows but adds round trips and has consistency considerations if data changes between queries. Use it deliberately and measure. AsSingleQuery preserves one round trip but can be expensive for wide multi-collection graphs.

For API read models, projection often beats both Include variants. Use Include when you genuinely need a tracked entity graph for domain behavior or updates.

Tracking choices

Tracking allows SaveChanges to detect modifications and provides identity resolution. It costs memory and CPU. Use AsNoTracking for read-only queries. If a no-tracking graph contains repeated entity references and you need reference identity, AsNoTrackingWithIdentityResolution can help, but it has its own allocation cost.

Do not add AsNoTracking to a query whose entities will be modified later without understanding attach/update semantics. Performance advice must preserve correctness.

Pagination

Offset pagination with Skip and Take becomes expensive at large offsets because the database still locates and discards earlier rows. Prefer keyset pagination for feeds and stable ordered lists.

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

The ordering must be unique and the cursor must include every ordering component. Add a matching composite index.

Indexes and query shape

An ORM cannot compensate for a missing index. Derive indexes from actual predicates, joins, and ordering. Avoid wrapping indexed columns in functions when it prevents index seeking. Inspect parameterization and cardinality estimates. When performance depends on a filtered or provider-specific index, manage it explicitly in migrations and document the workload it supports.

Compiled queries

EF Core caches query compilation internally. Explicit compiled queries can help only for very hot, stable query shapes where compilation overhead is measurable. They do not fix slow SQL, excessive rows, missing indexes, or network latency. Benchmark before adding complexity.

Client evaluation and premature materialization

Calling ToList too early moves later filtering to memory and may load an entire table. Keep the expression as IQueryable until the final terminal operation. Use IEnumerable only when you intentionally cross into in-memory processing and the bounded dataset is understood.

var query = db.Orders.Where(o => o.Status == status);
if (minimumTotal is not null)
    query = query.Where(o => o.Total >= minimumTotal);

var result = await query
    .OrderByDescending(o => o.CreatedAt)
    .Select(o => new OrderSummary(o.Id, o.Customer.Name, o.Total))
    .Take(100)
    .AsNoTracking()
    .ToListAsync(token);

Write-path concerns

Do not run multiple parallel operations on the same DbContext; it is not thread-safe. For bulk imports, batch work, keep change tracking bounded, and consider provider-supported set-based ExecuteUpdate/ExecuteDelete operations when domain rules allow. SaveChanges in a loop usually creates avoidable round trips and fragmented transactions.

Diagnosis playbook

1. Reproduce with representative data volume.
2. Capture query count and normalized SQL.
3. Measure command time, rows returned, and response size.
4. Inspect generated SQL and the actual database plan.
5. Decide whether projection, Include, or split queries match the required shape.
6. Add or correct indexes based on predicates and ordering.
7. Bound results with keyset pagination when appropriate.
8. Load test the endpoint and compare p50, p95, and database reads.
9. Add a regression test or telemetry threshold for command count.

Interview scenario

A dashboard endpoint is fast with ten customers but times out with ten thousand. It loads customers, their latest five orders, and total spend. A strong answer first verifies whether one query is issued per customer, then proposes a set-based projection or window-function-capable query, bounds the result, checks indexes, and compares SQL plans. It also asks whether the dashboard needs all customers synchronously or a precomputed aggregate.

Answer pattern

State the measurement plan before naming optimizations. Explain the desired data shape, generated SQL, tracking requirement, index, and pagination strategy. Close with the regression signal that will stop N+1 from returning.