数据访问层设计模式

SunnyFan大约 14 分钟约 4267 字

数据访问层设计模式

简介

数据访问层（Data Access Layer, DAL）是应用程序与数据存储之间的桥梁，负责封装所有与数据存储交互的细节。良好的数据访问层设计能够隔离业务逻辑与存储实现、提升代码可测试性、优化查询性能，并为未来的存储技术迁移提供便利。

数据访问层设计模式涵盖了从传统的 Repository + Unit of Work 模式到现代的 CQRS、Specification 等模式，以及延迟加载、批量优化、审计追踪、软删除、多租户等横切关注点的实现策略。

特点

关注点分离：将数据访问逻辑从业务逻辑中隔离
可测试性：通过接口抽象支持 Mock 和单元测试
存储无关性：业务层不依赖具体的数据库类型
查询优化：集中管理查询逻辑，便于性能调优
横切关注点：统一处理审计、软删除、多租户等

实现

Repository 模式

基础 Repository 接口

// 泛型 Repository 接口
public interface IRepository<TEntity, TKey> where TEntity : class
{
    Task<TEntity> GetByIdAsync(TKey id, CancellationToken ct = default);
    Task<IReadOnlyList<TEntity>> GetAllAsync(CancellationToken ct = default);
    Task<IReadOnlyList<TEntity>> FindAsync(ISpecification<TEntity> spec, CancellationToken ct = default);
    Task<TEntity> FirstOrDefaultAsync(ISpecification<TEntity> spec, CancellationToken ct = default);
    Task AddAsync(TEntity entity, CancellationToken ct = default);
    Task AddRangeAsync(IEnumerable<TEntity> entities, CancellationToken ct = default);
    void Update(TEntity entity);
    void Remove(TEntity entity);
    void RemoveRange(IEnumerable<TEntity> entities);
    Task<int> CountAsync(ISpecification<TEntity> spec, CancellationToken ct = default);
    Task<bool> AnyAsync(ISpecification<TEntity> spec, CancellationToken ct = default);
    Task<PagedResult<TEntity>> GetPagedAsync(int page, int pageSize,
        ISpecification<TEntity> spec = null, CancellationToken ct = default);
}

// 分页结果
public record PagedResult<T>
{
    public IReadOnlyList<T> Items { get; init; }
    public int TotalCount { get; init; }
    public int Page { get; init; }
    public int PageSize { get; init; }
    public int TotalPages => (int)Math.Ceiling((double)TotalCount / PageSize);
    public bool HasPreviousPage => Page > 1;
    public bool HasNextPage => Page < TotalPages;
}

EF Core 实现

// Entity Framework Core 实现
public class EfRepository<TEntity, TKey> : IRepository<TEntity, TKey>
    where TEntity : Entity<TKey>
{
    protected readonly DbContext _context;
    protected readonly DbSet<TEntity> _dbSet;

    public EfRepository(DbContext context)
    {
        _context = context;
        _dbSet = context.Set<TEntity>();
    }

    public async Task<TEntity> GetByIdAsync(TKey id, CancellationToken ct = default)
    {
        return await _dbSet.FindAsync(new object[] { id }, ct);
    }

    public async Task<IReadOnlyList<TEntity>> GetAllAsync(CancellationToken ct = default)
    {
        return await _dbSet.AsNoTracking().ToListAsync(ct);
    }

    public async Task<IReadOnlyList<TEntity>> FindAsync(
        ISpecification<TEntity> spec, CancellationToken ct = default)
    {
        return await ApplySpecification(spec).ToListAsync(ct);
    }

    public async Task<TEntity> FirstOrDefaultAsync(
        ISpecification<TEntity> spec, CancellationToken ct = default)
    {
        return await ApplySpecification(spec).FirstOrDefaultAsync(ct);
    }

    public async Task AddAsync(TEntity entity, CancellationToken ct = default)
    {
        await _dbSet.AddAsync(entity, ct);
    }

    public async Task AddRangeAsync(IEnumerable<TEntity> entities, CancellationToken ct = default)
    {
        await _dbSet.AddRangeAsync(entities, ct);
    }

    public void Update(TEntity entity)
    {
        _dbSet.Update(entity);
    }

    public void Remove(TEntity entity)
    {
        _dbSet.Remove(entity);
    }

    public void RemoveRange(IEnumerable<TEntity> entities)
    {
        _dbSet.RemoveRange(entities);
    }

    public async Task<int> CountAsync(ISpecification<TEntity> spec, CancellationToken ct = default)
    {
        return await ApplySpecification(spec).CountAsync(ct);
    }

    public async Task<bool> AnyAsync(ISpecification<TEntity> spec, CancellationToken ct = default)
    {
        return await ApplySpecification(spec).AnyAsync(ct);
    }

    public async Task<PagedResult<TEntity>> GetPagedAsync(
        int page, int pageSize,
        ISpecification<TEntity> spec = null, CancellationToken ct = default)
    {
        var query = spec != null ? ApplySpecification(spec) : _dbSet.AsQueryable();
        var totalCount = await query.CountAsync(ct);
        var items = await query
            .Skip((page - 1) * pageSize)
            .Take(pageSize)
            .ToListAsync(ct);

        return new PagedResult<TEntity>
        {
            Items = items,
            TotalCount = totalCount,
            Page = page,
            PageSize = pageSize
        };
    }

    private IQueryable<TEntity> ApplySpecification(ISpecification<TEntity> spec)
    {
        return SpecificationEvaluator<TEntity>.Apply(_dbSet, spec);
    }
}

