Data Access & Architecture Patterns

Data Access & Architecture Patterns in ASP.NET

Master data access strategies and architectural patterns with free flashcards and spaced repetition practice. This lesson covers Entity Framework Core, the Repository Pattern, Unit of Work, CQRS, and dependency injection—essential concepts for building scalable, maintainable ASP.NET applications with .NET 10.

Welcome 💻

Welcome to one of the most crucial aspects of ASP.NET development! Data access and architecture patterns are the foundation of professional web applications. Whether you're building a simple API or a complex enterprise system, understanding how to structure your data layer and apply proven architectural patterns will dramatically improve your code's maintainability, testability, and scalability.

In this lesson, we'll explore Entity Framework Core (EF Core), the modern ORM for .NET, and dive into architectural patterns like Repository, Unit of Work, and CQRS (Command Query Responsibility Segregation). You'll learn not just what these patterns are, but when and why to use them in real-world scenarios.

Core Concepts 🎯

Entity Framework Core: The Foundation

Entity Framework Core is Microsoft's object-relational mapper (ORM) that eliminates much of the data-access code you'd typically need to write. It allows you to work with databases using .NET objects, and it supports LINQ queries, change tracking, and migrations.

Key Components:

Connection Between Layers:

┌─────────────────────────────────────────────┐
│           ASP.NET APPLICATION               │
├─────────────────────────────────────────────┤
│                                             │
│  📱 Controllers/Endpoints                   │
│           ↕                                 │
│  📊 Business Logic Layer                    │
│           ↕                                 │
│  💾 Data Access Layer (DbContext)          │
│           ↕                                 │
│  🗄️  Database (SQL Server/PostgreSQL)     │
│                                             │
└─────────────────────────────────────────────┘

💡 Tip: Think of DbContext as a "gateway" to your database. It tracks changes to your entities and coordinates persisting those changes back to the database.

The Repository Pattern 📚

The Repository Pattern abstracts data access logic, providing a collection-like interface for accessing domain objects. It decouples business logic from data access implementation.

Why Use Repositories?

1. Abstraction - Business logic doesn't know about EF Core
2. Testability - Easy to mock for unit tests
3. Centralization - Common queries in one place
4. Flexibility - Swap data sources without changing business logic

Generic vs. Specific Repositories:

Unit of Work Pattern 🔄

The Unit of Work Pattern maintains a list of objects affected by a business transaction and coordinates writing changes to the database. It ensures that all repository operations within a transaction succeed or fail together.

Key Responsibilities:

• Track changes across multiple repositories
• Coordinate saving changes in a single transaction
• Implement rollback on failure

UNIT OF WORK TRANSACTION FLOW

  ┌──────────────────────────────────────┐
  │  Begin Transaction                   │
  └────────────┬─────────────────────────┘
               │
               ↓
  ┌──────────────────────────────────────┐
  │  Repository Operations               │
  │  • AddProduct()                      │
  │  • UpdateInventory()                 │
  │  • AddOrderHistory()                 │
  └────────────┬─────────────────────────┘
               │
        ┌──────┴──────┐
        ↓             ↓
    ✅ Success    ❌ Failure
        │             │
        ↓             ↓
    💾 Commit    🔙 Rollback

🧠 Memory Device - "UOW": Unify Operations, Write once!

CQRS: Command Query Responsibility Segregation ⚡

CQRS separates read and write operations into different models. Commands change state, Queries return data—never both.

Core Principle:

• Commands - Create, Update, Delete (change state)
• Queries - Read operations (no side effects)

Architecture:

┌─────────────────────────────────────────────┐
│              CLIENT REQUEST                 │
└─────────────┬───────────────────────────────┘
              │
      ┌───────┴────────┐
      │                │
      ↓                ↓
┌──────────┐     ┌──────────┐
│ COMMAND  │     │  QUERY   │
│  SIDE    │     │   SIDE   │
├──────────┤     ├──────────┤
│ Write DB │     │ Read DB  │
│ (Complex)│     │ (Simple) │
│          │     │          │
│ • Validation    │ • DTOs   │
│ • Business      │ • Views  │
│   Rules  │     │ • Cache  │
└──────────┘     └──────────┘
       │                │
       ↓                ↓
   🗄️ Database(s)  📊 Read Models

Benefits:

• Performance - Optimize reads and writes separately
• Scalability - Scale read and write sides independently
• Simplicity - Simpler query models
• Security - Different validation for commands vs. queries

🤔 Did you know? CQRS is often paired with Event Sourcing, where state changes are stored as a sequence of events rather than just the current state.

Dependency Injection in Data Access 💉

Dependency Injection (DI) is crucial for proper architecture. ASP.NET Core has built-in DI that makes registering and resolving dependencies straightforward.

Service Lifetimes:

