dotnet-efcore-architecture

name	dotnet-efcore-architecture
description	Designs EF Core data layer architecture. Read/write split, aggregate boundaries, N+1 governance.

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



| Scenario                            | Recommendation                                      |
| ----------------------------------- | --------------------------------------------------- |
| Simple CRUD app                     | Single `DbContext`  per-query `AsNoTracking()`  |
| Read-heavy API  complex queries | Separate read/write contexts                        |
| Read replica database               | Separate contexts  different connection strings |
| CQRS architecture                   | Separate contexts, possibly separate models         |

**Start simple.** Use a single `DbContext`  per-query `AsNoTracking()` 
{
      Id { ;  ; }
      CustomerId { ;  ; } = !;
     OrderStatus Status { ;  ; }
     DateTimeOffset CreatedAt { ;  ; }

    
      List<OrderItem> _items = [];
     IReadOnlyList<OrderItem> Items => _items.AsReadOnly();

    
    {
         (Status != OrderStatus.Draft)
              InvalidOperationException();

        _items.Add( OrderItem(productId, quantity, unitPrice));
    }
}


   
{
      Id { ;  ; }
      ProductId { ;  ; }
      Quantity { ;  ; }
      UnitPrice { ;  ; }

    
    {
        ProductId = productId;
        Quantity = quantity;
        UnitPrice = unitPrice;
    }

     { } 
}

```text



```csharp

    : <>
{
    
    {
        builder.HasKey(o => o.Id);

        builder.Property(o => o.CustomerId).IsRequired().HasMaxLength();
        builder.Property(o => o.Status).HasConversion<>();

        
        builder.OwnsMany(o => o.Items, items =>
        {
            items.WithOwner().HasForeignKey();
            items.Property(i => i.ProductId).IsRequired();
        });

        
        
        
        
        
        
        
        
    }
}

```text



 **Load the entire aggregate** --   load  aggregates. Use `Include()`  the owned collections.
 **Save through the aggregate root** -- call `SaveChangesAsync()`  the root,   child entities independently.
 **Reference other aggregates  ID** --   create navigation properties between aggregate roots. Use `CustomerId`
   (foreign key ),  `Customer` (navigation property).
 **Keep aggregates small** -- large aggregates cause  contention  slow loads. 
{
    
    {
         order =  Order(command.CustomerId);

         ( item  command.Items)
        {
            order.AddItem(item.ProductId, item.Quantity, item.UnitPrice);
        }

        db.Orders.Add(order);
         db.SaveChangesAsync(ct);

         order.Id;
    }
}

```text

**Pros:** Simple, no abstraction overhead, full LINQ power, easy to debug. **Cons:** Business logic can leak  query
methods, harder to unit test without a database.



```csharp

  
{
    Task<Order?> GetByIdAsync( id, CancellationToken ct);
    ;
    ;
}


{
      Task<Order?> GetByIdAsync( id, CancellationToken ct)
    {
          db.Orders
            .Include(o => o.Items)
            .FirstOrDefaultAsync(o => o.Id == id, ct);
    }

    
    {
         db.Orders.AddAsync(order, ct);
    }

    
    {
         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  LINQ  exposed, repository per aggregate can proliferate.



| Factor               | Direct DbContext               | Repository                    |
| -------------------- | ------------------------------ | ----------------------------- |
| Team size            | Small, aligned                 | Large, varied experience      |
| Test strategy        | Integration tests  real DB | Unit tests  mocked repos  |
| Query complexity     | High (reports, projections)    | Low-medium (CRUD, aggregates) |
| Aggregate discipline | Enforced  convention         | Enforced           |

**    ** (`<>`).      --   
  -   (   ,    ).  
        .

---

## +1  

+1         .         
    ,      .

### 

        :

```

<>( =>
    options.UseNpgsql(connectionString)
           .LogTo(Console.WriteLine, LogLevel.Information)
           .EnableSensitiveDataLogging()  
           .EnableDetailedErrors());      

```text



**Pattern : Lazy loading  a loop**

```csharp


 orders =  db.Orders.ToListAsync(ct);
 ( order  orders)
{
     total = order.Items.Sum(i => i.Quantity * i.UnitPrice); 
}


 orders =  db.Orders
    .Include(o => o.Items)
    .ToListAsync(ct);

```text

**Pattern : Querying inside a loop**

```csharp


 ( customerId  customerIds)
{
     orders =  db.Orders
        .Where(o => o.CustomerId == customerId)
        .ToListAsync(ct);
    
}


 orders =  db.Orders
    .Where(o => customerIds.Contains(o.CustomerId))
    .ToListAsync(ct);