Unit of Work 模式

// Unit of Work 接口
public interface IUnitOfWork : IDisposable
{
    IRepository<TEntity, int> GetRepository<TEntity>() where TEntity : class;
    Task<int> SaveChangesAsync(CancellationToken ct = default);
    Task BeginTransactionAsync(CancellationToken ct = default);
    Task CommitTransactionAsync(CancellationToken ct = default);
    Task RollbackTransactionAsync(CancellationToken ct = default);
}

// EF Core Unit of Work 实现
public class EfUnitOfWork : IUnitOfWork
{
    private readonly DbContext _context;
    private readonly Dictionary<Type, object> _repositories;
    private IDbContextTransaction _currentTransaction;

    public EfUnitOfWork(DbContext context)
    {
        _context = context;
        _repositories = new Dictionary<Type, object>();
    }

    public IRepository<TEntity, int> GetRepository<TEntity>() where TEntity : class
    {
        var type = typeof(TEntity);
        if (!_repositories.TryGetValue(type, out var repository))
        {
            repository = new EfRepository<TEntity, int>(_context);
            _repositories[type] = repository;
        }
        return (IRepository<TEntity, int>)repository;
    }

    public async Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        // 自动填充审计字段
        foreach (var entry in _context.ChangeTracker.Entries<IAuditableEntity>())
        {
            switch (entry.State)
            {
                case EntityState.Added:
                    entry.Entity.CreatedAt = DateTime.UtcNow;
                    entry.Entity.CreatedBy = GetCurrentUserId();
                    break;
                case EntityState.Modified:
                    entry.Entity.UpdatedAt = DateTime.UtcNow;
                    entry.Entity.UpdatedBy = GetCurrentUserId();
                    break;
            }
        }

        return await _context.SaveChangesAsync(ct);
    }

    public async Task BeginTransactionAsync(CancellationToken ct = default)
    {
        if (_currentTransaction != null) return;
        _currentTransaction = await _context.Database.BeginTransactionAsync(ct);
    }

    public async Task CommitTransactionAsync(CancellationToken ct = default)
    {
        try
        {
            await _context.SaveChangesAsync(ct);
            await _currentTransaction?.CommitAsync(ct);
        }
        catch
        {
            await RollbackTransactionAsync(ct);
            throw;
        }
        finally
        {
            _currentTransaction?.Dispose();
            _currentTransaction = null;
        }
    }

    public async Task RollbackTransactionAsync(CancellationToken ct = default)
    {
        await _currentTransaction?.RollbackAsync(ct);
    }

    private string GetCurrentUserId()
    {
        // 从上下文获取当前用户
        return Thread.CurrentPrincipal?.Identity?.Name ?? "system";
    }

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

Query Object 模式

// 查询对象：将复杂查询条件封装为对象
public class ProductQuery
{
    public string Keyword { get; set; }
    public int? CategoryId { get; set; }
    public decimal? MinPrice { get; set; }
    public decimal? MaxPrice { get; set; }
    public bool? IsInStock { get; set; }
    public string SortBy { get; set; }
    public bool SortDescending { get; set; }
    public int Page { get; set; } = 1;
    public int PageSize { get; set; } = 20;
}