⚠️ Critical Rule: Never inject a Scoped service (like DbContext) into a Singleton! This causes memory leaks and data corruption.

Design Pattern Comparison 🎨

Examples 💡

Example 1: Basic DbContext Setup

Let's start with the foundation—creating a DbContext and using it directly in a controller:

// Entity class
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int Stock { get; set; }
}

// DbContext
public class StoreDbContext : DbContext
{
    public StoreDbContext(DbContextOptions<StoreDbContext> options) 
        : base(options)
    {
    }

    public DbSet<Product> Products { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .Property(p => p.Price)
            .HasPrecision(18, 2);
    }
}

// Registration in Program.cs
builder.Services.AddDbContext<StoreDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// Controller usage
public class ProductsController : ControllerBase
{
    private readonly StoreDbContext _context;

    public ProductsController(StoreDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<Product>>> GetProducts()
    {
        return await _context.Products
            .Where(p => p.Stock > 0)
            .ToListAsync();
    }
}

Explanation: This is the simplest approach—injecting DbContext directly into controllers. It works well for small applications but tightly couples your controllers to EF Core. The AddDbContext with Scoped lifetime ensures each HTTP request gets its own DbContext instance.

Example 2: Implementing Repository Pattern

Now let's add abstraction with the Repository Pattern:

// Repository interface
public interface IProductRepository
{
    Task<IEnumerable<Product>> GetAllAsync();
    Task<Product> GetByIdAsync(int id);
    Task<IEnumerable<Product>> GetInStockAsync();
    Task AddAsync(Product product);
    Task UpdateAsync(Product product);
    Task DeleteAsync(int id);
}

// Repository implementation
public class ProductRepository : IProductRepository
{
    private readonly StoreDbContext _context;

    public ProductRepository(StoreDbContext context)
    {
        _context = context;
    }

    public async Task<IEnumerable<Product>> GetAllAsync()
    {
        return await _context.Products.ToListAsync();
    }

    public async Task<Product> GetByIdAsync(int id)
    {
        return await _context.Products.FindAsync(id);
    }

    public async Task<IEnumerable<Product>> GetInStockAsync()
    {
        return await _context.Products
            .Where(p => p.Stock > 0)
            .OrderBy(p => p.Name)
            .ToListAsync();
    }

    public async Task AddAsync(Product product)
    {
        await _context.Products.AddAsync(product);
        await _context.SaveChangesAsync();
    }

    public async Task UpdateAsync(Product product)
    {
        _context.Products.Update(product);
        await _context.SaveChangesAsync();
    }

    public async Task DeleteAsync(int id)
    {
        var product = await _context.Products.FindAsync(id);
        if (product != null)
        {
            _context.Products.Remove(product);
            await _context.SaveChangesAsync();
        }
    }
}

// Registration in Program.cs
builder.Services.AddScoped<IProductRepository, ProductRepository>();

// Controller now depends on interface
public class ProductsController : ControllerBase
{
    private readonly IProductRepository _repository;

    public ProductsController(IProductRepository repository)
    {
        _repository = repository;
    }

    [HttpGet("in-stock")]
    public async Task<ActionResult<IEnumerable<Product>>> GetInStock()
    {
        var products = await _repository.GetInStockAsync();
        return Ok(products);
    }
}

Explanation: The Repository Pattern decouples your business logic from EF Core. Notice how GetInStockAsync() encapsulates the query logic—controllers don't need to know about LINQ or EF Core. This also makes testing easier: you can mock IProductRepository without needing a real database.

💡 Tip: Notice that each repository method calls SaveChangesAsync() immediately. This works for simple scenarios, but for complex operations involving multiple repositories, you'll want the Unit of Work pattern.

Example 3: Unit of Work Pattern

Let's coordinate multiple repository operations:

// Generic repository interface
public interface IRepository<T> where T : class
{
    Task<T> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();
    Task AddAsync(T entity);
    void Update(T entity);
    void Delete(T entity);
}

// Unit of Work interface
public interface IUnitOfWork : IDisposable
{
    IRepository<Product> Products { get; }
    IRepository<Order> Orders { get; }
    IRepository<Customer> Customers { get; }
    Task<int> SaveChangesAsync();
}

// Unit of Work implementation
public class UnitOfWork : IUnitOfWork
{
    private readonly StoreDbContext _context;
    private IRepository<Product> _products;
    private IRepository<Order> _orders;
    private IRepository<Customer> _customers;

    public UnitOfWork(StoreDbContext context)
    {
        _context = context;
    }

    public IRepository<Product> Products => 
        _products ??= new Repository<Product>(_context);

    public IRepository<Order> Orders => 
        _orders ??= new Repository<Order>(_context);

    public IRepository<Customer> Customers => 
        _customers ??= new Repository<Customer>(_context);

