Articles

EF Core Interview Questions: Tracking, N+1 Queries, and Concurrency Conflicts

Prepare for EF Core interviews with tracking boundaries, N+1 diagnosis, split-query trade-offs, concurrency tokens, conflict policies, and provider tests.

An order endpoint is slow, a refreshed record still shows an old value, and two editors overwrite each other's changes. Adding AsNoTracking() to every query will not fix all three. Strong EF Core interview answers separate context state, database query shape, and the policy for conflicting writes.

This guide uses an original order-management example to connect those decisions. Official facts are linked to Microsoft documentation. Worked examples and design recommendations are ours; no candidate reports are used, and the scenarios are not presented as employer-reported interview questions. SQL arithmetic was checked with a small relational fixture, not an EF Core integration benchmark.

Try Design Calendar Event CRUD with Unit Tests while reading. For each endpoint, explain which objects must remain tracked, how many database commands you expect, and what happens if a competing edit wins.

What does tracking actually buy you?

Official fact: a tracking query associates returned entities with the context so changes can be detected and saved. It also performs identity resolution: repeated occurrences of the same entity key use the tracked instance. AsNoTracking() avoids that context tracking; AsNoTrackingWithIdentityResolution() deduplicates instances within the query result without attaching them to the context. See tracking and no-tracking queries.

Imagine an order detail screen where the user will change the delivery note and save immediately. A short-lived tracking context is a reasonable fit. A dashboard that serializes summaries and discards them usually does not need tracked entities. These are usage decisions, not declarations that one mode is always faster.

A subtle follow-up is whether projection guarantees no tracking:

// Contains an entity instance: normally tracked.
var a = await db.Orders
    .Select(o => new { Order = o, Count = o.Lines.Count() })
    .ToListAsync(ct);

// Scalar values only: no entity instance to track.
var b = await db.Orders
    .Select(o => new { o.Id, o.DeliveryNote })
    .ToListAsync(ct);

The first result is anonymous, but its Order member is still an entity. Explain the materialized contents rather than the outer C# type. For a response DTO, select only the fields the client needs and verify the resulting SQL on the actual provider.

Here is an interview reasoning check: if several result rows reference the same customer, ordinary no-tracking can materialize repeated customer instances. Identity resolution changes that object graph. Measure allocations and runtime for the real result shape before claiming a universal performance winner.

Why can a tracking query return stale values?

Suppose context A has loaded order 42 with note “Leave at reception.” Another context changes that note. A then executes a LINQ query for order 42 again. Does running SQL guarantee that A's existing object will be overwritten?

Official fact: a tracking query still queries the database, but an entity already tracked for the returned key is reused without replacing its current and original values. Microsoft's identity-resolution guidance explains this distinction. A context is not a general-purpose cache that refreshes every tracked property on every query.

My debugging recommendation is to inspect the context lifetime and whether the entity was already tracked before blaming replication lag. An unexpectedly long-lived context can make local state look like a database freshness problem.

If refreshing is intentional, understand the operation. ReloadAsync() reloads the tracked entity and can discard local changes; GetDatabaseValuesAsync() obtains database values for inspection without being the same operation as replacing the current entity. These tools are documented under accessing tracked entities.

In an editor, silently reloading could destroy the user's unsaved note. A better product decision may be to display the new database value alongside the proposed value and ask for resolution. The correct choice depends on whether you are refreshing a read-only page or reconciling an edit.

Diagnose N+1 from database commands

Consider a dashboard loading 20 orders, then displaying each order's lines. With lazy loading enabled, accessing each unloaded collection can trigger another query. In the simplified case, one parent query plus 20 collection queries produces 21 database commands.

Official fact: Microsoft's efficient-querying guidance highlights this round-trip pattern and recommends considering projection, result limits, and relationship loading together. Lazy loading is not enabled merely because navigation properties exist; the application's configuration matters.

A loop containing an explicit query can produce the same problem without lazy loading. Conversely, a loop over data already loaded into memory does not prove N+1. Count executed commands for one representative request instead of diagnosing from indentation.

For a summary-only endpoint, this query shape expresses the required result without materializing every line:

var page = await db.Orders
    .Where(o => o.Id > afterId)
    .OrderBy(o => o.Id)
    .Take(20)
    .Select(o => new {
        o.Id,
        o.DeliveryNote,
        LineCount = o.Lines.Count()
    })
    .ToListAsync(ct);

This is an illustrative model-level query, not a provider-independent promise about exact SQL text. Inspect its translation, indexes, parameter values, and execution plan. A single command can still scan too much data or return an unnecessarily large payload.

Microsoft's logging documentation describes command logging. My measurement plan is to record command count, database duration, returned rows, and end-to-end latency for the same endpoint inputs. Avoid logging sensitive parameter values indiscriminately in production.

When does Include create a different performance problem?

Suppose each of the 20 orders has ten lines and five adjustments. Lines and adjustments are sibling collections. A join that combines both can produce 10 × 5 rows per order, or 1,000 rows total, with parent data repeated across them.