// 查询对象处理器
public static class ProductQueryExtensions
{
    public static IQueryable<Product> ApplyQuery(this IQueryable<Product> query, ProductQuery queryObj)
    {
        // 过滤
        if (!string.IsNullOrEmpty(queryObj.Keyword))
        {
            query = query.Where(p =>
                p.Name.Contains(queryObj.Keyword) ||
                p.Description.Contains(queryObj.Keyword));
        }

        if (queryObj.CategoryId.HasValue)
        {
            query = query.Where(p => p.CategoryId == queryObj.CategoryId.Value);
        }

        if (queryObj.MinPrice.HasValue)
        {
            query = query.Where(p => p.Price >= queryObj.MinPrice.Value);
        }

        if (queryObj.MaxPrice.HasValue)
        {
            query = query.Where(p => p.Price <= queryObj.MaxPrice.Value);
        }

        if (queryObj.IsInStock.HasValue)
        {
            query = query.Where(p => p.Stock > 0 == queryObj.IsInStock.Value);
        }

        // 排序
        query = queryObj.SortBy?.ToLower() switch
        {
            "name" => queryObj.SortDescending
                ? query.OrderByDescending(p => p.Name)
                : query.OrderBy(p => p.Name),
            "price" => queryObj.SortDescending
                ? query.OrderByDescending(p => p.Price)
                : query.OrderBy(p => p.Price),
            "createdat" => queryObj.SortDescending
                ? query.OrderByDescending(p => p.CreatedAt)
                : query.OrderBy(p => p.CreatedAt),
            _ => query.OrderByDescending(p => p.CreatedAt)
        };

        return query;
    }
}

// 使用
var products = await _context.Products
    .ApplyQuery(query)
    .Skip((query.Page - 1) * query.PageSize)
    .Take(query.PageSize)
    .ToListAsync();

Specification 模式

// 规约接口
public interface ISpecification<T>
{
    Expression<Func<T, bool>> Criteria { get; }
    List<Expression<Func<T, object>>> Includes { get; }
    List<string> IncludeStrings { get; }
    Expression<Func<T, object>> OrderBy { get; }
    Expression<Func<T, object>> OrderByDescending { get; }
    int? Take { get; }
    int? Skip { get; }
    bool IsPagingEnabled { get; }
    bool AsNoTracking { get; }
}

// 规约基类
public abstract class BaseSpecification<T> : ISpecification<T>
{
    public Expression<Func<T, bool>> Criteria { get; private set; }
    public List<Expression<Func<T, object>>> Includes { get; } = new();
    public List<string> IncludeStrings { get; } = new();
    public Expression<Func<T, object>> OrderBy { get; private set; }
    public Expression<Func<T, object>> OrderByDescending { get; private set; }
    public int? Take { get; private set; }
    public int? Skip { get; private set; }
    public bool IsPagingEnabled { get; private set; }
    public bool AsNoTracking { get; private set; } = true;

    protected BaseSpecification(Expression<Func<T, bool>> criteria)
    {
        Criteria = criteria;
    }

    protected BaseSpecification() { }

    protected void AddInclude(Expression<Func<T, object>> include)
    {
        Includes.Add(include);
    }

    protected void AddInclude(string includeString)
    {
        IncludeStrings.Add(includeString);
    }

    protected void ApplyOrderBy(Expression<Func<T, object>> orderBy)
    {
        OrderBy = orderBy;
    }

    protected void ApplyOrderByDescending(Expression<Func<T, object>> orderByDesc)
    {
        OrderByDescending = orderByDesc;
    }

    protected void ApplyPaging(int skip, int take)
    {
        Skip = skip;
        Take = take;
        IsPagingEnabled = true;
    }

    protected void EnableTracking()
    {
        AsNoTracking = false;
    }
}

// 规约评估器
public static class SpecificationEvaluator<T> where T : class
{
    public static IQueryable<T> Apply(IQueryable<T> query, ISpecification<T> spec)
    {
        var result = query;

        // 条件
        if (spec.Criteria != null)
        {
            result = result.Where(spec.Criteria);
        }

        // Include
        foreach (var include in spec.Includes)
        {
            result = result.Include(include);
        }

        foreach (var includeString in spec.IncludeStrings)
        {
            result = result.Include(includeString);
        }

        // 排序
        if (spec.OrderBy != null)
        {
            result = result.OrderBy(spec.OrderBy);
        }
        else if (spec.OrderByDescending != null)
        {
            result = result.OrderByDescending(spec.OrderByDescending);
        }

        // 分页
        if (spec.IsPagingEnabled)
        {
            result = result.Skip(spec.Skip.Value).Take(spec.Take.Value);
        }

        // 无跟踪
        if (spec.AsNoTracking)
        {
            result = result.AsNoTracking();
        }

        return result;
    }
}

