Junior EF Core interviews ask "what is a DbContext." Senior interviews ask why your dashboard fires 200 queries to render one page, what happens when two users save the same row at once, and why calling Migrate() on startup took production down during a rolling deploy. EF Core makes the easy 80% trivial and hides the hard 20% — change tracking, query translation, concurrency, and schema evolution — right where production breaks. Seniority is knowing what SQL your LINQ actually becomes and what EF is doing on your behalf. This guide is 30 questions across change tracking & the DbContext, querying & performance, concurrency & transactions, and migrations & production.

Each answer is a short answer you could say in one breath, a deep dive into the mechanism (with code), and the senior signal the interviewer is listening for. Every example is from Mattrx — a real multi-tenant marketing-analytics platform on .NET 9 / EF Core 9 over Azure SQL, with tables like Campaigns (~4M rows) and CampaignEvents (~1.2B rows), every query scoped by TenantId. No Foo and Bar.

How to use this guide

Read the deep dive even when the short answer is obvious — seniority is in the generated SQL, the failure mode, and the trade-off. The blocks build on each other. If you can explain what SQL each snippet produces out loud, you're ready.

---

Block A — change tracking & the DbContext

1. What is change tracking, and how does SaveChanges know what to do?

Short answer: The DbContext keeps a snapshot of every entity it loaded (the change tracker). On SaveChanges, it compares current values to that snapshot, computes each entity's state (Added / Modified / Deleted / Unchanged), and generates the minimal INSERT/UPDATE/DELETE batch. You never write SQL for the state change — EF derives it from tracked state.

Deep dive: State drives everything. A tracked entity you mutate becomes Modified; only its changed columns go into the UPDATE. An entity from Add() is Added -> INSERT.

  Load -> snapshot stored -> you mutate props -> DetectChanges compares
       -> state = Modified -> SaveChanges emits UPDATE (only changed columns)

Senior signal: You describe SaveChanges as snapshot-diffing that produces state-driven SQL, and you know only changed columns are written.

2. What lifetime should DbContext have, and why not a singleton?

Short answer: Scoped (one per request/unit of work). DbContext is not thread-safe and accumulates tracked entities, so a singleton would corrupt state across concurrent requests and leak memory as the change tracker grows unbounded. Short-lived contexts keep the tracked set small and isolated.

Deep dive: In ASP.NET Core, AddDbContext registers it scoped by default. Mattrx keeps one context per HTTP request; background workers create a scope per message.

Senior signal: You say scoped, cite thread-safety + unbounded tracker growth as the reasons a singleton fails, and know a long-lived context is a memory/perf smell.

3. What does AsNoTracking do, and when do you use it?

Short answer: It tells EF not to snapshot or track the returned entities — so no change-tracking overhead and less memory. Use it for read-only queries (the vast majority of a read-heavy app), where you'll never call SaveChanges on the results.

Deep dive: Tracking every row of a large read is pure waste. Mattrx's list/dashboard endpoints are all AsNoTracking + projection.

// Read-only query: skip change tracking -> less memory, no identity-map overhead.
var rows = await _db.Campaigns
    .AsNoTracking()
    .Where(c => c.TenantId == tenantId && c.Status == CampaignStatus.Active)
    .Select(c => new CampaignListItem(c.Id, c.Name, c.UpdatedAt))
    .ToListAsync(ct);

Senior signal: You default reads to AsNoTracking and can quantify the win (no snapshot, no identity map) on large result sets.

4. How does EF decide INSERT vs UPDATE for an entity?

Short answer: By its tracked state, not by whether it "looks new." A tracked entity you modify is Modified -> UPDATE. An entity passed to Add() is Added -> INSERT. A detached entity (e.g., deserialized from an API request) has no state until you attach it — and EF guesses from the key.

Deep dive: This is the source of countless bugs: you deserialize an entity, call Update(), and EF UPDATEs all columns (or INSERTs a duplicate) because it never tracked the original.

Senior signal: You reason in terms of entity state, and you flag "detached entity + Update()" as a place to be explicit rather than trust EF's inference.

5. What is the identity map (first-level cache)?

Short answer: Within one DbContext, EF returns the same instance for the same key — if you load campaign X twice, you get one object, not two. This keeps tracked state consistent (no two conflicting copies of the same row) and means a second query for a tracked key can skip re-materializing.

Deep dive: It's per-context and short-lived — not a shared cache. It's also why long-lived contexts drift from the DB: the identity map serves the stale first copy.

Senior signal: You know the identity map is per-context consistency (not a cross-request cache) and connect it to why contexts should be short-lived.

