EF Core Interview Questions: From DbContext to Production Migrations

Part 6 covered the four data questions every .NET interview asks: indexes, N+1, tracking and the slow query. This bonus part is for the interview that keeps going, the one where the interviewer has lived with EF Core in production and wants to know whether you have too. Six questions in depth, then fifteen rapid-fire ones, each with the answer that earns the nod.

The six: how long a DbContext should live and why it is scoped, how migrations really work and what you do when one has to change in production, what SaveChanges actually does, how you stop two users overwriting each other, when you need an explicit transaction, and when you drop to raw SQL without opening an injection hole. Every snippet below was compiled against EF Core 10.

"How long should a DbContext live, and why does EF Core register it as scoped?"

Why they ask it: the lifetime of the context is where dependency injection, threading and EF Core meet, and the two most common EF Core crashes in a team's bug tracker come from getting it wrong.

A strong answer: a DbContext is a unit of work. It remembers every entity it loads, and SaveChanges writes the difference between what it remembers and what you changed. It is cheap to create, it is not thread-safe, and it should live exactly as long as one logical operation. AddDbContext registers it as scoped, which in ASP.NET Core means one context per HTTP request: created when the request starts, disposed when it ends. Two situations break that fit. A singleton, such as a BackgroundService, cannot take a scoped context in its constructor; in Development the host refuses to start, and the message is the title of a Fixes post of its own. And in Blazor Server the scope is the whole circuit, so a page that starts two queries at once hits "a second operation was started on this context instance". The answer to both is a factory: create a short-lived context where the work happens.

// Program.cs: one context per request for controllers and minimal APIs
builder.Services.AddDbContext<ClinicContext>(o => o.UseSqlServer(connectionString));

// And a factory for Blazor Server components and background work
builder.Services.AddDbContextFactory<ClinicContext>(o => o.UseSqlServer(connectionString));

// In a Blazor component or a hosted service: a context that lives for one operation
await using var db = await DbFactory.CreateDbContextAsync();
var upcoming = await db.Appointments
    .Where(a => a.StartsAt >= DateTime.UtcNow)
    .OrderBy(a => a.StartsAt)
    .ToListAsync();

Follow-ups to expect:

• "What does AddDbContextPool change?" It reuses context instances between requests and resets their state, so never keep per-request data in fields of the context.
• "Can two threads share one context?" No. Not even two awaited queries at the same time; that is the second-operation error.
• "One context per repository?" One per unit of work. Repositories in the same request share the same scoped context, or the transaction they think they share does not exist.

"Walk me through migrations, and what you do when one has to change in production."

Why they ask it: migrations are the one place EF Core touches production data, and the question separates people who have only ever run database update on a laptop from people who have shipped a schema change to a live system.

A strong answer: dotnet ef migrations add compares your current model with the model snapshot file from the last migration and scaffolds a class with an Up and a Down. database update runs the pending ones in order and records each in the __EFMigrationsHistory table, so "what has been applied" is the rows in that table, not the files in your folder. For production you generate an idempotent SQL script with migrations script --idempotent and run it from the pipeline, with a backup first; calling Migrate() at startup is fine for a single instance and a race for anything scaled out. The rule that matters: never edit a migration that has been applied anywhere. Add a new one. And split the risky changes into safe steps: add the column nullable, backfill, then make it NOT NULL, and drop the old column in a later release once nothing reads it. Part 11 of the Database Design series runs that sequence on both engines.

dotnet ef migrations add AddRoomToAppointments
dotnet ef migrations script --idempotent -o migrate.sql
dotnet ef database update

One detail interviewers who have been burned like to hear: a renamed property scaffolds as RenameColumn only when nothing else about the column changed. Rename it and change its type in the same migration and EF Core 10 scaffolds a drop and an add, which empties the column. We watched exactly that happen while building a migration skill, so read every scaffolded migration before you apply it.

Follow-ups to expect:

• "Do you run Down migrations in production?" Rarely. Roll forward with a new migration; Down is for your own machine.
• "Two developers added a migration on different branches?" Remove yours, pull, add it again so the snapshot chain stays linear.
• "What is the model snapshot for?" It is the last known model, so migrations add can diff without a database.

"What does SaveChanges actually do, and when is an entity tracked?"

Why they ask it: "it saves" is the junior answer. The senior answer explains the change tracker, because every strange update bug is a change-tracker bug.

A strong answer: every entity the context knows has a state: Added, Modified, Deleted, Unchanged or Detached. Entities that come out of a tracked query start Unchanged, with a snapshot of their values. SaveChanges runs change detection, compares current values with the snapshots, orders the inserts, updates and deletes so that foreign keys are satisfied, wraps them in one transaction, writes generated keys back into your objects and marks everything Unchanged again. With a disconnected entity, one that arrived in a web request, you choose the state yourself: Update marks every property modified, while Attach plus setting one property marks only that property. Loading a row and then attaching a second object with the same key is the "instance cannot be tracked" error. And for a change that touches many rows, skip the tracker: ExecuteUpdate and ExecuteDelete run one SQL statement immediately.