Those are original fixture assumptions, not typical production cardinalities. A split representation contains 20 parent rows, 200 line rows, and 100 adjustment rows: 320 rows across the separate result sets. Our SQLite fixture checks these counts. It does not measure EF materialization time or network traffic.

Official fact: EF Core split queries can avoid sibling-collection cartesian expansion, but introduce trade-offs including additional round trips and possible inconsistent observations across the separate queries. The single versus split query documentation also calls out ordering requirements for paginated queries on versions before EF Core 10.

The decision is therefore not “one query good, several queries bad.” For this dashboard, I would first ask whether the client needs both collections at all. If it only needs counts, projection can eliminate much of the materialized data. If it needs full details, compare single and split query behavior under realistic cardinality and latency.

For reproducible pagination, use a fully unique order such as a timestamp plus the primary key when timestamps can tie. A split query should not be chosen without deciding what consistency the page needs during concurrent changes. A suitable transaction can help, with isolation and cost determined by the database.

Another trap is filtered Include on a tracking context. Official fact: previously tracked related entities can appear through navigation fixup even when they do not satisfy the new filter. Microsoft documents this under eager loading. Use a fresh context or appropriate no-tracking query when the returned collection must reflect only that filter.

Walk through an optimistic concurrency conflict

Two editors load the same order with application-managed version 7. Editor A changes the note and successfully writes version 8. Editor B still submits an edit based on version 7.

The important predicate is conceptually:

UPDATE Orders
SET DeliveryNote = @proposedNote, Version = @nextVersion
WHERE Id = @id AND Version = @originalVersion;

In this original example, A's affected-row count is one; B's is zero. The row's key still exists, but the original version no longer matches. Deletion could also make an update affect zero rows, so the application must distinguish those cases if its response depends on them.

Official fact: with a configured concurrency token, EF Core compares the original token during tracked updates or deletes and reports a conflict through DbUpdateConcurrencyException when the expected update does not occur. SQL Server rowversion can provide a database-generated token; it is not a wall-clock timestamp, and equivalent support is provider-specific. See handling concurrency conflicts.

An application-managed token must change whenever the changes it protects occur. In our integer example, the application supplies 8 for the first successful edit. On SQL Server with a configured rowversion, the database generates the replacement value instead. Do not mix those two mechanisms in your explanation.

My recommended conflict policy starts with user intent. If both people changed the same delivery note, silently applying B after refreshing the token erases A's edit. If they changed independent fields, a merge might be acceptable, but only if it preserves the domain's combined constraints.

A deliberate resolution reloads database values, decides which proposal remains valid, updates the original token used for the next attempt, and retries with a bound. If the row is gone, there is no current row to merge. A repeated conflict should not become an infinite loop.

What changes with ExecuteUpdate and detached edits?

Official fact: ExecuteUpdate applies an update immediately without synchronizing tracked instances or automatically applying their concurrency-token checks. You can put the expected token in its predicate and inspect the affected-row count yourself. Multiple such calls are not automatically grouped into one shared transaction. See ExecuteUpdate and ExecuteDelete.

Consider a context that loaded a quantity of five, followed by a bulk operation that changes the database quantity to six. The tracked object still contains five. If later code computes a replacement from that stale object, the final outcome depends on the update predicate and any configured token. Mixing update paths requires an explicit refresh or separation strategy.

Detached web edits have a related boundary. The version the browser originally read must reach the server's conflict check. Reloading the newest row and then applying every posted field without comparing that original token can conceal a stale edit.

My design recommendation is to use a narrow update DTO, authorize the target row, preserve the client's expected version, and mark only permitted fields as changed. Calling Update on an arbitrary posted entity graph can express a much wider write than the endpoint intended. Conflict detection does not replace authorization or input validation.

What should you test before calling the answer complete?

Official fact: DbContext does not support concurrent operations on one instance. Await operations before reusing it, or use separate contexts for truly independent parallel work. The context-lifetime guidance describes a context as a short unit of work.

For a conflict test, use two contexts that both load the initial version. Save A, then save B and assert the expected conflict. Also test a deleted row and the selected merge or rejection behavior. A test that loads B only after A saves has skipped the stale-read condition.

For query performance, seed multiple parents and multiple sibling children, then assert command counts and response contents. Check tied pagination values, filtered navigations with previously tracked entities, and bounded result sizes. These fixtures catch mistakes that a one-row happy path hides.

Microsoft's testing-strategy guidance explains why a substitute database cannot establish all production-provider behavior. Our local relational checks establish the worked row counts and token predicates only. Provider integration tests remain necessary for LINQ translation, generated tokens, isolation, and exception behavior.

Question	Practice focus
Review getEvents endpoint for readability, performance, scalability, security	Inspect query count and payload
Design Calendar Event CRUD with Unit Tests	Define tracked update boundaries
Design Calendar Event Conflict Handling	Resolve competing user edits
Design scalable inventory system and avoid races	Preserve conditional write invariants
Implement cursor-based query pagination	Keep page ordering stable

Finish with Design Calendar Event Conflict Handling. Explain the SQL predicate, the user's visible outcome, and the test that distinguishes a stale edit from a successful save.