6. Tracking query returns stale data — why?

Short answer: Because of the identity map (Q5): a tracking query won't overwrite an already-tracked entity's current values with fresh DB values by default — it returns the tracked instance. If you need fresh data, use AsNoTracking, a new context, or Reload().

Deep dive: "I changed the row in SSMS but EF still shows the old value" is almost always a tracked instance from earlier in the same context.

Senior signal: You explain the surprise via identity resolution and know the three ways to force freshness.

7. What's the danger of Update() on a whole entity?

Short answer: Update() marks every property Modified, so EF writes all columns — overwriting fields you didn't intend to change (including ones another process just updated) and defeating concurrency where you only meant to touch one field. Prefer loading + mutating the specific properties, or ExecuteUpdate for targeted set-based changes.

Deep dive: On a detached DTO, Update() is a blunt instrument. Mattrx patches specific fields on a loaded, tracked entity so the UPDATE touches only what changed.

Senior signal: You avoid blanket Update() on detached entities and know it causes over-writing + lost updates.

8. What is DbContext pooling and when does it help?

Short answer: AddDbContextPool reuses context instances (resetting their state) instead of allocating a new one per request — cutting allocation/GC overhead on high-throughput apps. The catch: because instances are reused, you can't safely inject per-request state into the context's constructor.

Deep dive: Worth it at Mattrx's request volume; you inject request state (like tenant) via a scoped accessor, not the context ctor.

Senior signal: You reach for pooling under high throughput and know its constraint on constructor-injected per-request state.

---

Block B — querying & performance

9. IQueryable vs IEnumerable — where does the query run?