// 具体规约示例
public class ActiveProductsByCategorySpec : BaseSpecification<Product>
{
    public ActiveProductsByCategorySpec(int categoryId, int page = 1, int pageSize = 20)
        : base(p => p.CategoryId == categoryId && p.IsActive)
    {
        AddInclude(p => p.Category);
        AddInclude(p => p.Images);
        ApplyOrderByDescending(p => p.CreatedAt);
        ApplyPaging((page - 1) * pageSize, pageSize);
    }
}

public class ProductSearchSpec : BaseSpecification<Product>
{
    public ProductSearchSpec(string keyword)
        : base(p => p.Name.Contains(keyword) || p.Description.Contains(keyword))
    {
        AddInclude(p => p.Category);
        ApplyOrderBy(p => p.Name);
    }
}

// 组合规约
public class AndSpecification<T> : BaseSpecification<T>
{
    public AndSpecification(ISpecification<T> left, ISpecification<T> right)
        : base()
    {
        var parameter = Expression.Parameter(typeof(T), "x");

        var leftExpr = ReplaceParameter(left.Criteria.Body, parameter);
        var rightExpr = ReplaceParameter(right.Criteria.Body, parameter);

        Criteria = Expression.Lambda<Func<T, bool>>(
            Expression.AndAlso(leftExpr, rightExpr), parameter);
    }

    private Expression ReplaceParameter(Expression expression, ParameterExpression parameter)
    {
        return new ParameterReplacer(expression, parameter).Visit(expression);
    }
}

CQRS 数据访问

// CQRS 分离读写模型
// 命令（写）端
public interface ICommandHandler<in TCommand> where TCommand : ICommand
{
    Task<Result> HandleAsync(TCommand command, CancellationToken ct = default);
}

public record CreateProductCommand(
    string Name, string Description, decimal Price, int CategoryId, int Stock) : ICommand;

public class CreateProductCommandHandler : ICommandHandler<CreateProductCommand>
{
    private readonly IRepository<Product, int> _repository;
    private readonly IUnitOfWork _unitOfWork;

    public CreateProductCommandHandler(IRepository<Product, int> repository, IUnitOfWork unitOfWork)
    {
        _repository = repository;
        _unitOfWork = unitOfWork;
    }

    public async Task<Result> HandleAsync(CreateProductCommand command, CancellationToken ct = default)
    {
        var product = new Product
        {
            Name = command.Name,
            Description = command.Description,
            Price = command.Price,
            CategoryId = command.CategoryId,
            Stock = command.Stock,
            IsActive = true,
            CreatedAt = DateTime.UtcNow
        };

        await _repository.AddAsync(product, ct);
        await _unitOfWork.SaveChangesAsync(ct);

        return Result.Success();
    }
}

// 查询（读）端
public interface IQueryHandler<in TQuery, TResult> where TQuery : IQuery<TResult>
{
    Task<TResult> HandleAsync(TQuery query, CancellationToken ct = default);
}

public record GetProductListQuery(
    string Keyword, int? CategoryId, int Page, int PageSize) : IQuery<PagedResult<ProductDto>>;

public class GetProductListQueryHandler : IQueryHandler<GetProductListQuery, PagedResult<ProductDto>>
{
    private readonly DbContext _context;

    // 查询端直接使用 DbContext，不通过 Repository
    public GetProductListQueryHandler(DbContext context)
    {
        _context = context;
    }

    public async Task<PagedResult<ProductDto>> HandleAsync(
        GetProductListQuery query, CancellationToken ct = default)
    {
        var dbQuery = _context.Set<Product>()
            .AsNoTracking()
            .Where(p => p.IsActive);

        if (!string.IsNullOrEmpty(query.Keyword))
        {
            dbQuery = dbQuery.Where(p => p.Name.Contains(query.Keyword));
        }

        if (query.CategoryId.HasValue)
        {
            dbQuery = dbQuery.Where(p => p.CategoryId == query.CategoryId.Value);
        }

        var totalCount = await dbQuery.CountAsync(ct);

        var items = await dbQuery
            .OrderByDescending(p => p.CreatedAt)
            .Skip((query.Page - 1) * query.PageSize)
            .Take(query.PageSize)
            .Select(p => new ProductDto
            {
                Id = p.Id,
                Name = p.Name,
                Price = p.Price,
                CategoryName = p.Category.Name,
                Stock = p.Stock
            })
            .ToListAsync(ct);

        return new PagedResult<ProductDto>
        {
            Items = items,
            TotalCount = totalCount,
            Page = query.Page,
            PageSize = query.PageSize
        };
    }
}

