Design & Architecture

EF Core

Intermediate

Entity Framework Core is our default object-relational mapper for the .NET stack: it maps C# entities to relational tables, tracks changes, and generates SQL so you write less data-access boilerplate. It is excellent for transactional, domain-shaped work — and it has sharp edges (N+1 queries, accidental tracking, leaky entities) that you must know to use it safely.

EF Core lets you query with LINQ and persist object graphs without hand-writing most SQL, and its change tracker and `SaveChanges` give you a clean unit-of-work and transaction boundary. That productivity is real, and it is why EF Core is our default for command-side, domain-centric persistence.

The cost of that abstraction is that it hides what the database is doing, and the hidden behaviour is where the bugs live: a lazy navigation property that fires a query per row, a read that needlessly tracks entities, an EF entity bound straight to an HTTP request and over-posted. This page is about using EF Core deliberately. Where you need raw read performance or full SQL control, reach for Dapper instead — the two coexist happily, often in the same codebase split along CQRS lines. See also SQL Performance Tuning and Data Integrity & Transactions.

Query deliberately

DoUse `AsNoTracking()` for read-only queries. Tracking exists to support writes; on a pure read it wastes memory and CPU building a change-tracking graph you never use.
DoLoad related data explicitly with `Include`/projection and watch for N+1: a navigation accessed in a loop can fire one query per row. Project to a DTO with `Select` to fetch exactly what you need.
DoSelect only the columns you use rather than materialising whole entities for display. Wide `SELECT *`-style fetches are slow and pull data (and PII) you didn't need (see Data Protection & Privacy).
AlwaysKeep `DbContext` scoped to the request/unit of work and never share one instance across threads — it is not thread-safe, and a captured singleton context is a classic, severe bug (see Inversion of Control, Concurrency).
NeverConcatenating or interpolating user input into `FromSqlRaw`. Use parameterised `FromSqlInterpolated` or parameters — raw string-built SQL is an injection hole (see Trust Boundaries).

Tracked read + N+1

var cases = db.KycCases.ToList();          // tracked, all columns
foreach (var c in cases)
    total += c.Documents.Count();          // lazy load -> 1 query per case

Every case triggers another round trip for its documents, and all of it is needlessly change-tracked. Fine for 3 rows, a disaster for 30,000.

No-tracking projection

var rows = await db.KycCases
    .AsNoTracking()
    .Select(c => new CaseRow(c.Id, c.Reference, c.Documents.Count))
    .ToListAsync(ct);   // one query, only the columns needed

A single SQL statement returns exactly the shape the screen needs, with no tracking overhead and no per-row round trips.

Persist safely

DoTreat `SaveChanges` as your transaction boundary; for multi-step writes that must be atomic, wrap them in an explicit transaction and keep related changes together (see Data Integrity & Transactions).
AlwaysAwait async methods (`ToListAsync`, `SaveChangesAsync`) and flow a `CancellationToken`; never block on `.Result`/`.Wait()` over EF calls — that risks deadlocks and exhausts the thread pool.
DoUse optimistic concurrency (a rowversion/`[Timestamp]` token) on records that can be edited concurrently, so a last-writer-wins overwrite can't silently clobber another user's change (see Concurrency).
NeverBinding an EF entity directly to a request DTO and calling `SaveChanges`. That is over-posting/mass-assignment — a client can set fields you never intended. Bind to a DTO and map explicitly (see Trust Boundaries).

Manage schema and migrations

DoUse EF migrations as the versioned source of truth for schema changes, reviewed like any other code, and apply them deliberately as part of deployment (see Schema Versioning).
DoMap `decimal` with explicit precision/scale for money and never `float`/`double`; the default mapping can silently truncate (see Money & Currency).
ConsiderInspecting the generated SQL (logging, `ToQueryString()`) for hot queries during review, so you catch a bad plan or N+1 before it reaches production.

Self-review checklist

AskIs this a read-only query? Then is it `AsNoTracking` and projecting only what's needed?
AskCould any navigation in a loop be firing one query per row?
AskIs my DbContext properly scoped, never shared across threads or captured in a singleton?
AskAm I binding an entity straight from the request, and could a client over-post fields?

Why it matters: EF Core removes most data-access boilerplate and gives you a clean unit-of-work, which makes it our default for transactional, domain-shaped persistence. But it works by hiding the database, and the things it hides — N+1 queries, needless tracking, over-posting, raw-SQL injection, a mis-scoped context — are exactly where correctness, performance, and security go wrong. Used deliberately, with no-tracking reads, explicit loading, DTO binding, and proper transactions, it is productive and safe; used on autopilot, it quietly creates slow, leaky, or vulnerable code.