Short answer: IQueryable builds an expression tree that EF translates to SQL and runs on the database. The moment you drop to IEnumerable (e.g., .AsEnumerable(), or a method EF can't translate), the rest executes in memory on the app server — pulling rows over the wire first. Knowing where the boundary is is the whole game.

Deep dive: Where on IQueryable -> SQL WHERE. The same Where after .ToList() -> filtering 1.2B rows in C# after loading them. That's the difference between 40ms and an outage.

Senior signal: You track the IQueryable->IEnumerable transition as "this is where execution moves from SQL to memory."

10. What is the N+1 problem and how do you fix it?

Short answer: You run one query for a list, then one more query per item in a loop (lazy loading or manual per-item queries) — 1 + N round trips. Fix it by loading related data in one query: Include for entities, or better, project the aggregate/columns you need server-side.

Deep dive: The classic Mattrx bug was per-campaign event counts:

// N+1: one query for campaigns, then one MORE per campaign for its count.
var campaigns = await _db.Campaigns.Where(c => c.TenantId == tenantId).ToListAsync(ct);
foreach (var c in campaigns)
    c.EventCount = await _db.Events.CountAsync(e => e.CampaignId == c.Id, ct); // fires N times

// Fix: one server-side query that projects the aggregate.
var list = await _db.Campaigns
    .Where(c => c.TenantId == tenantId)
    .Select(c => new CampaignSummary(c.Id, c.Name, c.Events.Count()))
    .ToListAsync(ct);

Senior signal: You spot N+1 in a loop, fix it with projection/Include, and check the generated SQL round-trip count — not just that it "works."

11. Eager vs lazy vs explicit loading — when each?

Short answer: Eager (Include) loads related data up front in the same round trip — the default choice when you know you need it. Explicit (.Entry(e).Collection(...).LoadAsync()) loads on demand, deliberately. Lazy (proxies) loads transparently on property access — convenient but the #1 cause of accidental N+1; most teams disable it.

Deep dive: Mattrx disables lazy loading entirely so every DB round trip is explicit and visible in code.

Senior signal: You default to eager/projection, treat lazy loading as an N+1 footgun, and reach for explicit loading when you want control.

12. Two Includes made the query huge and slow — why?

Short answer: Cartesian explosion. Including two (or more) collection navigations in one query produces a JOIN whose row count is the product of the collections — the same parent data repeated many times over the wire. Fix with AsSplitQuery(), which issues one query per collection.

Deep dive:

// One JOIN would return |Events| x |Segments| rows. Split into separate queries instead.
var campaign = await _db.Campaigns
    .Include(c => c.Events)
    .Include(c => c.Segments)
    .AsSplitQuery()
    .FirstOrDefaultAsync(c => c.Id == id, ct);

Senior signal: You recognize cartesian explosion from multiple collection Includes and know AsSplitQuery trades one bloated round trip for a few lean ones (with the consistency caveat that splits aren't one snapshot).

13. Projection vs loading full entities — why does it matter?

Short answer: Select-ing into a DTO makes EF fetch only those columns and skip change tracking — less data over the wire, less memory, no tracking overhead. Loading full entities pulls every column (including big text/blobs) whether you use them or not. For reads, project.

Deep dive: Mattrx's list endpoints never materialize full Campaign entities — they project the 3–4 columns the card needs.

Senior signal: You project to DTOs for reads by default and connect it to narrower SQL SELECT lists + no tracking.

14. What are compiled queries?

Short answer: EF.CompileAsyncQuery caches the LINQ->SQL translation so a hot query skips the expression-tree compilation on every call. EF already caches query plans internally, but explicit compiled queries shave the remaining translation overhead for the highest-frequency queries.

Deep dive: Mattrx compiles its two hottest dashboard queries; measurable at thousands of req/sec, negligible below that.

Senior signal: You know EF caches plans already, and reserve explicit compiled queries for genuinely hot paths where you've measured the translation cost.

15. What is client-side evaluation and why is it dangerous?

Short answer: When part of your LINQ can't be translated to SQL, older EF silently ran it in memory after fetching rows — quietly turning an indexed WHERE into "load everything, filter in C#." EF Core 3.0+ throws instead (except the final projection), forcing you to see it. It's the classic silent perf killer.

Deep dive: A .Where(c => MyHelper(c.Name)) that EF can't translate now errors — you rewrite it to translatable SQL or move the logic post-materialization deliberately.

Senior signal: You know EF Core 3+ throws on un-translatable predicates, and you treat that exception as the framework protecting you, not an obstacle.

16. When do you drop to raw SQL, and how safely?

Short answer: Use FromSql/ExecuteSql when LINQ can't express it (hints, complex windowing, a tuned query). Use parameterized interpolation (FromSql($"... {value}")) so EF parameterizes it — never string-concatenate user input, which is SQL injection. FromSqlRaw with concatenation is the unsafe path.

Deep dive: Mattrx's 1.2B-row aggregate uses a hand-tuned FromSql with a columnstore-friendly shape that the LINQ translator wouldn't produce.

// Interpolated -> EF turns {tenantId}/{from} into SQL PARAMETERS (safe).
var rows = await _db.Database
    .SqlQuery<DailyKpi>($@"SELECT ... FROM CampaignEvents
                           WHERE TenantId = {tenantId} AND EventDate >= {from}")
    .ToListAsync(ct);

Senior signal: You reach for raw SQL deliberately, always parameterized, and know FromSql results still compose with LINQ (and still respect the mapped entity).

17. Streaming vs buffering results?

Short answer: ToListAsync buffers the whole result set into memory. AsAsyncEnumerable (or iterating the query) streams row by row — constant memory for large exports. Buffer small results (simpler, releases the connection sooner); stream large ones.

Deep dive: Mattrx's CSV export streams CampaignEvents so a multi-million-row export doesn't balloon the working set.

Senior signal: You match buffering vs streaming to result size and know streaming holds the connection/reader open while you iterate.

---

Block C — concurrency & transactions

18. How does optimistic concurrency work in EF Core?

Short answer: You add a concurrency token (typically a SQL rowversion). EF appends it to the UPDATE's WHERE clause; if someone changed the row since you read it, the token won't match, 0 rows are affected, and EF throws DbUpdateConcurrencyException — so a lost update becomes a detectable conflict instead of a silent overwrite.

Deep dive:

public class Campaign
{
    public Guid Id { get; set; }
    public string Name { get; set; } = "";
    [Timestamp] public byte[] RowVersion { get; set; } = default!; // SQL rowversion token
}
// UPDATE Campaigns SET Name=@n WHERE Id=@id AND RowVersion=@original
// 0 rows -> someone else won -> DbUpdateConcurrencyException

Senior signal: You describe the WHERE token = original mechanism and frame it as turning silent lost-updates into explicit conflicts.

19. How do you handle DbUpdateConcurrencyException?

Short answer: Catch it, reload the current database values, and decide a policy: client wins (re-apply your change over theirs), store wins (discard yours), or merge (surface a diff to the user). There's no universal right answer — it's a business decision per entity.

Deep dive:

try { await _db.SaveChangesAsync(ct); }
catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var dbValues = await entry.GetDatabaseValuesAsync(ct);   // the winner's current row
    if (dbValues is null) { /* row was deleted -> handle */ }
    else
    {
        entry.OriginalValues.SetValues(dbValues);            // rebase onto current, then retry
        await _db.SaveChangesAsync(ct);
    }
}

Senior signal: You reload and apply an explicit resolution policy, and you say which policy fits which entity rather than blindly retrying.

20. How do transactions work in EF Core?

Short answer: A single SaveChanges is already atomic — all its INSERT/UPDATE/DELETEs run in one implicit transaction. For multiple SaveChanges or mixing EF with raw SQL as one unit, open an explicit BeginTransactionAsync and commit/rollback yourself.

Deep dive: Mattrx wraps a multi-step write (save order + write outbox row) in one explicit transaction so both commit or neither does.

await using var tx = await _db.Database.BeginTransactionAsync(ct);
_db.Orders.Add(order);
await _db.SaveChangesAsync(ct);
_db.Outbox.Add(evt);
await _db.SaveChangesAsync(ct);
await tx.CommitAsync(ct);   // both, or neither

Senior signal: You know one SaveChanges is atomic by itself and reach for an explicit transaction only when a unit of work spans multiple saves / raw SQL.

21. Does EF Core support pessimistic locking?

Short answer: Not natively — EF's concurrency model is optimistic. For pessimistic locking (e.g., SELECT ... WITH (UPDLOCK, ROWLOCK)), you drop to raw SQL inside a transaction. Prefer optimistic concurrency unless contention is high enough that retrying conflicts is worse than serializing.

Deep dive: Mattrx uses a raw UPDLOCK read inside a transaction only for the rare hot-row counter; everything else is optimistic.

Senior signal: You know EF has no built-in pessimistic lock, use raw SQL hints when truly needed, and default to optimistic.

22. What is an execution strategy, and what's the transaction gotcha?

Short answer: EnableRetryOnFailure wraps operations in a retrying execution strategy for transient faults (essential on Azure SQL). The gotcha: a retrying strategy can't implicitly retry a user-initiated transaction (it can't know how to replay your multi-statement block), so you must wrap that block in strategy.ExecuteAsync(...) explicitly.

Deep dive:

var strategy = _db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
    await using var tx = await _db.Database.BeginTransactionAsync(ct);
    // ... multiple SaveChanges ...
    await tx.CommitAsync(ct);          // the WHOLE block is retried atomically on transient fault
});