    public async Task<int> SaveChangesAsync()
    {
        return await _context.SaveChangesAsync();
    }

    public void Dispose()
    {
        _context.Dispose();
    }
}

// Service using Unit of Work
public class OrderService
{
    private readonly IUnitOfWork _unitOfWork;

    public OrderService(IUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
    }

    public async Task<Order> CreateOrderAsync(int customerId, List<int> productIds)
    {
        var customer = await _unitOfWork.Customers.GetByIdAsync(customerId);
        if (customer == null)
            throw new Exception("Customer not found");

        var order = new Order
        {
            CustomerId = customerId,
            OrderDate = DateTime.UtcNow
        };

        await _unitOfWork.Orders.AddAsync(order);

        foreach (var productId in productIds)
        {
            var product = await _unitOfWork.Products.GetByIdAsync(productId);
            if (product != null && product.Stock > 0)
            {
                product.Stock--;
                _unitOfWork.Products.Update(product);
            }
        }

        // Single transaction for all changes
        await _unitOfWork.SaveChangesAsync();
        return order;
    }
}

Explanation: The Unit of Work coordinates multiple repositories and ensures all changes are saved in a single transaction. If updating inventory fails, the order won't be created either. This maintains data consistency across related entities.

Example 4: CQRS with MediatR

Let's implement a simple CQRS pattern using the popular MediatR library:

// Command to create a product
public record CreateProductCommand(string Name, decimal Price, int Stock) 
    : IRequest<int>;

// Command handler
public class CreateProductCommandHandler 
    : IRequestHandler<CreateProductCommand, int>
{
    private readonly StoreDbContext _context;

    public CreateProductCommandHandler(StoreDbContext context)
    {
        _context = context;
    }

    public async Task<int> Handle(
        CreateProductCommand request, 
        CancellationToken cancellationToken)
    {
        var product = new Product
        {
            Name = request.Name,
            Price = request.Price,
            Stock = request.Stock
        };

        _context.Products.Add(product);
        await _context.SaveChangesAsync(cancellationToken);
        return product.Id;
    }
}

// Query to get products
public record GetProductsQuery(bool InStockOnly) 
    : IRequest<List<ProductDto>>;

// Query handler
public class GetProductsQueryHandler 
    : IRequestHandler<GetProductsQuery, List<ProductDto>>
{
    private readonly StoreDbContext _context;

    public GetProductsQueryHandler(StoreDbContext context)
    {
        _context = context;
    }

    public async Task<List<ProductDto>> Handle(
        GetProductsQuery request, 
        CancellationToken cancellationToken)
    {
        var query = _context.Products.AsQueryable();

        if (request.InStockOnly)
            query = query.Where(p => p.Stock > 0);

        return await query
            .Select(p => new ProductDto
            {
                Id = p.Id,
                Name = p.Name,
                Price = p.Price
            })
            .AsNoTracking()
            .ToListAsync(cancellationToken);
    }
}

// Controller using CQRS
public class ProductsController : ControllerBase
{
    private readonly IMediator _mediator;

    public ProductsController(IMediator mediator)
    {
        _mediator = mediator;
    }

    [HttpPost]
    public async Task<ActionResult<int>> CreateProduct(
        CreateProductCommand command)
    {
        var id = await _mediator.Send(command);
        return CreatedAtAction(nameof(GetProducts), new { id }, id);
    }

    [HttpGet]
    public async Task<ActionResult<List<ProductDto>>> GetProducts(
        [FromQuery] bool inStockOnly = false)
    {
        var query = new GetProductsQuery(inStockOnly);
        var products = await _mediator.Send(query);
        return Ok(products);
    }
}

Explanation: CQRS separates commands (write operations) from queries (read operations). Commands use full entity models with validation and business rules. Queries use optimized read models (DTOs) with AsNoTracking() for better performance. The mediator pattern (MediatR) decouples controllers from handlers.

🔧 Try this: Add a caching layer to your query handlers to dramatically improve read performance for frequently accessed data.

Common Mistakes ⚠️

1. Not Using AsNoTracking for Read-Only Queries

// ❌ WRONG - Unnecessary change tracking overhead
public async Task<List<Product>> GetProductsForDisplay()
{
    return await _context.Products.ToListAsync();
}

// ✅ RIGHT - No tracking for read-only data
public async Task<List<Product>> GetProductsForDisplay()
{
    return await _context.Products
        .AsNoTracking()
        .ToListAsync();
}

Why it matters: AsNoTracking() tells EF Core not to track these entities for changes, reducing memory usage and improving performance by 30-50% for read operations.

2. Repository Over-Abstraction

// ❌ WRONG - Repository that just wraps DbContext
public interface IProductRepository
{
    IQueryable<Product> GetQueryable();
}

public class ProductRepository : IProductRepository
{
    public IQueryable<Product> GetQueryable() => _context.Products;
}

// ✅ RIGHT - Meaningful domain-specific methods
public interface IProductRepository
{
    Task<List<Product>> GetTopSellingProductsAsync(int count);
    Task<List<Product>> GetLowStockProductsAsync(int threshold);
    Task<Product> GetProductWithReviewsAsync(int id);
}

Why it matters: Don't create repositories just for the sake of abstraction. If you're exposing IQueryable, you're leaking EF Core into your business layer. Repositories should encapsulate meaningful domain queries.

3. Captive Dependencies (Scoped in Singleton)

// ❌ WRONG - DbContext captured in Singleton
builder.Services.AddSingleton<ProductCache>();

public class ProductCache
{
    private readonly StoreDbContext _context; // Scoped service!
    
