Capstone: one checked reservation unit of work

Make the reservation result depend on the whole relational unit

A reservation needs more than subtracting stock in a local object. The operation must locate the intended tenant’s item, validate quantity against available stock, persist the stock change and reservation record together, and return success only after commit. A unique tenant/reference index rejects this fixture’s repeated reference, while an application-managed item version protects stale updates. Those mechanisms serve different contracts. The example deliberately returns duplicate-ref rather than pretending a duplicate is an idempotent success with a reconciled earlier result. Distributed inventory, payment and retry protocols remain separate requirements.

Trace success and a rejected duplicate through real SQLite

The fixture seeds A/R1 with stock five, price 125 cents and version one. Reserving two units commits a 250-cent reservation. A second request using the same reference attempts another change but the unique index rejects its save; the explicit transaction rolls back. An excessive quantity returns insufficient and a B-context lookup returns missing. A fresh A context then verifies stock three, version two and one reservation. The result transcript therefore has stored-state assertions behind it, not only friendly status strings. The actual relationship, filter, token and indexes are included in the downloadable model.

using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

await using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=True");
await connection.OpenAsync();
var options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).Options;
await using (var seed = new LabDb(options, "A")) { await seed.Database.EnsureCreatedAsync(); seed.Items.Add(new Item { TenantId = "A", Sku = "R1", Stock = 5, PriceCents = 125, Version = 1 }); await seed.SaveChangesAsync(); }
Check(await Reserve(options, "A", "R1", 2, "REF1", CancellationToken.None) == "reserved:250", "reservation accepted"); Console.WriteLine("reserved:250");
Check(await Reserve(options, "A", "R1", 1, "REF1", CancellationToken.None) == "duplicate-ref", "duplicate reference rejects transaction"); Console.WriteLine("duplicate-ref");
Check(await Reserve(options, "A", "R1", 99, "REF2", CancellationToken.None) == "insufficient", "stock rejection"); Console.WriteLine("insufficient");
Check(await Reserve(options, "B", "R1", 1, "REF3", CancellationToken.None) == "missing", "tenant-scoped lookup"); Console.WriteLine("other-tenant:missing");
await using (var verify = new LabDb(options, "A")) { var item = await verify.Items.AsNoTracking().SingleAsync(); Check(item.Stock == 3 && item.Version == 2 && await verify.Reservations.CountAsync() == 1, "fresh atomic result"); Console.WriteLine("stock:3"); Console.WriteLine("reservations:1"); }
static async Task<string> Reserve(DbContextOptions<LabDb> options, string trustedTenant, string sku, int quantity, string reference, CancellationToken token)
{
    if (quantity <= 0 || string.IsNullOrWhiteSpace(reference)) return "invalid";
    await using var db = new LabDb(options, trustedTenant); await using var transaction = await db.Database.BeginTransactionAsync(token);
    var item = await db.Items.SingleOrDefaultAsync(x => x.Sku == sku, token); if (item == null) return "missing"; if (item.Stock < quantity) return "insufficient";
    item.Stock -= quantity; item.Version = checked(item.Version + 1);
    db.Reservations.Add(new Reservation { TenantId = trustedTenant, Reference = reference, ItemId = item.Id, Quantity = quantity, TotalCents = checked((long)item.PriceCents * quantity) });
    try { await db.SaveChangesAsync(token); await transaction.CommitAsync(token); return "reserved:" + checked((long)item.PriceCents * quantity); }
    catch (DbUpdateException ex) when (ex.InnerException is SqliteException { SqliteErrorCode: 19, SqliteExtendedErrorCode: 2067 }) { await transaction.RollbackAsync(CancellationToken.None); return "duplicate-ref"; }
}

static void Check(bool condition, string name) { if (!condition) throw new InvalidOperationException("CHECK: " + name); }

Check a quote rule without inventing relational guarantees

The .NET9 practice receives stock, quantity and integer unit price. It rejects invalid values, returns insufficient when quantity exceeds supplied stock and otherwise calculates a checked long total. Its 250 and insufficient outputs establish that narrow decision on supplied data. No stock row is locked, no reference is stored and no transaction commits. The two additional portable projects expand independent rule checks while retaining that BCL-only boundary. Use them to specify expected application behavior, then verify actual atomicity, uniqueness and concurrency with the real EF provider rather than a custom class imitating its namespace.

Keep rejected units and uncertain outcomes visible

The duplicate exception filter recognizes the known SQLite unique-index case in this controlled operation; another schema or provider needs a reviewed classification strategy. A DbUpdateConcurrencyException is not silently retried, and rollback cleanup has its own noncancelled token so caller cancellation is not mistaken for completed recovery. Early missing or insufficient branches leave without commit and dispose their transaction. Query filters also do not authorize writes generally: the trusted tenant and constructed reservation are explicit application responsibilities. A real uncertain commit may require reconciliation by reference, which this fixture does not claim to solve.

Practice: challenge one stored invariant at a time

Add independent cases for zero quantity, a duplicate reference under another permitted tenant and a stale item version updated by another context before reservation save. Verify final stock and reservation rows from fresh contexts after each result. Do not combine several invalid conditions into one request, because the first rejection could conceal a missing later check. For a genuinely idempotent protocol, define how a repeated reference returns the earlier result only when its payload matches, then test that as a separate contract. A unique index alone supplies rejection, not automatic semantic deduplication or remote side-effect recovery.

• Hint: verify that rejection leaves stored stock and version unchanged.
• Hint: reference uniqueness and an idempotent replay result are different contracts.

Practice: return a structured decision with no hidden effects

Extend the pure quote function to return a structured accepted quote or named rejection, preserving the supplied SKU, quantity and unit price. Test exact-stock acceptance, negative input, a missing SKU and checked arithmetic boundaries independently. Keep batch duplicate-reference validation separate from stock arithmetic. The model should remain deterministic and side-effect-free so another implementation can satisfy the same expected decisions. When an EF adapter adopts it, review when data was read and how concurrency evidence is preserved; a correct calculation on stale supplied stock does not establish that a real reservation can safely commit.

• Hint: a pure accepted quote is a proposal until storage commits the required unit.
• Hint: preserve enough request identity to reconcile, but do not claim the model performs reconciliation.

Keep the final evidence provider-specific and complete

The external project validates a successful reservation, unique-reference rollback, insufficient-stock rejection and tenant-scoped lookup with fresh SQLite assertions. It does not claim SQL Server or PostgreSQL behavior, production migrations, full authentication or distributed transaction guarantees. The .NET9 practices establish their own logic outputs and independent checks. A useful release receipt names the SDK, package pins, schema strategy, real provider, transaction outcome and remaining operational limits. That complete statement is stronger than declaring an ORM capstone finished because one happy-path query produced a plausible total while constraints, rejected writes and context ownership remained untested.