延迟加载 vs 立即加载

// 延迟加载（Lazy Loading）
// 需要安装 Microsoft.EntityFrameworkCore.Proxies
// 配置：
// optionsBuilder.UseLazyLoadingProxies();

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }

    // 导航属性标记为 virtual 以支持延迟加载
    public virtual ICollection<Product> Products { get; set; }
}

// 延迟加载的问题：N+1 查询
public async Task<List<CategoryDto>> GetCategoriesWithProducts_Lazy()
{
    var categories = await _context.Categories.ToListAsync();

    var result = new List<CategoryDto>();
    foreach (var cat in categories)
    {
        // 每次访问 Products 都触发一次 SQL 查询！
        var productCount = cat.Products.Count; // N+1 问题
        result.Add(new CategoryDto
        {
            Name = cat.Name,
            ProductCount = productCount
        });
    }
    return result;
}

// 立即加载（Eager Loading）
public async Task<List<CategoryDto>> GetCategoriesWithProducts_Eager()
{
    return await _context.Categories
        .Include(c => c.Products) // 一次 JOIN 查询
        .Select(c => new CategoryDto
        {
            Name = c.Name,
            ProductCount = c.Products.Count
        })
        .ToListAsync();
}

// 显式加载（Explicit Loading）
public async Task<Category> GetCategoryExplicit(int id)
{
    var category = await _context.Categories.FindAsync(id);

    // 需要时才加载关联数据
    await _context.Entry(category)
        .Collection(c => c.Products)
        .LoadAsync();

    return category;
}

// 选择性加载（Select Loading）—— 推荐方式
public async Task<List<CategoryDto>> GetCategoriesOptimized()
{
    return await _context.Categories
        .Select(c => new CategoryDto
        {
            Id = c.Id,
            Name = c.Name,
            ProductCount = c.Products.Count
        })
        .ToListAsync();
}

批量查询优化

// 批量操作优化
public class BatchOperationService
{
    private readonly DbContext _context;

    // 批量插入（EF Core 7+）
    public async Task BulkInsertAsync(List<Product> products)
    {
        // EF Core 7+ 原生支持 ExecuteUpdate/ExecuteDelete
        await _context.Products.AddRangeAsync(products);
        await _context.SaveChangesAsync();

        // 大批量数据建议使用 EFCore.BulkExtensions
        // await _context.BulkInsertAsync(products);
    }

    // 批量更新
    public async Task BulkUpdatePriceAsync(int categoryId, decimal priceMultiplier)
    {
        // EF Core 7+ 批量更新
        await _context.Products
            .Where(p => p.CategoryId == categoryId)
            .ExecuteUpdateAsync(setters => setters
                .SetProperty(p => p.Price, p => p.Price * priceMultiplier)
                .SetProperty(p => p.UpdatedAt, DateTime.UtcNow));
    }

    // 批量删除
    public async Task BulkDeleteInactiveAsync()
    {
        await _context.Products
            .Where(p => !p.IsActive && p.Stock == 0)
            .ExecuteDeleteAsync();
    }

    // 使用临时表优化 IN 查询
    public async Task<List<Product>> GetProductsByIds(List<int> ids)
    {
        // 当 ID 列表很大时，使用 JOIN 替代 WHERE IN
        var idQuery = ids.Select(id => new { Id = id });

        return await _context.Products
            .Join(idQuery, p => p.Id, i => i.Id, (p, _) => p)
            .ToListAsync();
    }
}

审计追踪模式

// 审计接口
public interface IAuditableEntity
{
    DateTime CreatedAt { get; set; }
    string CreatedBy { get; set; }
    DateTime? UpdatedAt { get; set; }
    string UpdatedBy { get; set; }
}

// 软删除接口
public interface ISoftDeletableEntity
{
    bool IsDeleted { get; set; }
    DateTime? DeletedAt { get; set; }
    string DeletedBy { get; set; }
}

// 审计 DbContext 基类
public abstract class AuditableDbContext : DbContext
{
    private readonly IUserContext _userContext;

    protected AuditableDbContext(DbContextOptions options, IUserContext userContext)
        : base(options)
    {
        _userContext = userContext;
    }