```text

**Pattern : Missing projection**

```csharp


 orders =  db.Orders
    .Include(o => o.Items)
    .Include(o => o.Customer)
    .ToListAsync(ct);
 dtos = orders.Select(o =>  OrderDto(...));


 dtos =  db.Orders
    .Select(o =>  OrderDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Items.Count,
        Total = o.Items.Sum(i => i.Quantity * i.UnitPrice)
    })
    .ToListAsync(ct);

```text



- **Disable lazy loading** --   install `Microsoft.EntityFrameworkCore.Proxies`  configure
  `UseLazyLoadingProxies()`. Eager loading via `Include()`  projection via `Select()` makes data access .
- **Review queries  code review** -- look  loops that access navigation properties  call `FindAsync` /
  `FirstOrDefaultAsync` per element.
- **Use query tags** -- `db.Orders.TagWith()` makes queries identifiable  logs  profiling tools.
- **Set up EF Core logging  development** -- every lazy load  unexpected query  visible  the console output.

---



Unbounded queries are a production risk. Always limit the number of rows returned.




{
      maxPageSize = ;
    pageSize = Math.Min(pageSize, maxPageSize);

     query = db.Orders
        .AsNoTracking()
        .Where(o => o.CustomerId == customerId);

     (afterId.HasValue)
    {
        query = query.Where(o => o.Id > afterId.Value);
    }

     items =  query
        .OrderBy(o => o.Id)
        .Take(pageSize + )  
        .Select(o =>  OrderSummary
        {
            Id = o.Id,
            Status = o.Status,
            CreatedAt = o.CreatedAt,
            Total = o.Items.Sum(i => i.Quantity * i.UnitPrice)
        })
        .ToListAsync(ct);

     hasNext = items.Count > pageSize;
     (hasNext)
    {
        items.RemoveAt(items.Count - );
    }

      PagedResult<OrderSummary>
    {
        Items = items,
        HasNextPage = hasNext,
        NextCursor = hasNext ? items[^].Id : 
    };
}

```text



For admin UIs  small datasets  exact page numbers matter:

```csharp

 page =  db.Orders
    .AsNoTracking()
    .OrderBy(o => o.CreatedAt)
    .Skip((pageNumber - ) * pageSize)
    .Take(pageSize)
    .ToListAsync(ct);

```text

**Warning:** Offset pagination degrades at scale -- `OFFSET ` forces the database to scan  discard , rows.
Prefer keyset pagination  user-facing APIs.



Set a hard upper bound  all queries to prevent accidental full-table scans:

```csharp


    : 
{
       MaxRows = ;

    
    {
        
        
        
         queryExpression;
    }
}

```text

**Practical approach:** Rather than a runtime interceptor, enforce row limits through:

 **Code review convention** -- every `ToListAsync()` must have `Take(N)`  be a `Select()` projection  `Take(N)`.
 **API-level page size caps** -- validate `pageSize`  the request pipeline before it reaches the query.
 **Query tags** -- annotate queries  `TagWith()` to identify unbounded queries  monitoring.

---



Projections (`Select()`) are the most effective optimization  read queries. They reduce data transfer, skip change
tracking,  eliminate N+ risks.



```csharp

   
{
      Id { ; ; }
      CustomerName { ; ; } = !;
      ItemCount { ; ; }
      Total { ; ; }
     DateTimeOffset CreatedAt { ; ; }
}

 summaries =  db.Orders
    .Select(o =>  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()
    .ToListAsync(ct);

```text



| Concern             | Entity + Include          | Projection (Select)   |
| ------------------- | ------------------------- | --------------------- |
| Change tracking     | Yes (unless AsNoTracking) | No                    |
| Data transferred    | All columns               | Only selected columns |
| N+ risk            | Yes (lazy nav props)      | No (computed  SQL)  |
| Cartesian explosion | Yes (multiple Includes)   | No (single query)     |
| Type safety         | Entity types              | DTO/       |

**:**     -    .       
.

---

##  

- **    **       --        
  
- **    ** --    
- **    ** --       
- **  ** --       `()`  `()`
- **  ** --           
- ** ** --  `()`          
- **  **     

---

##  

1. **       ** --     (., ``)
       (., ``). -     
            .
2. **    ** (`<>`) --    -   
     .          .
3. **   `()`** --    +1     . 
   `()`     `()`  .
4. **   `<>`  ** --         
     (.,  , - ).    (`<>`,
   `?`).
5. **   `()`  `()`   ** --       
     .     .
6. **         ** --       
   .        .

---

## 

- [    ](:
- [    ](:
- [    ](:
- [-    ](:
- [   ](:
- [    ](: