Repository Pattern: Abstract Data Access & Separation of Concerns

Problem

Modern applications often tightly couple business logic with data access code, creating several issues:

• Tight Coupling: Business logic directly depends on specific database implementations
• Testing Difficulty: Hard to unit test without actual database connections
• Code Duplication: Data access logic scattered across multiple services
• Maintenance Burden: Changing database technology requires refactoring throughout the codebase
• Scalability Issues: Difficult to implement caching, query optimization, or switching data sources
• Violation of SOLID: Breaks Single Responsibility and Dependency Inversion principles

// ❌ PROBLEMATIC: Tightly coupled data access
public class UserService
{
    private SqlConnection _connection;

    public User GetUser(int id)
    {
        using (var cmd = new SqlCommand("SELECT * FROM Users WHERE Id = @id", _connection))
        {
            cmd.Parameters.AddWithValue("@id", id);
            var reader = cmd.ExecuteReader();
            // Manual mapping, SQL scattered everywhere
        }
    }
}

Solution

The Repository Pattern abstracts data access behind a clean interface, providing:

• Abstraction Layer: Business logic interacts with repositories, not databases
• Testability: Easy to mock repositories for unit testing
• Flexibility: Swap implementations without changing business logic
• Reusability: Common data access patterns centralized
• Separation of Concerns: Each layer has a single responsibility
• SOLID Compliance: Depends on abstractions, not concrete implementations

Core Principles

1. Interface-Based Design: Define contracts for data operations
2. Generic Repositories: Reusable base functionality for common CRUD operations
3. Specific Repositories: Domain-specific queries and operations
4. Dependency Injection: Inject repositories into services
5. Unit of Work Pattern: Manage transactions across multiple repositories

Code

1. Define Generic Repository Interface

public interface IRepository<T> where T : class
{
    Task<T> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();
    Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate);
    Task AddAsync(T entity);
    Task UpdateAsync(T entity);
    Task DeleteAsync(T entity);
    Task SaveChangesAsync();
}

2. Generic Repository Implementation

public class Repository<T> : IRepository<T> where T : class
{
    protected readonly DbContext _context;
    protected readonly DbSet<T> _dbSet;

    public Repository(DbContext context)
    {
        _context = context;
        _dbSet = context.Set<T>();
    }

    public async Task<T> GetByIdAsync(int id)
    {
        return await _dbSet.FindAsync(id);
    }

    public async Task<IEnumerable<T>> GetAllAsync()
    {
        return await _dbSet.ToListAsync();
    }

    public async Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate)
    {
        return await _dbSet.Where(predicate).ToListAsync();
    }

    public async Task AddAsync(T entity)
    {
        await _dbSet.AddAsync(entity);
    }

    public async Task UpdateAsync(T entity)
    {
        _dbSet.Update(entity);
    }

    public async Task DeleteAsync(T entity)
    {
        _dbSet.Remove(entity);
    }

    public async Task SaveChangesAsync()
    {
        await _context.SaveChangesAsync();
    }
}

3. Domain-Specific Repository Interface

public interface IUserRepository : IRepository<User>
{
    Task<User> GetByEmailAsync(string email);
    Task<IEnumerable<User>> GetActiveUsersAsync();
    Task<bool> EmailExistsAsync(string email);
}

4. Domain-Specific Repository Implementation

public class UserRepository : Repository<User>, IUserRepository
{
    public UserRepository(DbContext context) : base(context) { }

    public async Task<User> GetByEmailAsync(string email)
    {
        return await _dbSet
            .FirstOrDefaultAsync(u => u.Email == email);
    }

    public async Task<IEnumerable<User>> GetActiveUsersAsync()
    {
        return await _dbSet
            .Where(u => u.IsActive)
            .OrderBy(u => u.CreatedDate)
            .ToListAsync();
    }

    public async Task<bool> EmailExistsAsync(string email)
    {
        return await _dbSet.AnyAsync(u => u.Email == email);
    }
}

5. Unit of Work Pattern

public interface IUnitOfWork : IDisposable
{
    IUserRepository Users { get; }
    IOrderRepository Orders { get; }
    IProductRepository Products { get; }
    Task<int> SaveChangesAsync();
}

public class UnitOfWork : IUnitOfWork
{
    private readonly DbContext _context;
    private IUserRepository _userRepository;
    private IOrderRepository _orderRepository;
    private IProductRepository _productRepository;

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

    public IUserRepository Users =>
        _userRepository ??= new UserRepository(_context);

    public IOrderRepository Orders =>
        _orderRepository ??= new OrderRepository(_context);

    public IProductRepository Products =>
        _productRepository ??= new ProductRepository(_context);

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

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

6. Business Logic Service

public interface IUserService
{
    Task<UserDto> RegisterUserAsync(RegisterRequest request);
    Task<UserDto> GetUserAsync(int id);
    Task<IEnumerable<UserDto>> GetActiveUsersAsync();
}

public class UserService : IUserService
{
    private readonly IUnitOfWork _unitOfWork;
    private readonly IMapper _mapper;

    public UserService(IUnitOfWork unitOfWork, IMapper mapper)
    {
        _unitOfWork = unitOfWork;
        _mapper = mapper;
    }

    public async Task<UserDto> RegisterUserAsync(RegisterRequest request)
    {
        // Check if email exists
        if (await _unitOfWork.Users.EmailExistsAsync(request.Email))
            throw new InvalidOperationException("Email already registered");

        var user = new User
        {
            Email = request.Email,
            Name = request.Name,
            PasswordHash = HashPassword(request.Password),
            IsActive = true,
            CreatedDate = DateTime.UtcNow
        };

        await _unitOfWork.Users.AddAsync(user);
        await _unitOfWork.SaveChangesAsync();

        return _mapper.Map<UserDto>(user);
    }

