EF Core Interview Q&A

-- ======================================================================
-- Q: Two users edit the same product at the same time. What happens and how do
-- you prevent it?
--
-- By default: last write wins. The second user overwrites the first user's
-- change without knowing it (lost update). Solution: optimistic concurrency with
-- a concurrency token.
-- EF Core puts the token in the WHERE clause. If it changed in the meantime, 0
-- rows are affected and it throws DbUpdateConcurrencyException.
-- In an API: the version goes to the client (ETag / version field) and comes
-- back. On conflict: 409 Conflict (or 412 with If-Match), and the user reloads.
-- In a background job: reload and retry (or merge per field).
-- ======================================================================

-- UPDATE generated by EF Core with the concurrency token in WHERE
UPDATE products SET list_price = @p0
WHERE id = @p1 AND xmin = @p2;        -- 0 rows → DbUpdateConcurrencyException

// ======================================================================
// Q: Two users edit the same product at the same time. What happens and how do
// you prevent it?
//
// By default: last write wins. The second user overwrites the first user's
// change without knowing it (lost update). Solution: optimistic concurrency with
// a concurrency token.
// EF Core puts the token in the WHERE clause. If it changed in the meantime, 0
// rows are affected and it throws DbUpdateConcurrencyException.
// In an API: the version goes to the client (ETag / version field) and comes
// back. On conflict: 409 Conflict (or 412 with If-Match), and the user reloads.
// In a background job: reload and retry (or merge per field).
// ======================================================================

// SQL Server: rowversion that changes automatically on every UPDATE
public class Product
{
    public int Id { get; set; }
    public decimal ListPrice { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = [];
}

// PostgreSQL (Npgsql): the xmin system column as the token, no extra column
public uint Version { get; set; }
modelBuilder.Entity<Product>().Property(p => p.Version).IsRowVersion();

// Or for specific fields only
[ConcurrencyCheck] public decimal ListPrice { get; set; }

// PUT /products/{sku}: the version the client saw becomes the "original value"
var product = await db.Products.SingleAsync(p => p.Sku == sku, ct);
db.Entry(product).Property(p => p.Version).OriginalValue = request.Version;
product.ListPrice = request.Price;
try
{
    await db.SaveChangesAsync(ct);
    return Results.NoContent();
}
catch (DbUpdateConcurrencyException)
{
    return Results.Conflict("The product was changed by someone else. Reload and retry.");
}

// Background job: reload and retry on a concurrency conflict
for (var attempt = 1; ; attempt++)
{
    try
    {
        product.Stock += delta;
        await db.SaveChangesAsync(ct);
        break;
    }
    catch (DbUpdateConcurrencyException ex) when (attempt < 3)
    {
        var entry = ex.Entries.Single();
        var current = await entry.GetDatabaseValuesAsync(ct)
                      ?? throw new NotFoundException();       // deleted in the meantime
        entry.OriginalValues.SetValues(current);   // new token
        entry.CurrentValues.SetValues(current);    // "database wins", then we reapply
    }
}

-- ======================================================================
-- Q: Which index would you add?
--
-- A composite index with the equality columns first and the sort column last.
-- Verify with EXPLAIN (ANALYZE, BUFFERS). For large datasets use keyset
-- pagination (WHERE created_at < @lastSeen) instead of OFFSET.
-- ======================================================================

-- Query to optimize
SELECT id, order_number, total_net
FROM orders
WHERE customer_id = @c AND status = 'Open'
ORDER BY created_at DESC
LIMIT 20;

-- Composite covering index: equality columns first, sort column last
CREATE INDEX CONCURRENTLY ix_orders_customer_status_created
    ON orders (customer_id, status, created_at DESC)
    INCLUDE (order_number, total_net);   -- covering index

// ======================================================================
// Q: This is slow with 10,000 orders. Why?
//
// - N+1 queries: one query per order.
// - The whole table is loaded into memory.
// Fix: do the calculation in the database.
// Other tools: AsNoTracking() for reads, projection with Select instead of
// Include, AsSplitQuery() when many Includes cause a cartesian explosion. To see
// the SQL: LogTo or Application Insights.
// ======================================================================

// Slow version (lazy loading)
var orders = await _db.Orders.ToListAsync();
foreach (var o in orders)
    total += o.Lines.Sum(l => l.Quantity * l.UnitPrice); // lazy loading

// Fix: compute the sum in the database
var total = await _db.OrderLines
    .Where(l => l.Order.CreatedAt >= from)
    .SumAsync(l => l.Quantity * l.UnitPrice, ct);