Senior signal: You enable retry-on-failure for cloud SQL and know explicit transactions must be wrapped in ExecuteAsync or you'll get a runtime error.

23. How do you do a bulk update without loading every row?

Short answer: EF7+ ExecuteUpdate/ExecuteDelete run a set-based UPDATE/DELETE directly in SQL — no loading entities, no change tracking, one round trip. The old load-modify-save loop pulled every row into memory. Note: bulk ops bypass the change tracker and don't run SaveChanges interceptors.

Deep dive:

// One SQL UPDATE for the whole matching set — no entities materialized.
await _db.Campaigns
    .Where(c => c.TenantId == tenantId && c.Status == CampaignStatus.Draft)
    .ExecuteUpdateAsync(s => s.SetProperty(c => c.Status, CampaignStatus.Archived), ct);

Senior signal: You use ExecuteUpdate/Delete for set-based writes and know they skip tracking/interceptors — so any logic in SaveChanges won't fire.

24. How do you handle transient failures against Azure SQL?

Short answer: Enable connection resiliency (EnableRetryOnFailure) so EF retries transient errors (throttling, failover) with backoff. Combine with idempotent writes/optimistic concurrency so a retry after an ambiguous failure doesn't double-apply.

Deep dive: Cloud databases fail transiently as a matter of course; Mattrx pairs retries with concurrency tokens so a retried save can't silently clobber.

Senior signal: You treat transient faults as normal in the cloud, enable retries, and pair them with idempotency/concurrency so retries are safe.

---

Block D — migrations, modeling & production

25. How do EF Core migrations work?