    public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        var entries = ChangeTracker.Entries()
            .Where(e => e.Entity is IAuditableEntity &&
                       (e.State == EntityState.Added || e.State == EntityState.Modified));

        var auditEntries = new List<AuditLog>();

        foreach (var entry in entries)
        {
            var entity = (IAuditableEntity)entry.Entity;
            var currentUser = _userContext.CurrentUserId ?? "system";

            switch (entry.State)
            {
                case EntityState.Added:
                    entity.CreatedAt = DateTime.UtcNow;
                    entity.CreatedBy = currentUser;
                    break;
                case EntityState.Modified:
                    entity.UpdatedAt = DateTime.UtcNow;
                    entity.UpdatedBy = currentUser;
                    break;
            }

            // 记录变更详情
            auditEntries.Add(CreateAuditLog(entry));
        }

        // 保存审计日志
        if (auditEntries.Any())
        {
            Set<AuditLog>().AddRange(auditEntries);
        }

        return await base.SaveChangesAsync(ct);
    }

    private AuditLog CreateAuditLog(EntityEntry entry)
    {
        var auditLog = new AuditLog
        {
            EntityType = entry.Entity.GetType().Name,
            EntityId = entry.Properties.FirstOrDefault(p => p.Metadata.IsPrimaryKey())?.CurrentValue?.ToString(),
            Action = entry.State.ToString(),
            Timestamp = DateTime.UtcNow,
            UserId = _userContext.CurrentUserId,
            Changes = new Dictionary<string, (object OldValue, object NewValue)>()
        };

        foreach (var property in entry.Properties)
        {
            if (property.IsModified)
            {
                auditLog.Changes[property.Metadata.Name] = (
                    property.OriginalValue,
                    property.CurrentValue);
            }
        }

        return auditLog;
    }
}

// 审计日志实体
public class AuditLog
{
    public int Id { get; set; }
    public string EntityType { get; set; }
    public string EntityId { get; set; }
    public string Action { get; set; }
    public DateTime Timestamp { get; set; }
    public string UserId { get; set; }
    public Dictionary<string, (object OldValue, object NewValue)> Changes { get; set; }
    public string ChangesJson
    {
        get => JsonSerializer.Serialize(Changes);
        set => Changes = JsonSerializer.Deserialize<Dictionary<string, (object, object)>>(value);
    }
}

软删除模式

// 软删除在 DbContext 中的实现
public class SoftDeleteDbContext : AuditableDbContext
{
    public SoftDeleteDbContext(DbContextOptions options, IUserContext userContext)
        : base(options, userContext) { }

    public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        // 处理软删除
        var softDeleteEntries = ChangeTracker.Entries<ISoftDeletableEntity>()
            .Where(e => e.State == EntityState.Deleted);

        foreach (var entry in softDeleteEntries)
        {
            // 将 DELETE 改为 UPDATE
            entry.State = EntityState.Modified;
            entry.Entity.IsDeleted = true;
            entry.Entity.DeletedAt = DateTime.UtcNow;
            entry.Entity.DeletedBy = _userContext.CurrentUserId;
        }

        return await base.SaveChangesAsync(ct);
    }

    // 全局查询过滤器：自动过滤已删除的实体
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // 为所有实现 ISoftDeletableEntity 的实体添加全局过滤器
        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            if (typeof(ISoftDeletableEntity).IsAssignableFrom(entityType.ClrType))
            {
                modelBuilder.Entity(entityType.ClrType)
                    .HasQueryFilter(
                        GenerateSoftDeleteFilter(entityType.ClrType));
            }
        }
    }

    private static LambdaExpression GenerateSoftDeleteFilter(Type entityType)
    {
        var parameter = Expression.Parameter(entityType, "e");
        var property = Expression.Property(parameter, nameof(ISoftDeletableEntity.IsDeleted));
        var filter = Expression.Lambda(Expression.Not(property), parameter);
        return filter;
    }
}

多租户数据访问

// 多租户接口
public interface ITenantEntity
{
    string TenantId { get; set; }
}

// 租户上下文
public interface ITenantContext
{
    string CurrentTenantId { get; }
}

// 多租户 DbContext
public class MultiTenantDbContext : SoftDeleteDbContext
{
    private readonly ITenantContext _tenantContext;

