EF Core Performance Interview Questions

Q1 How do you detect and fix an N+1 query problem in EF Core?#

Short answer: N+1 happens when code loads a collection of parents and then triggers one additional query per parent — almost always through lazy loading or a missed Include — turning what should be one or two round trips into N+1. You detect it by counting the SQL statements a request actually issues (via logging, an interceptor, or an APM tool) and comparing that count to what the code should produce; you fix it with eager loading or a projection that fetches everything in one shot.

// N+1: one query for blogs, then one query per blog for its posts
var blogs = await context.Blogs.ToListAsync();
foreach (var blog in blogs)
{
    Console.WriteLine(blog.Posts.Count); // lazy-loads here, once per blog
}

// Fixed: a single query with an eager Include
var blogs = await context.Blogs.Include(b => b.Posts).ToListAsync();

In practice, N+1 is rarely this obvious. It usually hides behind a service method that looks innocent in isolation — GetAuthorName(post.AuthorId) called inside a loop over posts — and only becomes visible once you turn on EF Core's simple logging (optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information)) and see the same SELECT ... FROM Authors WHERE Id = @p0 repeated dozens of times with different parameter values. An IDbCommandInterceptor that increments a per-request counter, or an APM tool's dependency-call count on a single trace, catches the same pattern at scale, in production, without reproducing it locally.

What interviewers look for: the ability to describe how you'd find it, not just recite "use Include" — production N+1 bugs are rarely visible in a code diff.

• Common mistakes: fixing one N+1 by adding Include for a single collection, then introducing cartesian explosion by including a second sibling collection on the same query (see the next question).
• Follow-up questions: How would you catch a new N+1 regression in CI before it ships? What's the difference between N+1 from lazy loading and N+1 from calling a repository method inside a loop?

Q2 What is cartesian explosion, and what do split queries cost you in exchange for fixing it?#

Short answer: When you Include two or more sibling collection navigations in a single query, EF Core joins them all against the same query, and relational databases return the cross product of the collections per parent row — ten posts times ten contributors on one blog returns 100 rows for that blog alone. AsSplitQuery() fixes this by issuing one SQL query per included collection instead of one query with multiple joins, at the cost of extra round trips and losing the single-query consistency guarantee.

var blogs = await context.Blogs
    .Include(b => b.Posts)
    .Include(b => b.Contributors)
    .AsSplitQuery()
    .ToListAsync();

Cartesian explosion is specific to sibling collections — Include(b => b.Posts).Include(b => b.Comments) where both hang off Blog directly. A ThenInclude chain (Include(b => b.Posts).ThenInclude(p => p.Comments)) doesn't multiply the same way, because the collections are at different nesting levels. EF Core actually detects the multi-collection-Include pattern automatically and logs a warning if you haven't explicitly chosen AsSplitQuery or AsSingleQuery, precisely because the default (single query, with joins) is the one more likely to surprise you on a large dataset.

The trade-offs with split queries are real, not just theoretical: most databases give you row-level consistency within a single query, but not across several — if a concurrent write lands between your split queries, you can see inconsistent results, unless you wrap the whole call in a serializable or snapshot transaction (which has its own cost). Each split query is an additional network round trip, which hurts more as latency to the database grows. And because most database drivers can only have one active result set at a time, EF Core has to buffer earlier results in memory while executing later ones.

What interviewers look for: the distinction between sibling collections (real cartesian risk) and nested ThenInclude (no cross-product), and an honest accounting of what split queries cost, not just that they exist.

• Follow-up questions: When would you choose data duplication over split queries despite the extra bytes on the wire? How do you guarantee ordering stays correct across split queries when paging with Skip/Take?

Q3 When does a no-tracking query actually hurt performance instead of helping it?#

Short answer: No-tracking queries skip the change tracker entirely, which is usually a clear win for read-only paths, but they also skip identity resolution by default — so a query that returns the same logical entity multiple times (through a join or a collection) materializes a separate object instance every time, which can mean more total allocations than a tracking query would have produced for a result set with heavy duplication.

