/optimizing-ef-core-queries

name: optimizing-ef-core-queries
description: "Review and optimize application data access that uses Entity Framework Core (EF Core). Use for EF Core performance and correctness reviews of services, repositories, imports, and background jobs: slow or memory-heavy queries, excessive round-trips, hidden SaveChanges, premature materialization, dynamic query construction, result-graph tracking, bulk writes, DbContext lifetime or overlapping operations, search semantics, paging, raw SQL, and model/index coverage. Use whether or not EF Core owns the schema. For EF Core, not Dapper or raw ADO.NET."
license: MIT

Optimizing EF Core Queries

Diagnose and fix slow or incorrect application data access built on EF Core. In a source-only review, trace the code without executing it and separate proven code behavior from runtime assumptions. When SQL, logs, or plans are available, use them to confirm the bottleneck. Prefer changes that reduce round-trips, duplicated rows, scans, or per-call translation cost over micro-optimizations.

When to Use

• EF Core queries are slow or emit far more SQL statements than expected
• The same query repeats once per row (N+1 / lazy loading)
• Multiple collection `Include`s blow up or duplicate rows
• Deep pages slow down as `Skip` grows, or bulk updates load rows just to modify them
• A filtered/sorted query scans **even though the column is indexed**, or a filtered/sorted column has no supporting index
• A hot, frequently-executed query pays EF Core's LINQ-translation cost on every call

When Not to Use

• **The code uses Dapper or raw ADO.NET, not EF Core.** Answer the SQL/indexing/query-plan question directly; do not introduce a `DbContext` or recommend `AsNoTracking`, `Include`, `AsSplitQuery`, or other EF Core APIs.

Source review workflow

1. Find the EF boundary: `DbContext`, `DbSet`, repository implementation, unit of work, query helper, or model-building code. Follow the implementation rather than assuming what an interface method does. 2. Trace each query to execution or enumeration. Check whether operators bind to `Queryable` or `Enumerable`, including inside helpers; an `IEnumerable` can remain a deferred database sequence. 3. Count database operations and actual transaction boundaries along the calling path, including immediate writes and repeated enumeration. Also track which operations share the same `DbContext` instance 4. For every execution path, independently inspect predicates, expression construction, projection contents, result bounds, ordering, Includes, tracking, SQL parameterization, and model-building code. 5. Preserve the contract: result membership and order, random-page access, generated keys, retries, transaction and partial-failure behavior, concurrency handling, and domain/interceptor side effects. 6. Report prioritized findings with locations, the affected call path, concrete impact, and a safe remedy. Briefly identify sound counterparts and state what was not validated. Do not equate source reasoning with measured plans or speedups.

For a source-only request, recommend runtime validation as a next step.

For raw SQL, prefer EF Core's interpolated overloads (`FromSql` / `FromSqlInterpolated` and `ExecuteSql[Async]`) so values are sent as parameters.

Gate source-only findings on evidence

When the user requests a source-only review, distinguish a demonstrated defect from an optimization that still needs workload or deployment evidence:

• A load-mutate loop followed by one `SaveChanges[Async]` is not `SaveChanges` in a loop. Do not call it a per-row round-trip defect; suggest a bulk API only when the operation is demonstrably large and bypassing tracking semantics is safe.
• Missing `HasIndex` calls in one supplied file do not prove that the deployed database lacks indexes. Use relevant model-building code, migrations, or a query plan as evidence. Do not pad a source review with generic conditional index advice when that evidence is absent.
• Missing `AsNoTracking` is actionable only on proven read-only entity results. Scalar-only projections have nothing to track, but an entity inside a DTO/record is still tracked by default.
• If every reviewed pattern is sound, say that no actionable changes are needed rather than manufacturing a finding.

Capture SQL and query counts

Use supplied logs and plans first. For an executable investigation, enable command logging to verify query shape and count before and after a change:

optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);
// or set "Microsoft.EntityFrameworkCore.Database.Command": "Information" in appsettings.json

Tag a query with `.TagWith("...")` to find it in the log. Count how many statements a slow operation runs, and how many rows each returns, before and after each change.

Fixes

Trace materialization and result bounds

Materialization inside a helper is still an execution boundary. A list or array has already loaded rows; later reductions run in memory. An `IEnumerable<T>` can instead hide a deferred EF query: its `Enumerable` operators process rows locally, and each enumeration may execute the database query again. Trace implementation and static types, not just method names. Keep translatable filters, projections, and aggregates on `IQueryable<T>` until execution:

// Loads rows, then filters in memory.
var candidates = await repository.LoadCandidatesAsync(cancellationToken);
return candidates.Where(o => o.CreatedAt < cutoff).ToList();

// Composes and filters in SQL.
return await repository.QueryCandidates()
    .Where(o => o.CreatedAt < cutoff)
    .ToListAsync(cancellationToken);

Use `AnyAsync` for boolean existence, but preserve `CountAsync` when the numeric total is required. Combine repeated full-sequence summary calculations into server-side aggregates rather than caching an unbounded list or parallelizing queries on one context.

Separately review result cardinality. Page or cap interactive res