    public MultiTenantDbContext(
        DbContextOptions options,
        IUserContext userContext,
        ITenantContext tenantContext)
        : base(options, userContext)
    {
        _tenantContext = tenantContext;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // 为所有租户实体添加全局过滤器
        foreach (var entityType in modelBuilder.Model.GetEntityTypes())
        {
            if (typeof(ITenantEntity).IsAssignableFrom(entityType.ClrType))
            {
                var parameter = Expression.Parameter(entityType.ClrType, "e");
                var tenantProperty = Expression.Property(parameter, nameof(ITenantEntity.TenantId));

                var tenantId = Expression.Constant(_tenantContext.CurrentTenantId);
                var filter = Expression.Lambda(Expression.Equal(tenantProperty, tenantId), parameter);

                modelBuilder.Entity(entityType.ClrType).HasQueryFilter(filter);
            }
        }
    }

    public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
    {
        // 自动设置租户 ID
        var tenantEntries = ChangeTracker.Entries<ITenantEntity>()
            .Where(e => e.State == EntityState.Added);

        foreach (var entry in tenantEntries)
        {
            entry.Entity.TenantId = _tenantContext.CurrentTenantId;
        }

        return await base.SaveChangesAsync(ct);
    }
}

// 租户感知的 Repository
public class TenantAwareRepository<TEntity> : IRepository<TEntity, int>
    where TEntity : class, ITenantEntity
{
    private readonly IRepository<TEntity, int> _inner;
    private readonly ITenantContext _tenantContext;

    public TenantAwareRepository(IRepository<TEntity, int> inner, ITenantContext tenantContext)
    {
        _inner = inner;
        _tenantContext = tenantContext;
    }

    public async Task AddAsync(TEntity entity, CancellationToken ct = default)
    {
        entity.TenantId = _tenantContext.CurrentTenantId;
        await _inner.AddAsync(entity, ct);
    }

    // ... 其他方法委托给 _inner，全局过滤器自动处理查询
}

缓存层

// 缓存层装饰器
public class CachedRepository<TEntity> : IRepository<TEntity, int>
    where TEntity : class
{
    private readonly IRepository<TEntity, int> _inner;
    private readonly IDistributedCache _cache;
    private readonly TimeSpan _cacheDuration;
    private readonly ILogger _logger;

    public CachedRepository(
        IRepository<TEntity, int> inner,
        IDistributedCache cache,
        TimeSpan cacheDuration,
        ILogger logger)
    {
        _inner = inner;
        _cache = cache;
        _cacheDuration = cacheDuration;
        _logger = logger;
    }

    public async Task<TEntity> GetByIdAsync(int id, CancellationToken ct = default)
    {
        var cacheKey = $"{typeof(TEntity).Name}:{id}";
        var cached = await _cache.GetStringAsync(cacheKey, ct);

        if (cached != null)
        {
            _logger.LogDebug($"缓存命中: {cacheKey}");
            return JsonSerializer.Deserialize<TEntity>(cached);
        }

        var entity = await _inner.GetByIdAsync(id, ct);
        if (entity != null)
        {
            await _cache.SetStringAsync(cacheKey,
                JsonSerializer.Serialize(entity),
                new DistributedCacheEntryOptions { AbsoluteExpirationRelativeToNow = _cacheDuration },
                ct);
        }

        return entity;
    }

    public async Task AddAsync(TEntity entity, CancellationToken ct = default)
    {
        await _inner.AddAsync(entity, ct);
        await InvalidateCacheAsync(entity);
    }

    public void Update(TEntity entity)
    {
        _inner.Update(entity);
        InvalidateCacheAsync(entity).GetAwaiter().GetResult();
    }

    public void Remove(TEntity entity)
    {
        _inner.Remove(entity);
        InvalidateCacheAsync(entity).GetAwaiter().GetResult();
    }

    // 委托其他方法到内部 Repository
    public Task<IReadOnlyList<TEntity>> GetAllAsync(CancellationToken ct = default)
        => _inner.GetAllAsync(ct);

    public Task<IReadOnlyList<TEntity>> FindAsync(ISpecification<TEntity> spec, CancellationToken ct = default)
        => _inner.FindAsync(spec, ct);

    private async Task InvalidateCacheAsync(TEntity entity)
    {
        if (entity is Entity<int> identifiable)
        {
            var cacheKey = $"{typeof(TEntity).Name}:{identifiable.Id}";
            await _cache.RemoveAsync(cacheKey);
        }
    }

    // ... 其他方法的委托实现
}