Tracking queries reuse an existing tracked instance whenever the same primary key comes back again, which is both a memory optimization and a correctness feature (navigation fix-up works because there's exactly one instance per key). No-tracking queries don't do this unless you explicitly ask with AsNoTrackingWithIdentityResolution(), which keeps a standalone, per-query change tracker alive just long enough to deduplicate results, then discards it — cheaper than full tracking, but not free, and still more expensive than plain AsNoTracking().

// Duplicated Blog instances if a blog has many posts
var rows = await context.Blogs.Include(b => b.Posts).AsNoTracking().ToListAsync();

// One Blog instance per unique key, still not tracked by the context afterward
var rows = await context.Blogs.Include(b => b.Posts)
    .AsNoTrackingWithIdentityResolution()
    .ToListAsync();

The practical rule: default to AsNoTracking() for flat, single-entity read queries and for projections; reach for AsNoTrackingWithIdentityResolution() specifically when a read query joins in collections and you'd otherwise pay for (and be confused by) duplicate instances; use tracking queries when you're about to mutate and save.

What interviewers look for: knowing that "always use AsNoTracking for reads" is a rule of thumb, not an absolute — and that identity resolution, not tracking itself, is the thing that changes with duplicated join results.

• Common mistakes: assuming AsNoTracking is strictly faster in every case; forgetting that SaveChanges on a no-tracking-fetched entity requires re-attaching and explicitly marking properties as modified.

Q4 Why are projections often a bigger performance win than tracking mode?#

Short answer: A Select projection lets EF Core generate SQL that only fetches the columns you actually need, avoids materializing full entity graphs, and — critically — avoids the change tracker's overhead entirely, if the projected shape contains no entity instances at all. That last condition is the part developers get wrong most often.

Fetching entire entities pulls every mapped column, including large nvarchar(max) or binary columns you may not need on a list page, and when those large columns sit on the "one" side of a one-to-many Include, they get duplicated once per row on the "many" side. Projecting to a purpose-built shape avoids all of that, and if the projection is a genuine DTO with no entity type in it, EF Core skips change tracking setup entirely, which is faster than even a no-tracking query against the full entity.

The gotcha: EF Core tracks entity types contained anywhere in the result, even inside an anonymous type, even if you also select unrelated scalar fields alongside them.

// Still tracks the Blog instance, because 'b' — the whole entity — is in the projection
var mixed = await context.Blogs.Select(b => new { b.Id, Blog = b }).ToListAsync();

// Not tracked: every member is a scalar value, no entity instance anywhere
var dto = await context.Blogs
    .Select(b => new BlogSummaryDto { Id = b.Id, Name = b.Name, PostCount = b.Posts.Count })
    .ToListAsync();

What interviewers look for: the specific rule that tracking depends on whether an entity instance appears in the result shape, not just whether you called AsNoTracking.

• Follow-up questions: How would you keep a projection DTO in sync with the entity model as it evolves? When would you still want to fetch the full entity instead of projecting?

Q5 How do ExecuteUpdate and ExecuteDelete change the performance profile of bulk writes?#

Short answer: ExecuteUpdate/ExecuteDelete translate a LINQ query directly into a single set-based UPDATE/DELETE statement, executed immediately against the database — bypassing the change tracker, entity materialization, and SaveChanges entirely — which is dramatically cheaper than loading rows, mutating them, and calling SaveChanges for anything beyond a handful of rows.

// Traditional: loads every matching row, tracks it, then issues one DELETE per row
await foreach (var post in context.Posts.Where(p => p.Views < 10).AsAsyncEnumerable())
    context.Posts.Remove(post);
await context.SaveChangesAsync();

// ExecuteDelete: one set-based statement, nothing loaded or tracked
await context.Posts.Where(p => p.Views < 10).ExecuteDeleteAsync();

await context.Posts
    .Where(p => p.Views < 10)
    .ExecuteUpdateAsync(setters => setters.SetProperty(p => p.IsArchived, true));

EF Core 10 improved the ergonomics considerably: ExecuteUpdateAsync now accepts a regular, non-expression lambda for the setters, so you can build the set of property updates conditionally with ordinary if statements instead of hand-assembling an Expression<Func<...>> tree, and ExecuteUpdate gained support for updating properties inside JSON-mapped columns and complex types.

The trade-off that catches teams off guard: because these bypass SaveChanges, none of your ISaveChangesInterceptor audit logic, concurrency-token checks, or in-memory validation runs — a rowversion column won't protect an ExecuteUpdate the way it protects a tracked SaveChanges call, so if you need optimistic concurrency semantics on a bulk operation, you have to encode the check into the Where clause yourself. And ExecuteUpdate currently can't reference navigation properties directly in the setter; you route around that with a Select projection first.

What interviewers look for: understanding that these APIs trade the SaveChanges pipeline's safety net (interceptors, concurrency tokens, change tracking) for raw set-based throughput — and knowing when that trade is worth making.

• Common mistakes: assuming ExecuteUpdate respects concurrency tokens or triggers SaveChanges interceptors automatically.

Q6 What are compiled queries, and in what scenarios do they actually matter?#

Short answer: EF.CompileQuery/EF.CompileAsyncQuery pre-compile a LINQ query into a reusable, thread-safe delegate that bypasses the normal query-cache lookup entirely, shaving a small but measurable amount of per-call overhead — worthwhile only in narrow, extremely hot code paths, not as a general-purpose optimization.

Every EF Core query is already cached by expression-tree shape, so repeated calls to the same LINQ query are fast by default; what a compiled query removes is the cost of walking and comparing the expression tree to find that cached entry in the first place. Microsoft's own benchmarks show roughly a 15-20% reduction in per-call time for a trivial single-row query — real, but small in absolute terms compared to network and database execution time, which usually dominate.

private static readonly Func<AppDbContext, int, Task<Blog?>> GetBlogById =
    EF.CompileAsyncQuery((AppDbContext ctx, int id) => ctx.Blogs.FirstOrDefault(b => b.Id == id));

var blog = await GetBlogById(context, blogId);

Compiled queries have real constraints: they can only be used against a single EF Core model (problematic if you sometimes swap models per tenant), and their parameters must be simple scalars — no member or method access inside the lambda. Given the limitations and the modest gain, they're worth reaching for only when profiling has shown that query-compilation overhead, specifically, is a measurable fraction of a hot path's latency — a tight loop executing thousands of times a second, not a typical web request.

What interviewers look for: calibrated judgment — recognizing this as a micro-optimization with real limitations, not a default recommendation.

Q7 How does DbContext pooling improve throughput, and what bugs does it introduce?#

Short answer: AddDbContextPool reuses DbContext instances instead of constructing and disposing a new one per request, avoiding the setup cost of internal services each context normally pays — Microsoft's own benchmark shows roughly a 2x improvement for a simple single-row query (about 700 microseconds down to about 350). The catch is that a pooled context behaves like a singleton between requests, so any state you set up once and expect to vary per request silently leaks across requests unless you handle it deliberately.

builder.Services.AddDbContextPool<AppDbContext>(
    options => options.UseSqlServer(connectionString),
    poolSize: 1024); // default is 1024; excess requests fall back to non-pooled creation

The trap: OnConfiguring on a pooled context runs exactly once, the first time an instance is created, not once per checkout from the pool — so you can't use it to inject per-request state like a tenant ID. The supported pattern is a custom, Scoped factory that gets a pooled instance from a Singleton PooledDbContextFactory and then sets the per-request state explicitly before handing the context to your code, every single time it's checked out.

Also worth knowing: EF Core's context pooling is completely separate from ADO.NET connection pooling, which happens at the database-driver level regardless of whether you use AddDbContextPool. Pooling the context avoids allocation and setup cost; pooling the connection avoids the cost of the physical network handshake — they solve different problems and both matter, but conflating them is a common interview slip.

What interviewers look for: the state-leakage trap specifically — it's the detail that separates "I've read about pooling" from "I've debugged a pooling bug in production."

• Common mistakes: enabling context pooling on a multi-tenant app without a custom scoped factory, and having tenant A's context occasionally serve tenant B's request.

Q8 How would you diagnose a slow EF Core query in production without reproducing it locally?#

Short answer: Start from what EF Core and the database already recorded — enable simple logging or ILogger-based logging to capture generated SQL and timings, use IQueryable.ToQueryString() to get the exact SQL for a suspect LINQ query without executing it, and correlate that SQL against SQL Server's Query Store or extended events using the query's hash, rather than trying to reproduce the exact data and load conditions that caused the slowdown.

A practical toolkit, roughly in the order I'd reach for them:

• EF Core simple logging / LogTo for local reproduction, and structured ILogger integration wired to your existing observability stack for production, since it emits the same diagnostic events without the console-only limitation.
• ToQueryString() on the suspect IQueryable, run in a debugger or a unit test, to get exactly what SQL EF Core would send — useful for confirming a hypothesis before touching production.
• An IDbCommandInterceptor that records command text and duration per execution, if you need timing data logging alone doesn't give you.
• APM/dependency tracking (distributed tracing, Application Insights-style dependency calls) to see which specific endpoint or background job triggered the slow command and how many times, in context with the rest of the request.
• Database-side tools — SQL Server's Query Store to see whether the plan regressed, or extended events / sys.dm_exec_query_stats, joined back to the captured SQL text from EF Core's logs.

What interviewers look for: a systematic, tool-by-tool answer that starts from evidence rather than guesswork, and specifically knowing ToQueryString() exists — it's the fastest way to turn "this LINQ query feels slow" into an actual SQL statement you can hand to a DBA.

Q9 How can EF Core's own query cache work against you?#

Short answer: EF Core caches compiled queries by the shape of the expression tree, and a literal constant embedded directly in a LINQ expression — as opposed to a captured local variable — becomes part of that shape, so two logically-identical queries that only differ by an inline literal are compiled and cached separately, and each produces SQL with the literal inlined rather than parameterized, which also stops the database from reusing a single execution plan.

// Two different cache entries and two different, non-parameterized SQL statements
var a = await context.Posts.Where(p => p.Title == "post1").FirstAsync();
var b = await context.Posts.Where(p => p.Title == "post2").FirstAsync();

// One cache entry: 'title' is a captured variable, so EF parameterizes it
string title = "post1";
var c = await context.Posts.Where(p => p.Title == title).FirstAsync();

This gets much worse in dynamically-constructed queries — search/filter endpoints that build up a Where clause based on user input using the Expression API. Building those expressions with embedded Expression.Constant nodes instead of parameter references causes a fresh compilation on essentially every call, which both slows down that endpoint and pollutes the database's own plan cache with near-duplicate, single-use plans, quietly degrading unrelated queries too. Building the same expressions with parameter references instead keeps one cached, reusable plan regardless of the value supplied. EF Core exposes a Query Cache Hit Rate metric specifically so you can catch this class of problem: in a healthy application it settles near 100% shortly after startup, and a rate that stays persistently low is a strong signal that something — usually a dynamically-built query with inlined constants — is defeating the cache.

What interviewers look for: understanding that EF's query cache and the database's plan cache are two separate caches that fail together for the same underlying reason — literal constants instead of parameters.

• Follow-up questions: How would you build a dynamic filter endpoint without falling into this trap? What does the Query Cache Hit Rate metric tell you that request latency alone doesn't?

Q10 Walk me through your process for a performance review of an EF Core-heavy codebase you didn't write.#

Short answer: I'd start with evidence, not code reading — production logs, APM traces and slow-query lists — to find the handful of endpoints actually responsible for most of the pain, then work through a fixed checklist against each one: tracking mode, Include shape, projection opportunity, bulk-write candidates, and context lifetime, before touching anything structural like indexes or caching layers.

Concretely, for each offending endpoint I'd check: is it read-only but still using a tracking query; does it Include sibling collections without AsSplitQuery, risking cartesian explosion; could the response shape be a projection instead of a full entity graph; is a loop hiding an N+1 query, whether from lazy loading or a repository call; would a bulk write collapse into a single ExecuteUpdate/ExecuteDelete; and is the context pooled where the request volume would benefit from it. I'd also check the boring, high-leverage items first, since they usually explain more of the pain than exotic EF Core features do: is AutoDetectChangesEnabled firing needlessly inside a hot loop, is the endpoint round-tripping to the database more times than necessary for reasons that have nothing to do with EF Core (an N+1 at the HTTP layer calling this endpoint repeatedly, for instance), and does the generated SQL actually have a matching index, which I'd confirm using ToQueryString() and an execution plan rather than assuming.

Only after that pass would I consider more invasive changes — compiled queries, compiled models, or restructuring the data access layer — because those carry real complexity cost and, per the earlier questions here, rarely move the needle as much as fixing tracking mode, Include shape and N+1 do.

What interviewers look for: a prioritized, evidence-driven process rather than a grab-bag of EF Core trivia — this question is really testing whether you'd walk into a legacy codebase and make things better without breaking anything.