// One UPDATE statement, no entities loaded, runs immediately (not part of SaveChanges)
var missed = await db.Appointments
    .Where(a => a.Status == "Booked" && a.StartsAt < DateTime.UtcNow.AddHours(-1))
    .ExecuteUpdateAsync(s => s.SetProperty(a => a.Status, "Missed"));

Follow-ups to expect:

• "What does SaveChanges return?" The number of rows written.
• "Why is adding ten thousand entities slow?" Change detection runs on each tracked entity; turn AutoDetectChangesEnabled off for the bulk add, or batch into fresh contexts.
• "Does ExecuteUpdate update entities already loaded?" No. The tracker does not see it; reload if you need the new values.

"How do you stop two users overwriting each other's edits?"

Why they ask it: the default is last write wins, and most candidates have never noticed. The ones who have usually found out from a customer.

A strong answer: optimistic concurrency with a token. On SQL Server a rowversion column mapped with [Timestamp]; on PostgreSQL the system column xmin, which Npgsql maps to a uint property marked [Timestamp] (or IsRowVersion() in the fluent API). EF Core then adds the token's original value to the WHERE clause of every UPDATE and DELETE. If someone changed the row in between, zero rows match and SaveChanges throws DbUpdateConcurrencyException. What you do next is the real answer: reload the database values, show the user what changed, and let them decide, or merge field by field when the rules allow it. Pessimistic locking, holding the row for the length of an edit, almost never fits a web app.

public class Patient
{
    public int Id { get; set; }
    public string FullName { get; set; } = "";
    [Timestamp] public byte[] RowVersion { get; set; } = [];   // SQL Server rowversion
}

try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();
    if (databaseValues is null)
    {
        // Someone deleted the row; tell the user and stop
    }
    else
    {
        // Accept the database as the new baseline, then re-apply the user's edit or ask them
        entry.OriginalValues.SetValues(databaseValues);
    }
}

Follow-ups to expect:

• "How is this different from a transaction?" A transaction protects one operation; a token protects an edit that spans two requests with a human in the middle.
• "Where does the token go in a web form?" In a hidden field, so the original value comes back with the edit.
• "What about a delete of a row someone else edited?" Same exception, same handling.

"When do you need an explicit transaction?"

Why they ask it: to find out whether you know what SaveChanges already does, and whether you have met the execution strategy exception that appears the first time someone turns on retries.

A strong answer: one SaveChanges call is already atomic: every statement it sends runs in one transaction. You need BeginTransaction when several SaveChanges calls, or a SaveChanges and a raw SQL command, must succeed or fail together, or when you need a specific isolation level. The trap comes with EnableRetryOnFailure: once retries are on, EF Core refuses a user-started transaction unless the whole block runs inside the execution strategy, because a retry would otherwise replay half of it.

var strategy = db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
    await using var tx = await db.Database.BeginTransactionAsync();

    db.Invoices.Add(invoice);
    await db.SaveChangesAsync();

    await db.Database.ExecuteSqlAsync(
        $"UPDATE Patients SET Balance = Balance + {invoice.Total} WHERE Id = {invoice.PatientId}");

    await tx.CommitAsync();
});

Follow-ups to expect:

• "Which isolation level does EF Core use?" The database's default, Read Committed on both SQL Server and PostgreSQL, unless you pass one to BeginTransaction.
• "SaveChanges in a loop?" One per batch, or one at the end; each call is a round trip and a transaction.
• "TransactionScope?" Works, with TransactionScopeAsyncFlowOption.Enabled, but the EF transaction API is simpler and the one the team will read.

"When do you drop to raw SQL, and how do you keep it safe?"

Why they ask it: every real codebase has some raw SQL, and the interviewer wants to know whether yours is parameterised.

A strong answer: you go to SQL when LINQ cannot say it (window functions, recursive CTEs, an engine feature the provider has no translation for), when a query could not be translated and the workaround would be worse, or for a report where you want to own the plan. The safe tools are FromSql and ExecuteSql with an interpolated string: EF Core turns every interpolated value into a parameter, so the string the database sees never contains user input. SqlQuery<T> does the same for results that are not entities. The dangerous tool is FromSqlRaw fed a string you built yourself; that is injection with extra steps.

// Interpolated values become parameters; the database never sees the text of 'prefix'
var prefix = "Mar";
var patients = await db.Patients
    .FromSql($"SELECT * FROM Patients WHERE FullName LIKE {prefix + "%"}")
    .OrderBy(p => p.FullName)        // still composable: this becomes a subquery
    .ToListAsync();

// A result shape that is not an entity
var perDay = await db.Database
    .SqlQuery<DailyCount>($"SELECT CAST(StartsAt AS date) AS Day, COUNT(*) AS Appointments FROM Appointments GROUP BY CAST(StartsAt AS date)")
    .ToListAsync();