Short answer: dotnet ef migrations add diffs your current model against the model snapshot (a C# file EF maintains) and generates an Up/Down migration of schema changes. database update applies pending migrations, recording each in a __EFMigrationsHistory table so it knows what's already applied.

Deep dive: The snapshot — not the database — is EF's source of truth for "what the model looked like last migration," which is why you commit it.

Senior signal: You know migrations diff against the committed snapshot (not the live DB) and that history table tracks applied state.

26. How should you apply migrations in production?

Short answer: Not with db.Database.Migrate() on app startup. In a multi-instance rolling deploy, several instances race to migrate, it needs schema-owner permissions at runtime, and there's no review. Instead generate an idempotent SQL script in CI and apply it as a reviewed, gated deploy step.

Deep dive:

  dotnet ef migrations script --idempotent -o migrate.sql   # generate in CI
  -> review in PR -> apply as an explicit, gated deploy step (not app startup)

Senior signal: You never auto-migrate on startup in prod, cite the multi-instance race + permissions + no-review reasons, and ship idempotent scripts.

27. Fluent API vs data annotations?

Short answer: Data annotations ([Required], [MaxLength]) are simple and co-located on the entity, good for basic constraints. Fluent API (OnModelCreating) is more powerful (composite keys, relationships, filters, converters, anything annotations can't express) and keeps mapping out of your domain classes. Fluent wins where they conflict.

Deep dive: Mattrx keeps domain entities annotation-light and does the real mapping in Fluent configuration classes (IEntityTypeConfiguration<T>).

Senior signal: You use Fluent for anything non-trivial, know it overrides annotations, and organize it into per-entity configuration classes.

28. What are owned types and value converters?

Short answer: Owned types map a value object (e.g., Money, Address) into the owner's table without its own identity — good DDD modeling without extra tables. Value converters translate a property between its CLR type and a DB type (e.g., an enum stored as a string, or an encrypted column) on the way in and out.

Deep dive: Mattrx stores enums as strings via converters (readable in the DB) and models Money as an owned type.

Senior signal: You use owned types to model value objects cleanly and value converters to bridge CLR<->DB representations without leaking mapping into the domain.

29. What are global query filters and their pitfalls?

Short answer: HasQueryFilter applies a predicate to every query for an entity — perfect for multi-tenancy and soft-delete (auto-append TenantId = current or IsDeleted = false). Pitfalls: the filter captures a service, so the context must be scoped; it doesn't apply to raw SQL; and you need IgnoreQueryFilters() for admin/cross-tenant jobs.

Deep dive:

// Defense in depth: every Campaign query is auto-scoped to the current tenant.
modelBuilder.Entity<Campaign>().HasQueryFilter(c => c.TenantId == _tenant.Id);
// Admin/cross-tenant read must opt out explicitly:
var all = await _db.Campaigns.IgnoreQueryFilters().Where(...).ToListAsync(ct);

Senior signal: You use global filters as a tenant/soft-delete safety net, and you name the pitfalls (scoped context, raw-SQL bypass, IgnoreQueryFilters).

30. What are the EF Core performance mistakes you check for in production?

Short answer: Missing AsNoTracking on reads, N+1 from lazy loading, loading full entities instead of projecting, cartesian explosion from multiple Includes, no pagination on large sets, client-side evaluation, Update() writing all columns, and auto-migrate on startup. Most EF perf problems are one of these eight.

Deep dive: The through-line: EF makes the wrong-but-easy thing effortless (track everything, load everything, one round trip per access). Seniority is defaulting to the right thing — project, no-track, paginate, one query.

Senior signal: You have a concrete checklist and you verify with the generated SQL (logging / ToQueryString()), not by trusting that it compiled.

---

Bonus — what makes an answer "senior" in EF Core?

Short answer: A junior treats EF as a magic box that turns objects into rows; a senior always knows what SQL comes out and what EF is doing on their behalf. "Why is this slow?" gets a shrug from a junior and "it's an N+1 from lazy loading — here's the ToQueryString() showing 200 round trips; projecting fixes it to one" from a senior.

Deep dive: The recurring senior lens across all 30: EF's convenience hides round trips, tracking cost, and full-column loads. Read the generated SQL, default reads to no-tracking + projection, make concurrency explicit, and treat schema changes as reviewed deploy steps — not startup magic.

Senior signal: You reach for ToQueryString()/SQL logging reflexively and can always answer "what does this become in the database?"

The model to carry forward

EF Core is a leaky abstraction on purpose — it lets you ignore SQL until the moment ignoring it costs you a page of round trips or a lost update. So don't ignore it: project reads into DTOs with AsNoTracking, kill N+1 with one query, split multi-collection includes, make concurrency a rowversion and a resolution policy, wrap real units of work in explicit transactions with retries, and ship migrations as idempotent, reviewed scripts. Answer each question with the generated SQL and the failure it prevents, and you'll sound like someone who has run EF Core in production — which is the signal.

Further reading

• SQL Server Interview Questions for Senior Developers — Execution Plans, Indexing & Locking (Deep Answers)
• C# Interview Questions for Senior Developers — Async, Memory, LINQ & Concurrency (Deep Answers)
• React Interview Questions for Senior Developers — Hooks, Reconciliation, Fiber & Performance (Deep Answers)

---

Prepping for a senior .NET interview and want these drilled live, or a mock focused on EF Core query performance and concurrency? I'm always happy to help — reach me at randhir.jassal@gmail.com.