    public async Task<UserDto> GetUserAsync(int id)
    {
        var user = await _unitOfWork.Users.GetByIdAsync(id);
        if (user == null)
            throw new KeyNotFoundException($"User {id} not found");

        return _mapper.Map<UserDto>(user);
    }

    public async Task<IEnumerable<UserDto>> GetActiveUsersAsync()
    {
        var users = await _unitOfWork.Users.GetActiveUsersAsync();
        return _mapper.Map<IEnumerable<UserDto>>(users);
    }

    private string HashPassword(string password)
    {
        return BCrypt.Net.BCrypt.HashPassword(password);
    }
}

7. Dependency Injection Setup

public static class ServiceCollectionExtensions
{
    public static IServiceCollection AddRepositories(
        this IServiceCollection services)
    {
        services.AddScoped<DbContext, ApplicationDbContext>();
        services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
        services.AddScoped<IUserRepository, UserRepository>();
        services.AddScoped<IOrderRepository, OrderRepository>();
        services.AddScoped<IProductRepository, ProductRepository>();
        services.AddScoped<IUnitOfWork, UnitOfWork>();
        services.AddScoped<IUserService, UserService>();

        return services;
    }
}

// In Startup.cs or Program.cs
public void ConfigureServices(IServiceCollection services)
{
    services.AddRepositories();
    services.AddControllers();
}

8. Controller Usage

[ApiController]
[Route("api/[controller]")]
public class UsersController : ControllerBase
{
    private readonly IUserService _userService;

    public UsersController(IUserService userService)
    {
        _userService = userService;
    }

    [HttpPost("register")]
    public async Task<ActionResult<UserDto>> Register(RegisterRequest request)
    {
        var user = await _userService.RegisterUserAsync(request);
        return CreatedAtAction(nameof(GetUser), new { id = user.Id }, user);
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<UserDto>> GetUser(int id)
    {
        var user = await _userService.GetUserAsync(id);
        return Ok(user);
    }

    [HttpGet("active")]
    public async Task<ActionResult<IEnumerable<UserDto>>> GetActiveUsers()
    {
        var users = await _userService.GetActiveUsersAsync();
        return Ok(users);
    }
}

9. Unit Testing Example

public class UserServiceTests
{
    private readonly Mock<IUnitOfWork> _mockUnitOfWork;
    private readonly Mock<IMapper> _mockMapper;
    private readonly UserService _userService;

    public UserServiceTests()
    {
        _mockUnitOfWork = new Mock<IUnitOfWork>();
        _mockMapper = new Mock<IMapper>();
        _userService = new UserService(_mockUnitOfWork.Object, _mockMapper.Object);
    }

    [Fact]
    public async Task RegisterUserAsync_WithValidData_ReturnsUserDto()
    {
        // Arrange
        var request = new RegisterRequest 
        { 
            Email = "test@example.com", 
            Name = "Test User",
            Password = "SecurePass123"
        };

        _mockUnitOfWork.Setup(u => u.Users.EmailExistsAsync(request.Email))
            .ReturnsAsync(false);

        var userDto = new UserDto { Id = 1, Email = request.Email, Name = request.Name };
        _mockMapper.Setup(m => m.Map<UserDto>(It.IsAny<User>()))
            .Returns(userDto);

        // Act
        var result = await _userService.RegisterUserAsync(request);

        // Assert
        Assert.NotNull(result);
        Assert.Equal(request.Email, result.Email);
        _mockUnitOfWork.Verify(u => u.SaveChangesAsync(), Times.Once);
    }

    [Fact]
    public async Task RegisterUserAsync_WithExistingEmail_ThrowsException()
    {
        // Arrange
        var request = new RegisterRequest { Email = "existing@example.com" };
        _mockUnitOfWork.Setup(u => u.Users.EmailExistsAsync(request.Email))
            .ReturnsAsync(true);

        // Act & Assert
        await Assert.ThrowsAsync<InvalidOperationException>(
            () => _userService.RegisterUserAsync(request));
    }
}

Tips

✅ Best Practices

1. Use Generic Repositories for CRUD: Avoid code duplication for basic operations
2. Create Specific Repositories for Complex Queries: Domain-specific repositories handle business logic queries
3. Implement Unit of Work: Manage transactions across multiple repositories consistently
4. Depend on Abstractions: Always inject interfaces, not concrete implementations
5. Keep Repositories Thin: Move business logic to services, not repositories
6. Use Async/Await: Leverage async operations for better scalability
7. Implement Pagination: Handle large datasets efficiently with skip/take patterns
8. Cache Strategically: Add caching layer without changing repository interface

⚠️ Common Pitfalls

• Over-Engineering: Don't create repositories for simple CRUD operations
• Leaky Abstractions: Avoid exposing ORM-specific types (IQueryable) in repository interfaces
• Business Logic in Repositories: Keep repositories focused on data access only
• Ignoring Performance: Monitor N+1 queries and implement eager loading
• Tight Coupling to ORM: Use abstractions that work across different data access technologies

🔧 Advanced Patterns

// Specification Pattern for complex queries
public interface ISpecification<T>
{
    Expression<Func<T, bool>> Criteria { get; }
    List<Expression<Func<T, object>>> Includes { get; }
}

// Repository with Specification support
public async Task<IEnumerable<T>> GetAsync(ISpecification<T> spec)
{
    return await ApplySpecification(spec).ToListAsync();
}

The Repository Pattern is essential for building maintainable, testable applications with clear separation of concerns.