EF Core Data Access and Transactions
Model relational persistence with EF Core, control query shape and tracking, protect transactions, and avoid N+1 queries or leaky IQueryable abstractions.
Before this lesson
Control EF Core query and tracking behavior
Design transaction boundaries
Detect N+1 and over-fetching problems
The short answer
EF Core maps LINQ expression trees to database queries and tracks entity changes inside a DbContext unit of work. Keep contexts scoped and short-lived, project only needed columns, inspect generated SQL, and make multi-write invariants transactional.
Build the runtime mental model
DbContext combines identity mapping, change tracking, query translation, and SaveChanges. It is not thread-safe. IQueryable builds a remote query until execution; materializing with ToListAsync crosses into in-memory work.
Advanced C# work improves when you separate language syntax, runtime behavior, and application policy. Write down which layer owns the guarantee in this lesson. Then identify the observable evidence—a compiler rejection, test result, generated query, trace, or measurement—that would prove the model correct.
Design the boundary deliberately
Use AsNoTracking for read-only projections, Include only when the complete graph is truly required, and project DTOs for query-specific shapes. Database constraints remain the final defense for uniqueness and referential integrity.
The starter isolates one part of the mental model so it can run in the browser. The exercise moves the same rule into a current local .NET project where packages, framework hosting, diagnostics, and multi-file tests are available.
using System;
using System.Collections.Generic;
using System.Linq;
class Program
{
static void Main()
{
var orders = new List<decimal> { 12m, 30m, 8m };
var projection = orders.Where(value => value >= 10m).Select(value => value * 1.1m);
Console.WriteLine(projection.Count());
}
}Expected output
2
Diagnose failure and misuse
Looping over entities and lazily querying each relationship creates N+1 calls. Returning IQueryable across layers leaks provider behavior and context lifetime. Catching a concurrency exception without a merge or retry policy simply repeats conflict.
Classify each failure as a contract violation, transient operational failure, permanent dependency response, concurrency conflict, or programmer defect. That classification determines whether to reject, retry, compensate, cancel, or fail fast. A generic catch-and-continue policy destroys the information needed to make that decision.
| Question | Evidence to inspect | Decision |
|---|---|---|
| Is the input valid? | Validation result and boundary examples | Reject with a stable contract |
| Is the failure transient? | Typed status, exception, and policy context | Retry only when bounded and safe |
| Is state still consistent? | Invariant and transaction outcome | Commit, compensate, or abort |
| Is performance acceptable? | Representative latency and allocation data | Keep simple or optimize one cause |
Apply the concept in production
Apply migrations through a controlled deployment step, test against the real database engine, log slow query shapes, and index from measured access paths. Define transaction ownership at an application use-case boundary.
Finish by making the result operable. Add structured diagnostics at the boundary, propagate cancellation, avoid sensitive data, and record SDK and dependency versions. Test the public behavior instead of private implementation details. If a framework or provider performs translation, serialization, concurrency, or I/O, include at least one test against the real production technology.
A senior-level review should be able to answer four questions: what contract is promised, who owns lifetime and cleanup, how failures become visible, and what evidence supports the design. If any answer depends on “the framework probably handles it,” inspect the documentation or runtime behavior and turn the assumption into a checked decision.
Quick knowledge check
Answer before you reveal.
01Why should DbContext normally be short-lived and scoped?
It tracks a unit of work, is not thread-safe, and grows costly and stale when retained across unrelated operations.
02What must happen before adding complexity to this design?
State the requirement, preserve a correct baseline, collect evidence, and explain how the proposed mechanism improves a specific quality.
Exercise
Practice challenge
Implement an EF Core order query with projection, no tracking, pagination, transaction-safe creation, and integration tests against the target database.
Requirements
- The implementation states its contract and ownership boundary explicitly
- Automated checks cover the successful path and at least two meaningful failures
- Diagnostics expose failure context without secrets or swallowed exceptions
- The project documents required SDK, packages, setup, run, and test commands
Optional extension: Measure or load-test the critical path and record whether the evidence justifies another optimization or abstraction.
Open in C# compilerLesson checkpoint
One small step locks it in
Mark this lesson complete, then keep the momentum going.
Clear up the details
Frequently asked questions
When should I use ef core data access and transactions?
Use it when its explicit tradeoff solves a measured requirement or clarifies an owned boundary. Keep the simpler design when the additional mechanism does not improve correctness, operability, or changeability.
Does the browser compiler cover the complete production setup?
No. It runs the focused starter program. Framework, package, database, benchmark, and multi-project work requires a current local .NET SDK and the project commands described in the exercise.
What evidence should I keep after the exercise?
Keep the acceptance cases, automated tests, diagnostic or benchmark output where relevant, and a short decision note describing the chosen boundary and rejected alternative.