ASP.NET Core / Intermediate: Data, Identity, and Asynchronous Work / Lesson 11
EF Core and Persistence Boundaries
Review unit-of-work, provider translation, tracking, concurrency, migrations, and transactions without treating in-memory LINQ as a database test.
On this page
Lesson goals
- Explain a short-lived non-thread-safe DbContext.
- Separate IQueryable translation from local enumeration.
- Match persistence tests to the actual provider and transaction contract.
A context is an owned unit of work
DbContext tracks entity state and coordinates provider-backed operations over a short unit of work. It is not thread-safe and should not be shared across parallel operations. Await an async query before using the context again, and dispose it after the owned work completes. A request-scoped default can fit many applications but does not automatically define every background or multi-operation transaction.
Context pooling and database connection pooling are different mechanisms. Pooling can reuse context instances and therefore needs care with custom state such as tenant information. Do not infer a fresh business identity from a reused object. Keep provider/engine versions, connection ownership, and lifecycle explicit.
Provider setup is not executed in this course
The non-runnable excerpt shows where a provider and DbContext would be registered. It needs the correct EF/provider packages, model, trusted connection configuration, and actual engine. The course installs no such packages and opens no database. A plausible UseSqlServer line is not evidence of SQL translation, permissions, transactions, or migrations.
Keep connection secrets out of source and logs. A configured provider should be chosen deliberately for the application’s required data semantics. SQLite, EF’s InMemory provider, and a production relational engine are not interchangeable test substitutes, even when simple CRUD examples look similar.
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(connectionString));
This fragment is not compiled or run; the EF provider, context model, and connection are intentionally absent.
A query can remain deferred until the context is gone
IQueryable represents provider-interpreted expressions until execution. A returned deferred query can later run outside the context’s lifetime or expose unbounded query composition to callers. Materialize a bounded projection while ownership is valid, and document tracking/no-tracking choice, ordering, pagination, cancellation, and expected result grain.
AsEnumerable and materialization create execution boundaries that can move work to memory. A C# function valid on a list may not translate to SQL. Includes, split queries, and eager/lazy patterns can change round trips and row multiplication. Inspect actual provider behavior with approved tests rather than guessing from the fluent chain.
A local projection test proves only local semantics
The complete program filters and projects embedded records to an API DTO using LINQ to Objects. It verifies the intended shape and order without a database. This can serve a narrow domain/projection test, but it cannot validate SQL null/collation behavior, indexes, transaction isolation, query cost, or provider translation.
Avoid loading entire tables merely because local filtering is easy. Define page sizes and stable ordering and project only needed fields under the provider’s supported query model. A no-tracking read can reduce tracking work when appropriate, but it is not an authorization or thread-safety feature.
using System;
using System.Linq;
var rows = new[] { new Row(2, true), new Row(1, false), new Row(3, true) };
var result = rows.Where(row => row.Active).OrderBy(row => row.Id).Select(row => new ReportDto(row.Id)).ToArray();
Console.WriteLine(string.Join(",", result.Select(item => item.Id)));
internal sealed record Row(int Id, bool Active);
internal sealed record ReportDto(int Id);
2,3
The local sequence yields 2,3; no EF query or database engine executes.
A successful save has a specific transaction scope
SaveChanges can provide atomic behavior for its changes when the provider supports transactions, but it is not a transaction across every HTTP call, message, or external effect. Optimistic concurrency tokens can detect stale writes and require a documented conflict/retry policy. Blindly retrying an uncertain non-idempotent operation can duplicate effects.
Migrations are versioned infrastructure/data changes requiring review, compatibility, rollback/roll-forward, and an authorized deployment owner. Do not run them automatically against a configured database from a lesson. Tests should cover constraints, concurrency, query translation, and transaction behavior with the real intended provider in a separate approved environment. The local projection example establishes none of those engine claims.
Common errors and fixes
One DbContext is used by parallel request work.
- Why it happens
- A non-thread-safe unit of work is shared.
- How to fix it
- Use separate owned contexts/scopes or sequentially await the intended unit.
- Prevent it next time
- Test lifetime and concurrency assumptions.
A list test is reported as SQL-provider validation.
- Why it happens
- Local and provider execution were conflated.
- How to fix it
- Use the actual provider for database claims under approved isolation.
- Prevent it next time
- Label unit/projection tests narrowly.
An unbounded deferred query escapes the request.
- Why it happens
- Execution and context ownership are uncontrolled.
- How to fix it
- Expose a bounded operation/result contract.
- Prevent it next time
- Test materialization timing and result size.
Check your understanding
1. Why can EF’s InMemory provider pass a test that would fail on a relational engine?
Show worked solution
It does not reproduce all relational constraints, SQL translation, collation/null behavior, transactions, or concurrency semantics of the target provider. It can be useful for a carefully scoped test, but cannot certify database behavior. Use the intended provider under approved isolation for those claims, and keep domain tests independent of provider assumptions where possible.
2. A context updates a row and sends an external message. Does SaveChanges make both effects atomic?
Show worked solution
No. Its transaction scope does not automatically include an external broker or HTTP service. Define an outbox or another justified consistency/reconciliation design, idempotency, and retry policy. Test crashes around each commit boundary. A successful save alone cannot prove that the message was sent once or that a retry will not duplicate the business action.
Key takeaways
- DbContext is a short owned unit of work, not a parallel shared service.
- Provider translation and local LINQ need different validation evidence.
- Transactions, concurrency, migrations, and external effects require explicit boundaries.