EF Core Interview Q&A

// ======================================================================
// Q: When do you use AsNoTracking? When should you NOT use it?
//
// The change tracker keeps a snapshot of every entity it loads, so SaveChanges
// can find what changed. It also performs identity resolution: the same row =
// the same instance. This costs memory and CPU.
// Use AsNoTracking for:
// - Read-only queries: GET endpoints, lists, reports, exports.
// - Large result sets.
// - Data that goes into a cache.
// Do not use it:
// - When you will modify the entity and call SaveChanges.
// - When you need identity resolution across a graph (then use
//   AsNoTrackingWithIdentityResolution).
// - When you load an aggregate to apply domain rules.
// - Projections to a DTO / anonymous type are not tracked anyway, unless they
//   contain entity instances. AsNoTracking does no harm there, but it is not
//   needed.
// - Find / FindAsync always tracks and checks the tracker first (no query if the
//   entity is already loaded).
// - Bulk imports: with tracking, the tracker grows and every SaveChanges gets
//   slower. Call SaveChanges per batch and then db.ChangeTracker.Clear().
// - Measure the gain (BenchmarkDotNet, memory profiler), do not assume it. On
//   small queries the difference is negligible. On thousands of rows it is
//   significant.
// In the demo project (as admin): GET /api/admin/ef-demo/n-plus-
// one?mode=naive|include|projection shows 41 / 2 / 1 queries for the same data,
// GET /api/admin/ef-demo/tracking?mode=tracking|no-tracking shows 205 vs 0
// tracked entities, and POST /api/admin/ef-demo/concurrency-conflict shows the
// DbUpdateConcurrencyException from Q60. EfCoreDemoTests asserts on the number
// of queries.
// ======================================================================

// ✓ Read-only list
var products = await db.Products.AsNoTracking()
    .Where(p => p.Category == category)
    .OrderBy(p => p.Name)
    .ToListAsync(ct);

// ✓ Update: needs tracking
var product = await db.Products.SingleAsync(p => p.Sku == sku, ct);
product.ListPrice = newPrice;
await db.SaveChangesAsync(ct);          // UPDATE of list_price only

// ✗ AsNoTracking then a change: SaveChanges sees nothing
var p2 = await db.Products.AsNoTracking().SingleAsync(p => p.Sku == sku, ct);
p2.ListPrice = newPrice;
await db.SaveChangesAsync(ct);          // 0 rows. (db.Update(p2) would write ALL columns)

// The same customer across many orders = one instance, without tracking
var orders = await db.Orders.Include(o => o.Customer)
    .AsNoTrackingWithIdentityResolution()
    .ToListAsync(ct);

// Read-only context (e.g. CQRS read side): no-tracking by default
builder.Services.AddDbContext<ReadDbContext>(o =>
    o.UseNpgsql(cs).UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
// and .AsTracking() in the few places where you write

// ======================================================================
// Q: What is the N+1 problem in EF Core? How do you detect and fix it?
//
// 1 query for the list plus N queries, one per item, for the related data. With
// 500 orders: 501 round trips. It is invisible locally; in production with
// database latency it becomes seconds.
// - Projection is the best fix for reads: one query, only the columns you need.
// - Eager loading with Include when you need the entities (e.g. to modify them).
// - Batch loading with Contains when the data comes from elsewhere (e.g. ids
//   from the ERP).
// - AsSplitQuery with multiple collection Includes. A JOIN across two
//   collections returns lines × payments rows (cartesian explosion).
// - In production: Application Insights / OpenTelemetry. The telltale sign: the
//   same query repeated dozens of times in the same request.
// - Lazy loading is off by default in EF Core. Leave it off in web APIs. Also
//   watch serialization: entities with navigation properties in JSON can trigger
//   lazy loading. Return DTOs.
// ======================================================================

// ✗ Common causes of N+1
// ✗ Cause 1: lazy loading (UseLazyLoadingProxies). Every navigation = a new query
var orders = await db.Orders.Where(o => o.Status == OrderStatus.Placed).ToListAsync(ct);
foreach (var o in orders)
    Console.WriteLine($"{o.Customer.Name}: {o.Lines.Count} lines");   // 1 + 2N queries

// ✗ Cause 2: query inside a loop (even without lazy loading)
foreach (var o in orders)
{
    var customer = await db.Customers.FindAsync([o.CustomerId], ct); // N queries
}

// ✗ Cause 3: calling a service / repository per item
var dtos = new List<OrderDto>();
foreach (var o in orders)
    dtos.Add(await orderService.GetDetailsAsync(o.Id, ct));       // N queries, "hidden"

// Fix 1: projection (best for reads)
var rows = await db.Orders.AsNoTracking()
    .Where(o => o.Status == OrderStatus.Placed)
    .Select(o => new OrderRow(o.OrderNumber, o.Customer.Name, o.Lines.Count))
    .ToListAsync(ct);                                   // 1 query with JOIN / subquery

// Fix 2: eager loading with Include (when you need the entities)
var orders = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Lines).ThenInclude(l => l.Product)
    .Where(o => o.Status == OrderStatus.Placed)
    .ToListAsync(ct);

// Fix 3: batch loading with Contains
var ids = orders.Select(o => o.CustomerId).Distinct().ToList();
var customers = await db.Customers
    .Where(c => ids.Contains(c.Id))                 // WHERE id = ANY(@ids): 1 query
    .ToDictionaryAsync(c => c.Id, ct);

foreach (var o in orders)
    Console.WriteLine(customers[o.CustomerId].Name);

// Fix 4: AsSplitQuery for multiple collection Includes
var orders = await db.Orders
    .Include(o => o.Lines)
    .Include(o => o.Payments)
    .AsSplitQuery()                // 3 small queries instead of 1 huge JOIN
    .ToListAsync(ct);

