Persistence — EF Core
0. Project context
Resolve once per session, from the working directory: the solution (*.sln / *.slnx, else
*.csproj — ask if several match) and its projects by role: <Api>, <Application>, <Core>,
<Infrastructure>, <Tests>. Those placeholders mean the real project names; paths below are relative
to the solution root. Take product names from the repo — never invent one. If the solution is not
layered this way, say so and ask before imposing this structure.
1. Database-First — Non-Negotiable Rules
This skill assumes Database-First: the database schema is authoritative, EF Core maps to it, and there
are no IEntityTypeConfiguration classes and no EF migrations — a schema change is a SQL script followed by
a re-scaffold. Confirm that is how this repo works (no Migrations/ folder, scaffolded entities) before
applying it. If the repo is Code-First with migrations, keep its approach and apply only §3–§6 here.
Rules, the prohibited list, and what OnModelCreating may contain:
reference/database-first-postgres.md.
1a. One schema per module
In a modular monolith each module owns a database schema: a new table is created in its module's schema, raw
SQL is schema-qualified, no two modules write the same table, and there is still one AppDbContext and
one SaveChanges. Read reference/schema-per-module.md before adding a
table or a cross-module relationship. Skip it for a single-schema database.
2. DbContext Registration
The DbContext is registered once, in Infrastructure/DependencyInjection.cs, with the connection string read
from configuration (never a literal). All EF-specific code stays in Infrastructure; Application sees only
the narrow ports (IUnitOfWork, IRepository<T>, IReadQuery<T>).
PostgreSQL registration: reference/database-first-postgres.md.
3. Generic Repository Pattern
All data access goes through IRepository<T>. Direct AppDbContext injection into handlers is forbidden.
Required interface surface (Core/Interfaces/IRepository.cs):
public interface IRepository<T> where T : class
{
Task<T?> GetByIdAsync(Guid id, CancellationToken ct = default);
Task<IEnumerable<T>> GetAllAsync(CancellationToken ct = default);
Task<T?> SingleOrDefaultAsync(Expression<Func<T, bool>> predicate, CancellationToken ct = default);
Task<bool> ExistsAsync(Expression<Func<T, bool>> predicate, CancellationToken ct = default);
Task AddAsync(T entity, CancellationToken ct = default);
void Update(T entity);
void Remove(T entity);
}
Infrastructure implementation: GenericRepository<T> wraps AppDbContext.
Write path vs read path
The surface above is the write path — load an aggregate, mutate it, commit. It is deliberately tracking-enabled and entity-shaped.
It is not a query API, and must not be stretched into one. Two rules follow:
GetAllAsync()is for small, bounded reference tables only. It loads every row of the table with change tracking on. Never call it to back a dashboard, an admin queue, a list screen, or anything a user can grow. Filtering the result in memory ((await GetAllAsync()).Where(...)) means the predicate never reaches PostgreSQL — the whole table crosses the wire first.- Read-heavy queries get a read port, not a repository method. Adding
Include/paging/sorting parameters toIRepository<T>ends in an unreadable method with eight optional arguments.
Read port for queries (Core/Interfaces/IReadQuery.cs)
Query handlers may inject a read-only port that projects straight to a DTO — no tracking, no entity leaving Infrastructure:
public interface IReadQuery<T> where T : class
{
// AsNoTracking is applied inside the implementation; callers cannot forget it.
Task<TDto?> SingleAsync<TDto>(
Expression<Func<T, bool>> predicate,
Expression<Func<T, TDto>> selector,
CancellationToken ct = default);
Task<PagedResult<TDto>> PageAsync<TDto>(
Expression<Func<T, bool>> predicate,
Expression<Func<T, TDto>> selector,
int page, int pageSize,
Expression<Func<T, object>>? orderBy = null,
bool descending = false,
CancellationToken ct = default);
}
- The
selectorprojection is what avoids N+1 — EF translates it into a singleSELECTof exactly the needed columns, including across navigations, with noIncluderequired. PageAsyncreturns theitems/total/page/pageSizeenvelope list endpoints use (see the api-design skill). Every list endpoint pages — no unbounded user-facing collection.pageSizeis clamped server-side (cap it, e.g. 100); never pass a client value through unbounded.- Ownership scoping still applies here — the
predicatemust carry the current user's constraint, exactly as on the write path. - Commands still go through
IUnitOfWork. A handler that reads-then-writes uses the repository, not this port, so the entity stays tracked.
AppDbContext remains forbidden in handlers either way.
4. Unit of Work Pattern
IUnitOfWork is the only interface handlers may inject for data access:
public interface IUnitOfWork : IDisposable
{
IRepository<T> Repository<T>() where T : class;
Task<int> SaveChangesAsync(CancellationToken ct = default);
}
Two shapes satisfy this. The generic accessor above works before the domain exists and never needs editing when an aggregate is added. A project that prefers compile-time discoverability may instead expose one named property per aggregate root:
IRepository<Invoice> Invoices { get; } // illustrative - use the repo's own aggregates
Resolve which shape this repo uses from the existing code (§0); do not introduce the other
alongside it. Whichever it is, SaveChangesAsync stays the single commit point.
5. Atomic Transaction Rule — Critical
Multiple table updates within a single Handler must always be committed in a single
SaveChangesAsynccall.
Correct Pattern:
// Stage every change first (entity names here are illustrative)
await _unitOfWork.Repository<Order>().AddAsync(order, ct);
await _unitOfWork.Repository<AuditEntry>().AddAsync(auditEntry, ct);
// Single atomic commit — both rows in one DB transaction
await _unitOfWork.SaveChangesAsync(ct);
Forbidden Pattern:
await _unitOfWork.Repository<Order>().AddAsync(order, ct);
await _unitOfWork.SaveChangesAsync(ct); // ❌ First save
await _unitOfWork.Repository<AuditEntry>().AddAsync(auditEntry, ct);
await _unitOfWork.SaveChangesAsync(ct); // ❌ Second save — not atomic!
The shapes that require it — find the equivalents in this repo rather than assuming a list:
| Shape | Tables committed atomically |
|---|---|
| A create that must be audited | the new aggregate + its audit row |
| A state change that must be audited | the updated aggregate + its audit row |
| An action that issues a credential or token | the actor row + the issued token + the audit row |
| A review/approval that also records the decision | the reviewed row (+ any audit row) |
If a handler writes to more than one table for any reason, it commits once.
6. Entity Conventions
- Follow the existing schema's key type. For a new table default to a
Guidkey; an append-only log table may use a database-generatedlong. - Navigation properties use
null!for required relations (not initialized tonew()). - Follow the schema's delete model: hard delete via
Remove()unless the table has a soft-delete column — never mix the two for one entity. DateTimefields are alwaysDateTime.UtcNow— neverDateTime.Now.DateOnlyfor date-only columns (a start date, an end date).- Prefer LINQ over raw SQL unless performance requires otherwise.
- Do not duplicate entity definitions — use scaffolded entity types only.
Checklist Before Completing Work
- Database-First repo: no new
IEntityTypeConfiguration,Configurations/folder, or migration; entities match the scaffolded model - Handlers use
IUnitOfWork(writes) orIReadQuery<T>(queries) — never directAppDbContext - List/dashboard/queue queries are paged and projected — no
GetAllAsync()behind a user-growable table - Multi-table handler changes use a single
SaveChangesAsync - Persistence code resides in Infrastructure only
- New table created in its module schema; raw SQL schema-qualified; the module map updated if a table changes module