public class DailyCount
{
    public DateTime Day { get; set; }
    public int Appointments { get; set; }
}

Follow-ups to expect:

• "Can you add Where after FromSql?" Yes, for a plain SELECT; EF wraps it as a subquery. A stored procedure call is not composable.
• "How do you map a view?" A keyless entity with HasNoKey and ToView.
• "Dapper next to EF Core?" Common and fine: EF for the write model, Dapper for hand-tuned reads, both on the same connection string.

Rapid fire: fifteen more they might throw in

• IQueryable or IEnumerable? IQueryable is a query that has not run yet and will run in the database; the moment you call ToList or cast to IEnumerable, everything after that line runs in memory.
• Lazy, eager or explicit loading? Eager with Include is the default choice; explicit with Entry(x).Collection(c).LoadAsync() when you decide later; lazy needs proxies and is how N+1 gets in.
• What is a global query filter? A HasQueryFilter condition added to every query of that entity: soft delete and tenant isolation. IgnoreQueryFilters() opts out. Part 10 of Database Design shows the soft-delete side.
• Owned types and complex types? A value with no identity of its own, an address inside a patient, mapped into the parent's table with OwnsOne or, since EF Core 8, as a complex type.
• Value converters? HasConversion maps a property to a different database type: an enum stored as its name, a strongly typed id stored as an integer.
• Shadow properties? Columns that exist in the model but not in the class, CreatedAt set in a SaveChanges override, read with EF.Property.
• What does HasData do? Seed rows that live in migrations and need explicit keys. On PostgreSQL the identity sequence is not advanced past seeded ids, so the first real insert fails with 23505 until you reset it.
• Split queries? AsSplitQuery turns one join with several collection Includes into one query per collection, avoiding the row explosion of a cartesian product.
• Interceptors? SaveChangesInterceptor for audit columns, DbCommandInterceptor for logging or tagging every command, registered once in AddDbContext.
• TPH, TPT or TPC? Inheritance mapping: one table with a discriminator (the default and usually the fastest), one table per type joined by key, or one table per concrete class.
• Compiled queries and compiled models? EF.CompileAsyncQuery caches the translation of a hot query; dotnet ef dbcontext optimize precompiles a large model to cut startup time.
• Database-first? dotnet ef dbcontext scaffold reverse-engineers entities and a context from an existing database; for a quick look at what a CREATE TABLE script becomes, our DDL to EF Core tool does it in the browser.
• Does EF Core index foreign keys? Yes, by convention, one index per foreign key. HasIndex adds the rest; IsUnique for a unique index, HasAlternateKey only when another table must reference the column.
• Keyless entities? Views and raw query results map with HasNoKey; they are never tracked and cannot be saved.
• EF Core or Dapper? EF Core for the model, changes, migrations and most reads; Dapper when you want to hand-write the SQL of a hot path. Many teams run both, and saying so is the mature answer.

Frequently asked

What are the most common EF Core interview questions?

The DbContext lifetime and why it is scoped, how migrations work and how to run them safely in production, what SaveChanges and the change tracker do, optimistic concurrency with a rowversion or xmin token, when an explicit transaction is needed, and when to use raw SQL with FromSql or SqlQuery. Rapid-fire follow-ups cover IQueryable versus IEnumerable, loading strategies, query filters, owned types, value converters, HasData, split queries, interceptors and inheritance mapping.

Why is DbContext registered as scoped in ASP.NET Core?

A DbContext is a unit of work: it tracks the entities it loads and SaveChanges writes the difference. It is cheap to create and not thread-safe, so one instance per HTTP request fits most apps. A singleton such as a BackgroundService cannot consume it directly, and Blazor Server components should create short-lived contexts through IDbContextFactory, because the circuit's scope outlives a single operation.

How do you handle EF Core migrations in production?

Generate an idempotent SQL script with dotnet ef migrations script --idempotent and run it from the deployment pipeline after a backup. Never edit a migration that has already been applied anywhere; add a new one. Split risky changes into safe steps: add a nullable column, backfill it, make it NOT NULL, and drop the old column in a later release. Read every scaffolded migration, because a rename combined with another change scaffolds as a drop and an add.

How does EF Core handle concurrency?

With optimistic concurrency: a token column, rowversion on SQL Server mapped with the Timestamp attribute or xmin on PostgreSQL mapped to a uint property with the Timestamp attribute, is added to the WHERE clause of every UPDATE and DELETE. If another user changed the row, zero rows match and SaveChanges throws DbUpdateConcurrencyException. Handle it by reloading the database values, showing the user what changed, or merging when the rules allow.

Where to go from here

That closes the .NET interview series. If you landed here from a search, start with Part 1 for the shape of the whole loop, and read Part 6 for the SQL side of the same round. For the design knowledge behind the migrations and concurrency answers, the Database Design for Developers series runs every script on both engines.