// Detection
// 1. See the SQL in development
options.UseNpgsql(cs)
       .LogTo(Console.WriteLine, LogLevel.Information)
       .EnableSensitiveDataLogging(env.IsDevelopment())
       // warning → exception when AsSplitQuery is missing on multiple collection Includes
       .ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning));

// 2. A test that counts an endpoint's queries
public class QueryCounter : DbCommandInterceptor
{
    public int Count;
    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData data,
        InterceptionResult<DbDataReader> result, CancellationToken ct = default)
    {
        Interlocked.Increment(ref Count);
        return base.ReaderExecutingAsync(command, data, result, ct);
    }
}
// Assert.True(counter.Count <= 2);   // if someone adds an N+1, the test breaks

// ======================================================================
// Q: Is DbContext thread-safe? Can I run queries in parallel?
//
// No. One DbContext = one unit of work, one operation at a time. A second
// concurrent operation throws InvalidOperationException: A second operation was
// started on this context instance.... Common causes: Task.WhenAll on the same
// context, a forgotten await, async void, a singleton holding a scoped DbContext
// (captive dependency, Q5).
// In a BackgroundService: create a new scope
// (IServiceScopeFactory.CreateAsyncScope()) per message or iteration, so each
// unit of work has its own context. Parallel queries only make sense if they are
// independent and slow: each one takes a connection from the pool.
// ======================================================================

// ✗ same DbContext in parallel tasks
var orders = db.Orders.CountAsync(ct);
var products = db.Products.CountAsync(ct);
await Task.WhenAll(orders, products);

// ✓ separate context per parallel operation
public class DashboardQuery(IDbContextFactory<AppDbContext> factory)
{
    public async Task<(int Orders, int Products)> GetAsync(CancellationToken ct)
    {
        await using var db1 = await factory.CreateDbContextAsync(ct);
        await using var db2 = await factory.CreateDbContextAsync(ct);
        var orders = db1.Orders.CountAsync(ct);
        var products = db2.Products.CountAsync(ct);
        return (await orders, await products);
    }
}
builder.Services.AddDbContextFactory<AppDbContext>(o => o.UseNpgsql(cs));

// ======================================================================
// Q: How do transactions, isolation levels and retries work in EF Core?
//
// - A single SaveChangesAsync is already a transaction. You only need an
//   explicit transaction when several SaveChanges or ExecuteUpdate calls must
//   happen together.
// - Isolation: the default is Read Committed (SQL Server, PostgreSQL).
//   Repeatable Read / Serializable prevent more anomalies but cause
//   serialization failures, so they need retries. On SQL Server consider
//   READ_COMMITTED_SNAPSHOT so reads and writes do not block each other.
// - Transient errors: EnableRetryOnFailure. With your own transaction, wrap the
//   whole unit of work in an execution strategy so a retry re-runs all of it.
// - Keep transactions short: never make an HTTP call (ERP, email) inside a
//   transaction. It holds locks and a connection while it waits (Q10, outbox).
// ======================================================================

builder.Services.AddDbContext<AppDbContext>(o =>
    o.UseNpgsql(cs, npg => npg.EnableRetryOnFailure(maxRetryCount: 3)));

var strategy = db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
    await using var tx = await db.Database.BeginTransactionAsync(
        IsolationLevel.Serializable, ct);
    // ... all the work goes in here
    await db.SaveChangesAsync(ct);
    await tx.CommitAsync(ct);
});

// ======================================================================
// Q: 100 requests try to reserve the last items in stock. How do you avoid
// overselling?
//
// 1. Atomic conditional update: the best option for counters. One statement, no
// retries.
// 2. Pessimistic lock (SELECT ... FOR UPDATE) when you need complex logic on the
// row.
// Comparison of techniques (when to use / cost):
// - Atomic ExecuteUpdate: counters, stock, simple rules. Minimal cost, but it
//   bypasses the change tracker and domain events.
// - Optimistic (concurrency token + retry): low contention, edits by users.
//   Cost: retries when conflicts occur.
// - Pessimistic (FOR UPDATE): high contention, complex logic. Cost: locks,
//   waiting, deadlock risk. Keep transactions very short.
// - Database constraint: always, as the last line of defense. CHECK (available
//   >= 0), unique indexes.
// For worker queues: FOR UPDATE SKIP LOCKED, so two workers do not pick up the
// same rows (the demo does this in the OutboxProcessor). FromSql with
// interpolation is parameterized. FromSqlRaw with string concatenation is not
// (SQL injection).
// ======================================================================

// ✗ Problem: read-modify-write race
// ✗ read-modify-write: two requests both read 3, and both write 3 - 2 = 1
var stock = await db.Stock.SingleAsync(s => s.Sku == sku, ct);
if (stock.Available < qty) return false;
stock.Available -= qty;
await db.SaveChangesAsync(ct);

// Fix 1: atomic conditional update (best for counters)
var rows = await db.Stock
    .Where(s => s.Sku == sku && s.Available >= qty)
    .ExecuteUpdateAsync(s => s.SetProperty(x => x.Available, x => x.Available - qty), ct);
return rows == 1;       // 0 = there was not enough stock

-- SQL: UPDATE stock_items SET available = available - @qty
--      WHERE sku = @sku AND available >= @qty

// Fix 2: pessimistic lock with SELECT ... FOR UPDATE
await using var tx = await db.Database.BeginTransactionAsync(ct);
var stock = await db.Stock
    .FromSql($"SELECT * FROM stock_items WHERE sku = {sku} FOR UPDATE")  // parameterized
    .SingleAsync(ct);
// ... the others wait here until the commit
stock.Available -= qty;
await db.SaveChangesAsync(ct);
await tx.CommitAsync(ct);