// 缓存注册
public static class CacheExtensions
{
    public static IServiceCollection AddCachedRepository<TEntity>(
        this IServiceCollection services,
        TimeSpan cacheDuration)
        where TEntity : class
    {
        services.AddScoped<IRepository<TEntity, int>>(sp =>
        {
            var inner = new EfRepository<TEntity, int>(sp.GetRequiredService<DbContext>());
            var cache = sp.GetRequiredService<IDistributedCache>();
            var logger = sp.GetRequiredService<ILogger<CachedRepository<TEntity>>>();
            return new CachedRepository<TEntity>(inner, cache, cacheDuration, logger);
        });
        return services;
    }
}

优点

关注点分离：数据访问逻辑集中管理，业务层简洁
可测试性：通过接口抽象支持 Mock 测试
可维护性：查询逻辑集中，修改影响面可控
性能优化：批量操作、缓存等策略集中实现
横切关注点：审计、软删除、多租户等统一处理

缺点

过度抽象：简单的 CRUD 可能不需要 Repository 层
性能隐藏：抽象层可能隐藏底层查询性能问题
学习曲线：团队需要理解多种模式的适用场景
灵活性限制：复杂的数据库特定查询可能难以通过抽象表达

性能注意事项

N+1 查询：延迟加载导致的经典性能问题，优先使用 Eager Loading 或 Select
Change Tracker 开销：只读场景使用 AsNoTracking()
批量操作：大批量数据使用 EFCore.BulkExtensions
查询分页：始终对大数据集使用分页
索引优化：Specification 中的查询条件应有对应索引
连接管理：避免长时间持有 DbContext

总结

数据访问层设计模式提供了从简单到复杂的多层次方案。对于简单应用，直接使用 EF Core DbContext 即可；对于中等复杂度应用，Repository + Unit of Work 提供了良好的抽象；对于复杂应用，CQRS + Specification + 缓存层的组合提供了灵活的架构。选择合适的模式组合，避免过度设计。

关键知识点

Repository 职责边界：封装数据访问，不包含业务逻辑
Unit of Work 事务边界：确保一组操作的事务一致性
Specification 可组合性：规约可组合、可复用、可测试
CQRS 读写分离：读操作和写操作可以独立优化
全局查询过滤器：EF Core 的 HasQueryFilter 实现软删除和多租户
装饰器模式：通过装饰器叠加缓存、日志等横切关注点

常见误区

Repository 返回 IQueryable：泄露查询细节给业务层
每个实体一个 Repository：导致大量重复代码
忽略 Change Tracker：只读查询未使用 AsNoTracking
N+1 查询：延迟加载的导航属性在循环中访问
事务范围过大：UnitOfWork 包含过多操作增加锁持有时间
缓存不一致：更新数据后未正确失效缓存

进阶路线

领域驱动设计：聚合根 Repository、领域事件
Event Sourcing：事件溯源替代 CRUD
分库分表：Sharding 策略和路由
读写分离：主从数据库的数据同步和路由
多模型持久化：关系数据库 + 文档数据库 + 缓存的组合使用
低代码数据访问：动态查询构建器和代码生成

适用场景

企业管理系统：审计、软删除、多租户需求
电商平台：复杂查询、缓存、批量操作
SaaS 应用：多租户数据隔离
数据分析系统：CQRS 读写分离
内容管理系统：版本控制、软删除

落地建议

按需选择模式：简单应用不要过度设计
接口优先：定义清晰的接口，实现可替换
统一基类：DbContext 基类统一处理横切关注点
性能监控：监控慢查询和 N+1 问题
集成测试：使用 InMemoryDatabase 或 Testcontainers 测试数据访问层
文档化查询：复杂查询应添加 SQL 注释或文档

排错清单

是否存在 N+1 查询？检查循环中的延迟加载
Change Tracker 是否有不必要的追踪？检查 AsNoTracking 使用
软删除的查询是否正确过滤？检查全局过滤器
多租户数据是否隔离？检查 TenantId 过滤
审计日志是否正确记录？检查 SaveChanges 中的逻辑
缓存是否正确失效？检查更新操作的缓存清理
事务是否正确提交/回滚？检查 UnitOfWork 使用
分页查询是否高效？检查 Count 和数据查询

复盘问题

你的数据访问层是否过度抽象？哪些 Repository 可以简化？
有没有 N+1 查询的性能问题？
审计日志的粒度是否满足合规要求？
多租户方案是否经过了安全审计？
缓存策略的失效机制是否可靠？
数据访问层的单元测试覆盖率如何？