    public ProductCache(StoreDbContext context)
    {
        _context = context; // Memory leak!
    }
}

// ✅ RIGHT - Use IServiceScopeFactory
builder.Services.AddSingleton<ProductCache>();

public class ProductCache
{
    private readonly IServiceScopeFactory _scopeFactory;
    
    public ProductCache(IServiceScopeFactory scopeFactory)
    {
        _scopeFactory = scopeFactory;
    }
    
    public async Task RefreshCache()
    {
        using var scope = _scopeFactory.CreateScope();
        var context = scope.ServiceProvider.GetRequiredService<StoreDbContext>();
        // Use context within scope
    }
}

Why it matters: Injecting a Scoped service into a Singleton causes the DbContext to live for the entire application lifetime, leading to memory leaks and stale data.

4. Not Using Transactions for Multi-Step Operations

// ❌ WRONG - Multiple SaveChanges without transaction
public async Task TransferProductAsync(int productId, int fromWarehouse, int toWarehouse)
{
    var product = await _context.Products.FindAsync(productId);
    
    var fromInv = await _context.Inventory
        .FirstAsync(i => i.WarehouseId == fromWarehouse);
    fromInv.Quantity--;
    await _context.SaveChangesAsync(); // Could fail here!
    
    var toInv = await _context.Inventory
        .FirstAsync(i => i.WarehouseId == toWarehouse);
    toInv.Quantity++;
    await _context.SaveChangesAsync(); // Inconsistent state if first succeeded!
}

// ✅ RIGHT - Use transaction
public async Task TransferProductAsync(int productId, int fromWarehouse, int toWarehouse)
{
    using var transaction = await _context.Database.BeginTransactionAsync();
    try
    {
        var fromInv = await _context.Inventory
            .FirstAsync(i => i.WarehouseId == fromWarehouse);
        fromInv.Quantity--;
        
        var toInv = await _context.Inventory
            .FirstAsync(i => i.WarehouseId == toWarehouse);
        toInv.Quantity++;
        
        await _context.SaveChangesAsync();
        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

Why it matters: Without transactions, partial failures leave your database in an inconsistent state. Either all operations succeed, or none do.

5. Using Repository Pattern with CQRS (Redundant)

// ❌ WRONG - Repository + CQRS is redundant
public class GetProductsQueryHandler
{
    private readonly IProductRepository _repository;
    
    public async Task<List<Product>> Handle(GetProductsQuery query)
    {
        return await _repository.GetAllAsync(); // Extra layer!
    }
}

// ✅ RIGHT - CQRS queries access DbContext directly
public class GetProductsQueryHandler
{
    private readonly StoreDbContext _context;
    
    public async Task<List<Product>> Handle(GetProductsQuery query)
    {
        return await _context.Products
            .AsNoTracking()
            .ToListAsync();
    }
}

Why it matters: CQRS command/query handlers already provide abstraction. Adding repositories on top creates unnecessary layers. In CQRS, commands might use repositories for write operations, but queries typically access the DbContext directly for flexibility.

Key Takeaways 🎯

✅ Entity Framework Core provides a powerful ORM, but understand when to use AsNoTracking(), projections, and raw SQL for performance

✅ Repository Pattern adds abstraction and testability—use it for complex domains, but avoid over-abstraction that just wraps DbContext

✅ Unit of Work coordinates multiple repositories in transactions—essential for maintaining consistency across related entities

✅ CQRS separates read and write concerns—use it for complex applications where read and write models differ significantly

✅ Dependency Injection is critical—always use Scoped lifetime for DbContext and repositories, never capture them in Singletons

✅ Choose patterns based on complexity—start simple with direct DbContext usage, add patterns as complexity grows

✅ Transactions matter—use them for multi-step operations that must succeed or fail together

📚 Further Study

• Entity Framework Core Documentation - Official Microsoft docs with tutorials and best practices
• MediatR for CQRS in .NET - Popular library for implementing CQRS and mediator patterns
• Repository Pattern Discussion by Jimmy Bogard - Thoughtful analysis of when to use (and not use) repositories