rudironsoni dotnet-efcore-architecture
Strategic architectural patterns for EF Core data layers. Covers read/write model separation, aggregate boundary design, repository vs direct DbContext policy, N+1 query governance, row limit enforcement, and projection patterns. These patterns guide how to structure a data layer -- not how to write individual queries (see [skill:dotnet-efcore-patterns] for tactical usage).
Scope
- Read/write model separation and CQRS patterns
- Aggregate boundary design and repository policy
- N+1 query governance and row limit enforcement
- Projection patterns and query optimization strategy
Out of scope
- Tactical EF Core usage (DbContext lifecycle, AsNoTracking, migrations, interceptors) -- see [skill:dotnet-efcore-patterns]
- Data access technology selection (EF Core vs Dapper vs ADO.NET) -- see [skill:dotnet-data-access-strategy]
- DI container mechanics -- see [skill:dotnet-csharp-dependency-injection]
- Async patterns -- see [skill:dotnet-csharp-async-patterns]
- Integration testing data layers -- see [skill:dotnet-integration-testing]
Cross-references: [skill:dotnet-efcore-patterns] for tactical DbContext usage and migrations, [skill:dotnet-data-access-strategy] for technology selection, [skill:dotnet-csharp-dependency-injection] for service registration, [skill:dotnet-csharp-async-patterns] for async query patterns.
Package Prerequisites
Examples in this skill use PostgreSQL (UseNpgsql). Substitute the provider package for your database:
| Database | Provider Package |
|---|---|
| PostgreSQL | Npgsql.EntityFrameworkCore.PostgreSQL |
| SQL Server | Microsoft.EntityFrameworkCore.SqlServer |
| SQLite | Microsoft.EntityFrameworkCore.Sqlite |
All examples also require the core Microsoft.EntityFrameworkCore package (pulled in transitively by provider
packages).
Read/Write Model Separation
Separate read models (queries) from write models (commands) to optimize each path independently. This is not full CQRS -- it is a practical separation using EF Core features.
Approach: Separate DbContext Types
// Write context: full change tracking, navigation properties, interceptors
public sealed class WriteDbContext : DbContext
{
public DbSet<Order> Orders => Set<Order>();
public DbSet<Product> Products => Set<Product>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfigurationsFromAssembly(typeof(WriteDbContext).Assembly);
}
}
// Read context: no-tracking by default, optimized for projections
public sealed class ReadDbContext : DbContext
{
public DbSet<Order> Orders => Set<Order>();
public DbSet<Product> Products => Set<Product>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfigurationsFromAssembly(typeof(ReadDbContext).Assembly);
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
// Note: this is supplemental -- primary config is in DI registration
}
}
```text
### Registration
```csharp
// Write context: standard tracking, connection resiliency
builder.Services.AddDbContext<WriteDbContext>(options =>
options.UseNpgsql(connectionString, npgsql =>
npgsql.EnableRetryOnFailure(maxRetryCount: 3)));
// Read context: no-tracking, optionally pointed at a read replica
builder.Services.AddDbContext<ReadDbContext>(options =>
options.UseNpgsql(readReplicaConnectionString ?? connectionString)
.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
```text
### When to Separate
| Scenario | Recommendation |
| ----------------------------------- | --------------------------------------------------- |
| Simple CRUD app | Single `DbContext` with per-query `AsNoTracking()` |
| Read-heavy API with complex queries | Separate read/write contexts |
| Read replica database | Separate contexts with different connection strings |
| CQRS architecture | Separate contexts, possibly separate models |
**Start simple.** Use a single `DbContext` and per-query `AsNoTracking()` until you have a concrete reason to split
(different connection strings, divergent model shapes, or query complexity that justifies dedicated read models).
---
## Aggregate Boundaries
An aggregate is a cluster of entities that are always loaded and saved together as a consistency boundary. EF Core maps
well to aggregate-oriented design when navigation properties follow aggregate boundaries.
### Defining Aggregates
```csharp
// Order is the aggregate root -- it owns OrderItems
public sealed class Order
{
public int Id { get; private set; }
public string CustomerId { get; private set; } = default!;
public OrderStatus Status { get; private set; }
public DateTimeOffset CreatedAt { get; private set; }
// Owned collection -- part of the Order aggregate
private readonly List<OrderItem> _items = [];
public IReadOnlyList<OrderItem> Items => _items.AsReadOnly();
public void AddItem(int productId, int quantity, decimal unitPrice)
{
if (Status != OrderStatus.Draft)
throw new InvalidOperationException("Cannot add items to a non-draft order.");
_items.Add(new OrderItem(productId, quantity, unitPrice));
}
}
// OrderItem belongs to the Order aggregate -- no independent access
public sealed class OrderItem
{
public int Id { get; private set; }
public int ProductId { get; private set; }
public int Quantity { get; private set; }
public decimal UnitPrice { get; private set; }
internal OrderItem(int productId, int quantity, decimal unitPrice)
{
ProductId = productId;
Quantity = quantity;
UnitPrice = unitPrice;
}
private OrderItem() { } // EF Core constructor
}
```text
### EF Core Configuration for Aggregates
```csharp
public sealed class OrderConfiguration : IEntityTypeConfiguration<Order>
{
public void Configure(EntityTypeBuilder<Order> builder)
{
builder.HasKey(o => o.Id);
builder.Property(o => o.CustomerId).IsRequired().HasMaxLength(50);
builder.Property(o => o.Status).HasConversion<string>();
// Owned collection navigation -- cascade delete, no independent DbSet
builder.OwnsMany(o => o.Items, items =>
{
items.WithOwner().HasForeignKey("OrderId");
items.Property(i => i.ProductId).IsRequired();
});
// Alternatively, if OrderItem needs its own table with explicit FK:
// builder.HasMany(o => o.Items)
// .WithOne()
// .HasForeignKey("OrderId")
// .OnDelete(DeleteBehavior.Cascade);
//
// builder.Navigation(o => o.Items)
// .UsePropertyAccessMode(PropertyAccessMode.Field);
}
}
```text
### Aggregate Design Rules
1. **Load the entire aggregate** -- do not load partial aggregates. Use `Include()` for the owned collections.
2. **Save through the aggregate root** -- call `SaveChangesAsync()` on the root, not on child entities independently.
3. **Reference other aggregates by ID** -- do not create navigation properties between aggregate roots. Use `CustomerId`
(foreign key value), not `Customer` (navigation property).
4. **Keep aggregates small** -- large aggregates cause lock contention and slow loads. If a collection grows unbounded
(e.g., audit logs), it does not belong in the aggregate.
5. **One aggregate per transaction** -- modifying multiple aggregates in a single transaction creates coupling. Use
domain events or eventual consistency for cross-aggregate operations.
---
## Repository Policy
Whether to use the repository pattern or access `DbContext` directly is a team decision. Both approaches are valid in
.NET.
### Option A: Direct DbContext Access
```csharp
public sealed class CreateOrderHandler(WriteDbContext db)
{
public async Task<int> HandleAsync(
CreateOrderCommand command,
CancellationToken ct)
{
var order = new Order(command.CustomerId);
foreach (var item in command.Items)
{
order.AddItem(item.ProductId, item.Quantity, item.UnitPrice);
}
db.Orders.Add(order);
await db.SaveChangesAsync(ct);
return order.Id;
}
}
```text
**Pros:** Simple, no abstraction overhead, full LINQ power, easy to debug. **Cons:** Business logic can leak into query
methods, harder to unit test without a database.
### Option B: Repository per Aggregate Root
```csharp
public interface IOrderRepository
{
Task<Order?> GetByIdAsync(int id, CancellationToken ct);
Task AddAsync(Order order, CancellationToken ct);
Task SaveChangesAsync(CancellationToken ct);
}
public sealed class OrderRepository(WriteDbContext db) : IOrderRepository
{
public async Task<Order?> GetByIdAsync(int id, CancellationToken ct)
{
return await db.Orders
.Include(o => o.Items)
.FirstOrDefaultAsync(o => o.Id == id, ct);
}
public async Task AddAsync(Order order, CancellationToken ct)
{
await db.Orders.AddAsync(order, ct);
}
public Task SaveChangesAsync(CancellationToken ct)
{
return db.SaveChangesAsync(ct);
}
}
```text
**Pros:** Testable without a database, encapsulates query logic, enforces aggregate loading rules. **Cons:** Extra
abstraction layer, can become a leaky abstraction if LINQ is exposed, repository per aggregate can proliferate.
### Decision Guide
| Factor | Direct DbContext | Repository |
| -------------------- | ------------------------------ | ----------------------------- |
| Team size | Small, aligned | Large, varied experience |
| Test strategy | Integration tests with real DB | Unit tests with mocked repos |
| Query complexity | High (reports, projections) | Low-medium (CRUD, aggregates) |
| Aggregate discipline | Enforced by convention | Enforced by interface |
**Do not create generic repositories** (`IRepository<T>`). They add abstraction without value -- the generic interface
cannot express aggregate-specific loading rules (which Includes to use, which filters to apply). Repository interfaces
should be specific to the aggregate root they serve.
---
## N+1 Query Governance
N+1 queries are the most common EF Core performance problem. They occur when code iterates over a collection and
executes a query per element, instead of loading all data upfront.
### Detection
Enable sensitive logging in development to see SQL queries:
```csharp
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseNpgsql(connectionString)
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging() // Development only
.EnableDetailedErrors()); // Development only
```text
### Common N+1 Patterns and Fixes
**Pattern 1: Lazy loading in a loop**
```csharp
// BAD: N+1 -- each order.Items triggers a query
var orders = await db.Orders.ToListAsync(ct);
foreach (var order in orders)
{
var total = order.Items.Sum(i => i.Quantity * i.UnitPrice); // Lazy load!
}
// GOOD: Eager load with Include
var orders = await db.Orders
.Include(o => o.Items)
.ToListAsync(ct);
```text
**Pattern 2: Querying inside a loop**
```csharp
// BAD: N+1 -- one query per customer
foreach (var customerId in customerIds)
{
var orders = await db.Orders
.Where(o => o.CustomerId == customerId)
.ToListAsync(ct);
// ...
}
// GOOD: Single query with Contains
var orders = await db.Orders
.Where(o => customerIds.Contains(o.CustomerId))
.ToListAsync(ct);
```text
**Pattern 3: Missing projection**
```csharp
// BAD: Loads full entity graph, then maps in memory
var orders = await db.Orders
.Include(o => o.Items)
.Include(o => o.Customer)
.ToListAsync(ct);
var dtos = orders.Select(o => new OrderDto(...));
// GOOD: Project in the query -- no tracking, no extra data loaded
var dtos = await db.Orders
.Select(o => new OrderDto
{
Id = o.Id,
CustomerName = o.Customer.Name,
ItemCount = o.Items.Count,
Total = o.Items.Sum(i => i.Quantity * i.UnitPrice)
})
.ToListAsync(ct);
```text
### Governance Checklist
- **Disable lazy loading** -- do not install `Microsoft.EntityFrameworkCore.Proxies` or configure
`UseLazyLoadingProxies()`. Eager loading via `Include()` or projection via `Select()` makes data access explicit.
- **Review queries in code review** -- look for loops that access navigation properties or call `FindAsync` /
`FirstOrDefaultAsync` per element.
- **Use query tags** -- `db.Orders.TagWith("GetOrderSummary")` makes queries identifiable in logs and profiling tools.
- **Set up EF Core logging in development** -- every lazy load or unexpected query is visible in the console output.
---
## Row Limits and Pagination
Unbounded queries are a production risk. Always limit the number of rows returned.
### Keyset Pagination (Recommended)
Keyset pagination (also called cursor-based or seek pagination) is more efficient than offset pagination for large
datasets:
```csharp
public async Task<PagedResult<OrderSummary>> GetOrdersAsync(
string customerId,
int? afterId,
int pageSize,
CancellationToken ct)
{
const int maxPageSize = 100;
pageSize = Math.Min(pageSize, maxPageSize);
var query = db.Orders
.AsNoTracking()
.Where(o => o.CustomerId == customerId);
if (afterId.HasValue)
{
query = query.Where(o => o.Id > afterId.Value);
}
var items = await query
.OrderBy(o => o.Id)
.Take(pageSize + 1) // Fetch one extra to detect "has next page"
.Select(o => new OrderSummary
{
Id = o.Id,
Status = o.Status,
CreatedAt = o.CreatedAt,
Total = o.Items.Sum(i => i.Quantity * i.UnitPrice)
})
.ToListAsync(ct);
var hasNext = items.Count > pageSize;
if (hasNext)
{
items.RemoveAt(items.Count - 1);
}
return new PagedResult<OrderSummary>
{
Items = items,
HasNextPage = hasNext,
NextCursor = hasNext ? items[^1].Id : null
};
}
```text
### Offset Pagination (Simple Cases)
For admin UIs or small datasets where exact page numbers matter:
```csharp
var page = await db.Orders
.AsNoTracking()
.OrderBy(o => o.CreatedAt)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToListAsync(ct);
```text
**Warning:** Offset pagination degrades at scale -- `OFFSET 10000` forces the database to scan and discard 10,000 rows.
Prefer keyset pagination for user-facing APIs.
### Row Limit Enforcement
Set a hard upper bound on all queries to prevent accidental full-table scans:
```csharp
// Interceptor approach: enforce max rows at the DbContext level
public sealed class RowLimitInterceptor : IQueryExpressionInterceptor
{
private const int MaxRows = 1000;
public Expression QueryCompilationStarting(
Expression queryExpression,
QueryExpressionEventData eventData)
{
// This is a simplified illustration -- actual implementation requires
// expression tree analysis to detect existing Take() calls.
// Consider using a code review rule or analyzer instead.
return queryExpression;
}
}
```text
**Practical approach:** Rather than a runtime interceptor, enforce row limits through:
1. **Code review convention** -- every `ToListAsync()` must have `Take(N)` or be a `Select()` projection with `Take(N)`.
2. **API-level page size caps** -- validate `pageSize` in the request pipeline before it reaches the query.
3. **Query tags** -- annotate queries with `TagWith()` to identify unbounded queries in monitoring.
---
## Projection Patterns
Projections (`Select()`) are the most effective optimization for read queries. They reduce data transfer, skip change
tracking, and eliminate N+1 risks.
### Typed Projections
```csharp
public sealed record OrderSummary
{
public int Id { get; init; }
public string CustomerName { get; init; } = default!;
public int ItemCount { get; init; }
public decimal Total { get; init; }
public DateTimeOffset CreatedAt { get; init; }
}
var summaries = await db.Orders
.Select(o => new OrderSummary
{
Id = o.Id,
CustomerName = o.Customer.Name,
ItemCount = o.Items.Count,
Total = o.Items.Sum(i => i.Quantity * i.UnitPrice),
CreatedAt = o.CreatedAt
})
.OrderByDescending(o => o.CreatedAt)
.Take(50)
.ToListAsync(ct);
```text
### Advantages Over Entity Loading
| Concern | Entity + Include | Projection (Select) |
| ------------------- | ------------------------- | --------------------- |
| Change tracking | Yes (unless AsNoTracking) | No |
| Data transferred | All columns | Only selected columns |
| N+1 risk | Yes (lazy nav props) | No (computed in SQL) |
| Cartesian explosion | Yes (multiple Includes) | No (single query) |
| Type safety | Entity types | DTO/record types |
**Rule:** Use projections for all read-only endpoints that return DTOs. Reserve entity loading for commands that modify
data.
---
## Key Principles
- **Separate read and write paths** when you have different optimization needs -- do not force a single model to serve
both
- **Design aggregates around consistency boundaries** -- not around database tables
- **Reference other aggregates by ID** -- navigation properties between aggregate roots create coupling
- **Ban lazy loading** -- make all data access explicit through `Include()` or `Select()`
- **Enforce row limits** -- every query that returns a list must have an upper bound
- **Project early** -- use `Select()` to push computation to the database and reduce data transfer
- **Prefer keyset pagination** over offset pagination for scalability
---
## Agent Gotchas
1. **Do not create navigation properties between aggregate roots** -- use foreign key values (e.g., `CustomerId`)
instead of navigation properties (e.g., `Customer`). Cross-aggregate navigation properties break the consistency
boundary and encourage loading data that belongs to another aggregate.
2. **Do not create generic repositories** (`IRepository<T>`) -- they cannot express aggregate-specific loading rules and
become leaky abstractions. Create one repository interface per aggregate root with explicit methods.
3. **Do not use `UseLazyLoadingProxies()`** -- lazy loading hides N+1 queries and makes performance unpredictable. Use
`Include()` for eager loading or `Select()` for projections.
4. **Do not return `IQueryable<T>` from repositories** -- it leaks persistence concerns to callers and makes query
behavior unpredictable (e.g., multiple enumeration, client-side evaluation). Return materialized results (`List<T>`,
`T?`).
5. **Do not write `ToListAsync()` without `Take()` on unbounded queries** -- full table scans are a production incident
waiting to happen. Always limit the result set.
6. **Do not put audit logs or event streams inside aggregates** -- unbounded collections cause slow loads and lock
contention. Model them as separate entities or dedicated stores.
---
## References
- [EF Core performance best practices](https://learn.microsoft.com/en-us/ef/core/performance/)
- [EF Core loading related data](https://learn.microsoft.com/en-us/ef/core/querying/related-data/)
- [EF Core global query filters](https://learn.microsoft.com/en-us/ef/core/querying/filters)
- [Domain-driven design with EF Core](https://learn.microsoft.com/en-us/dotnet/architecture/microservices/microservice-ddd-cqrs-patterns/)
- [EF Core query tags](https://learn.microsoft.com/en-us/ef/core/querying/tags)
- [Keyset pagination in EF Core](https://learn.microsoft.com/en-us/ef/core/querying/pagination#keyset-pagination)
Code Navigation (Serena MCP)
Primary approach: Use Serena symbol operations for efficient code navigation:
- Find definitions:
serena_find_symbolinstead of text search - Understand structure:
serena_get_symbols_overviewfor file organization - Track references:
serena_find_referencing_symbolsfor impact analysis - Precise edits:
serena_replace_symbol_bodyfor clean modifications
When to use Serena vs traditional tools:
- Use Serena: Navigation, refactoring, dependency analysis, precise edits
- Use Read/Grep: Reading full files, pattern matching, simple text operations
- Fallback: If Serena unavailable, traditional tools work fine
Example workflow:
# Instead of:
Read: src/Services/OrderService.cs
Grep: "public void ProcessOrder"
# Use:
serena_find_symbol: "OrderService/ProcessOrder"
serena_get_symbols_overview: "src/Services/OrderService.cs"