Capstone: Design, Implement, Test, Tune, Secure, Deploy, and Defend a Production EF Core Data Layer

Learning outcomes

1. Capstone brief: defend ServiceHub as if another team must operate it

The final deliverable is not “a CRUD API that runs.” You are the owner of a multi-tenant ServiceHub data layer that another team must deploy, test, monitor and repair. Every major decision needs an artifact: model configuration, migration SQL, generated command, tracker evidence, test result, execution plan, metric, runbook or explicit provider constraint.

2. Required architecture: one portable core, explicit provider boundaries

Use the established SQLite mandatory path for a free local capstone and optionally qualify SQL Server/PostgreSQL/MySQL with the provider matrix from Chapters 19–21. Keep provider-specific features behind intentional adapters/configuration, not accidental LINQ scattered across the application.

3. The capstone command path

public sealed class ReviseWorkOrderCommandHandler(    ServiceHubContext db,    IWorkOrderRepository orders,    TimeProvider clock){    public async Task HandleAsync(int id, string summary, CancellationToken ct)    {        var order = await orders.LoadForRevisionAsync(id, ct)            ?? throw new KeyNotFoundException();        order.ReviseSummary(summary, clock); // invariant + Revision + domain event        await db.SaveChangesAsync(ct);        // aggregate + outbox in one transaction    }}

UPDATE "work_orders"SET "summary" = @summary, "revision" = @newRevisionWHERE "work_order_id" = @id  AND "revision" = @originalRevision;INSERT INTO "outbox_messages" (...)VALUES (...);-- both participate in the same SaveChanges transaction

The review must explain tenant filter/write ownership, the original-versus-new revision predicate, outbox atomicity and how a concurrency conflict is surfaced/resolved.

4. The capstone read path must prove shape and plan

var query = db.WorkOrders    .TagWith("ServiceHub.Capstone.Queue.v1")    .AsNoTracking()    .Where(w => w.Priority >= 3)    .OrderByDescending(w => w.Priority)    .ThenBy(w => w.OpenedUtc)    .Take(100)    .Select(w => new WorkOrderQueueRow(        w.Id, w.WorkOrderNumber, w.Summary,        w.Priority, w.ServiceAddress.City));Console.WriteLine(query.ToQueryString());var rows = await query.ToListAsync(ct);

Capture the generated SQL, actual row count, data distribution and SQLite EXPLAIN QUERY PLAN (or the production provider’s actual plan tool). If you replace this path with raw SQL/Dapper, repeat the same correctness/security/performance evidence.

5. Test matrix: happy paths are the least interesting rows

6. Security acceptance criteria

The capstone must keep SQL values parameterized, dynamic query choices allow-listed, credentials out of source/logs, runtime DB identity separate from migration authority where the provider supports roles, sensitive EF logging disabled by default, and tenant isolation enforced in more than one layer. A successful IgnoreQueryFilters admin operation must have explicit authorization and audit evidence.

7. Deliberate failure review: identify architecture smells before launch

[ ] Generic repository that mirrors every DbSet method[ ] IQueryable exposed to untrusted/API consumers without shape policy[ ] External broker publish inside SavingChanges[ ] Every read materializes tracked aggregates[ ] Dapper/raw SQL bypasses tenant predicate or transaction ownership[ ] ExecuteUpdate used where aggregate events/concurrency tokens are required[ ] App replicas run schema migration on startup by default[ ] Pool/timeout values copied from a blog without target measurements[ ] EnableSensitiveDataLogging enabled in ordinary production[ ] SQLite tests claimed as proof of SQL Server/PostgreSQL semantics[ ] Down migration treated as the backup/rollback plan

Each checked item needs either a repair or a documented, tested reason it is safe in this topology.

8. Production evidence packet

Submit a small evidence bundle alongside the code:

1. Model: WorkOrder metadata, complex ServiceAddress columns, tenant filters, keys/FKs/indexes and Revision token.
2. SQL: one command update and one hot query with parameters/tags.
3. Tracker: before/after DebugView for one aggregate change.
4. Migrations: reviewed SQL/bundle plan, migration-history state and expand/contract note.
5. Tests: concurrency, transaction/outbox, tenant red-team and provider-fidelity results.
6. Performance: data volume/distribution, warmup/cache disclosure, query plan and bounded latency baseline.
7. Operations: dashboard signals, backup/restore evidence and two incident runbooks.

9. Mandatory final lab: design, implement, test, tune, secure and deploy

1. Start from the accumulated ServiceHub project—not a new sample.
2. Implement one tenant-aware WorkOrder command with aggregate invariant, Revision and domain event.
3. Persist its outbox row atomically and run an idempotent local relay.
4. Implement the bounded queue read; capture SQL and plan; optionally compare Dapper 2.1.79.
5. Run the Chapter 23 migration/concurrency/transaction/query-count/provider tests and Chapter 22 tenant adversarial tests.
6. Generate a deployment migration artifact and execute it against a disposable environment with a separate deployment configuration.
7. Run readiness/observability checks, inject one timeout/lock/query-regression incident, and follow the runbook.
8. Perform a SQLite-aware backup and restore verification for the mandatory local path.
9. Write a one-page architecture defense: where EF Core is used, where it is not, and which evidence supports every exception.

10. Course completion: the transferable skill is prediction and evidence

You have completed the EF Core path when you can predict the model/query/tracker/write behavior, inspect what EF generates, identify what the provider/database actually guarantees, and choose architecture/operations from evidence. EF Core is neither a black box nor a religion: it is one part of a relational data system whose correctness depends on domain boundaries, SQL semantics, transactions, deployment, security, testing and observability working together.

Authoritative references

• DbContext lifetime/configuration — unit-of-work lifecycle and production context rules
• EF Core testing strategy — real-database/provider fidelity expectations
• Applying migrations — production schema deployment strategies and migration locking
• EF Core performance guidance — pooling/query/runtime performance boundaries
• CQRS pattern — intentional read/write model separation
• EF Core SQL queries — safe handwritten SQL boundaries and parameterization