Entity Framework Core 数据访问

安装和配置

安装依赖包

# SQL Server
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

# MySQL
dotnet add package Pomelo.EntityFrameworkCore.MySql

# PostgreSQL
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

# SQLite
dotnet add package Microsoft.EntityFrameworkCore.Sqlite

# 工具包
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Design

配置数据库上下文

SQL Server

using Microsoft.EntityFrameworkCore;

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

    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<Order> Orders { get; set; }

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

        // 配置实体
        modelBuilder.Entity<Product>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Name).IsRequired().HasMaxLength(200);
            entity.Property(e => e.Price).HasColumnType("decimal(18,2)");
            entity.HasOne(e => e.Category)
                  .WithMany(c => c.Products)
                  .HasForeignKey(e => e.CategoryId);
        });
    }
}

// Program.cs 配置
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=MyApp;User Id=sa;Password=YourPassword;TrustServerCertificate=True;"
  }
}

MySQL

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseMySql(
        builder.Configuration.GetConnectionString("DefaultConnection"),
        ServerVersion.AutoDetect(builder.Configuration.GetConnectionString("DefaultConnection"))
    ));

appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=myapp;User=root;Password=password;"
  }
}

PostgreSQL

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=myapp;Username=postgres;Password=password"
  }
}

实体定义

基础实体

// 基础实体类
public abstract class BaseEntity
{
    public int Id { get; set; }
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime? UpdatedAt { get; set; }
    public bool IsDeleted { get; set; } = false;
}

// 产品实体
public class Product : BaseEntity
{
    public string Name { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int Stock { get; set; }
    public int CategoryId { get; set; }

    // 导航属性
    public Category Category { get; set; } = null!;
    public ICollection<OrderItem> OrderItems { get; set; } = new List<OrderItem>();
}

// 分类实体
public class Category : BaseEntity
{
    public string Name { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;

    // 导航属性
    public ICollection<Product> Products { get; set; } = new List<Product>();
}

// 订单实体
public class Order : BaseEntity
{
    public string OrderNumber { get; set; } = string.Empty;
    public int UserId { get; set; }
    public decimal TotalAmount { get; set; }
    public OrderStatus Status { get; set; }

    // 导航属性
    public User User { get; set; } = null!;
    public ICollection<OrderItem> OrderItems { get; set; } = new List<OrderItem>();
}

// 订单明细
public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }

    // 导航属性
    public Order Order { get; set; } = null!;
    public Product Product { get; set; } = null!;
}

public enum OrderStatus
{
    Pending,
    Processing,
    Shipped,
    Delivered,
    Cancelled
}

数据库迁移

迁移命令

# 添加迁移
dotnet ef migrations add InitialCreate

# 更新数据库
dotnet ef database update

# 回滚到指定迁移
dotnet ef database update PreviousMigration

# 删除最后一次迁移
dotnet ef migrations remove

# 生成SQL脚本
dotnet ef migrations script

# 删除数据库
dotnet ef database drop

# 查看迁移列表
dotnet ef migrations list

初始化数据

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

    // 种子数据
    modelBuilder.Entity<Category>().HasData(
        new Category { Id = 1, Name = "Electronics", Description = "Electronic devices" },
        new Category { Id = 2, Name = "Books", Description = "Books and magazines" },
        new Category { Id = 3, Name = "Clothing", Description = "Clothing and accessories" }
    );

    modelBuilder.Entity<Product>().HasData(
        new Product { Id = 1, Name = "Laptop", Price = 999.99m, Stock = 10, CategoryId = 1 },
        new Product { Id = 2, Name = "Mouse", Price = 29.99m, Stock = 50, CategoryId = 1 },
        new Product { Id = 3, Name = "C# Book", Price = 49.99m, Stock = 20, CategoryId = 2 }
    );
}

CRUD 操作

仓储模式

// 通用仓储接口
public interface IRepository<T> where T : BaseEntity
{
    Task<T?> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();
    Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> predicate);
    Task<T> AddAsync(T entity);
    Task UpdateAsync(T entity);
    Task DeleteAsync(int id);
    Task<bool> ExistsAsync(int id);
}

// 通用仓储实现
public class Repository<T> : IRepository<T> where T : BaseEntity
{
    protected readonly AppDbContext _context;
    protected readonly DbSet<T> _dbSet;

    public Repository(AppDbContext 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.Where(e => !e.IsDeleted).ToListAsync();
    }

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

    public async Task<T> AddAsync(T entity)
    {
        entity.CreatedAt = DateTime.UtcNow;
        await _dbSet.AddAsync(entity);
        await _context.SaveChangesAsync();
        return entity;
    }

    public async Task UpdateAsync(T entity)
    {
        entity.UpdatedAt = DateTime.UtcNow;
        _dbSet.Update(entity);
        await _context.SaveChangesAsync();
    }

    public async Task DeleteAsync(int id)
    {
        var entity = await GetByIdAsync(id);
        if (entity != null)
        {
            entity.IsDeleted = true;
            entity.UpdatedAt = DateTime.UtcNow;
            await _context.SaveChangesAsync();
        }
    }

    public async Task<bool> ExistsAsync(int id)
    {
        return await _dbSet.AnyAsync(e => e.Id == id && !e.IsDeleted);
    }
}

// 特定仓储接口
public interface IProductRepository : IRepository<Product>
{
    Task<IEnumerable<Product>> GetByCategoryAsync(int categoryId);
    Task<IEnumerable<Product>> GetLowStockProductsAsync(int threshold);
}

// 特定仓储实现
public class ProductRepository : Repository<Product>, IProductRepository
{
    public ProductRepository(AppDbContext context) : base(context)
    {
    }

    public async Task<IEnumerable<Product>> GetByCategoryAsync(int categoryId)
    {
        return await _dbSet
            .Include(p => p.Category)
            .Where(p => p.CategoryId == categoryId && !p.IsDeleted)
            .ToListAsync();
    }

    public async Task<IEnumerable<Product>> GetLowStockProductsAsync(int threshold)
    {
        return await _dbSet
            .Where(p => p.Stock < threshold && !p.IsDeleted)
            .ToListAsync();
    }
}

// 注册服务
builder.Services.AddScoped(typeof(IRepository<>), typeof(Repository<>));
builder.Services.AddScoped<IProductRepository, ProductRepository>();

使用示例

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IProductRepository _productRepository;

    public ProductsController(IProductRepository productRepository)
    {
        _productRepository = productRepository;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<Product>>> GetAll()
    {
        var products = await _productRepository.GetAllAsync();
        return Ok(products);
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<Product>> GetById(int id)
    {
        var product = await _productRepository.GetByIdAsync(id);
        if (product == null)
            return NotFound();

        return Ok(product);
    }

    [HttpPost]
    public async Task<ActionResult<Product>> Create(Product product)
    {
        var created = await _productRepository.AddAsync(product);
        return CreatedAtAction(nameof(GetById), new { id = created.Id }, created);
    }

    [HttpPut("{id}")]
    public async Task<IActionResult> Update(int id, Product product)
    {
        if (id != product.Id)
            return BadRequest();

        await _productRepository.UpdateAsync(product);
        return NoContent();
    }

    [HttpDelete("{id}")]
    public async Task<IActionResult> Delete(int id)
    {
        await _productRepository.DeleteAsync(id);
        return NoContent();
    }

    [HttpGet("category/{categoryId}")]
    public async Task<ActionResult<IEnumerable<Product>>> GetByCategory(int categoryId)
    {
        var products = await _productRepository.GetByCategoryAsync(categoryId);
        return Ok(products);
    }
}

查询技巧

Include 预加载

// 单个导航属性
var products = await _context.Products
    .Include(p => p.Category)
    .ToListAsync();

// 多个导航属性
var orders = await _context.Orders
    .Include(o => o.User)
    .Include(o => o.OrderItems)
    .ToListAsync();

// 嵌套Include
var orders = await _context.Orders
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .ToListAsync();

// 条件Include
var orders = await _context.Orders
    .Include(o => o.OrderItems.Where(oi => oi.Quantity > 1))
    .ToListAsync();

分页查询

public class PagedResult<T>
{
    public List<T> Items { get; set; } = new();
    public int TotalCount { get; set; }
    public int PageNumber { get; set; }
    public int PageSize { get; set; }
    public int TotalPages => (int)Math.Ceiling(TotalCount / (double)PageSize);
}

public async Task<PagedResult<Product>> GetPagedProductsAsync(int pageNumber, int pageSize)
{
    var query = _context.Products.Where(p => !p.IsDeleted);

    var totalCount = await query.CountAsync();
    var items = await query
        .Skip((pageNumber - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return new PagedResult<Product>
    {
        Items = items,
        TotalCount = totalCount,
        PageNumber = pageNumber,
        PageSize = pageSize
    };
}

原始SQL查询

// 执行原始SQL
var products = await _context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 100)
    .ToListAsync();

// 存储过程
var products = await _context.Products
    .FromSqlRaw("EXEC GetProductsByCategory @CategoryId = {0}", categoryId)
    .ToListAsync();

// 执行非查询SQL
await _context.Database.ExecuteSqlRawAsync(
    "UPDATE Products SET Stock = Stock - {0} WHERE Id = {1}", quantity, productId);

事务处理

public async Task<Order> CreateOrderAsync(Order order, List<OrderItem> items)
{
    using var transaction = await _context.Database.BeginTransactionAsync();
    try
    {
        // 创建订单
        _context.Orders.Add(order);
        await _context.SaveChangesAsync();

        // 添加订单明细
        foreach (var item in items)
        {
            item.OrderId = order.Id;
            _context.OrderItems.Add(item);

            // 更新库存
            var product = await _context.Products.FindAsync(item.ProductId);
            if (product != null)
            {
                product.Stock -= item.Quantity;
            }
        }

        await _context.SaveChangesAsync();
        await transaction.CommitAsync();

        return order;
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

性能优化

AsNoTracking

// 只读查询使用 AsNoTracking
var products = await _context.Products
    .AsNoTracking()
    .ToListAsync();

// 全局配置
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
{
    ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}

批量操作

// 批量插入
var products = new List<Product>
{
    new Product { Name = "Product 1", Price = 10 },
    new Product { Name = "Product 2", Price = 20 }
};
await _context.Products.AddRangeAsync(products);
await _context.SaveChangesAsync();

// 批量更新（使用第三方库 EFCore.BulkExtensions）
await _context.BulkUpdateAsync(products);

// 批量删除
_context.Products.RemoveRange(productsToDelete);
await _context.SaveChangesAsync();

延迟加载

// 安装包
// dotnet add package Microsoft.EntityFrameworkCore.Proxies

// 启用延迟加载
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .UseLazyLoadingProxies());

// 实体中使用 virtual
public class Product
{
    public virtual Category Category { get; set; }
    public virtual ICollection<OrderItem> OrderItems { get; set; }
}

最佳实践

EF Core 最佳实践

性能

• ✅ 对只读查询使用 AsNoTracking()
• ✅ 使用分页避免加载大量数据
• ✅ 合理使用 Include 避免 N+1 问题
• ✅ 使用投影（Select）只获取需要的字段
• ✅ 批量操作使用 AddRange/UpdateRange

安全

• ✅ 使用参数化查询防止 SQL 注入
• ✅ 验证用户输入
• ✅ 实现软删除
• ✅ 使用事务保证数据一致性
• ✅ 记录审计日志

架构

• ✅ 使用仓储模式分离数据访问
• ✅ 使用 UnitOfWork 模式管理事务
• ✅ 实体和 DTO 分离
• ✅ 配置独立到 Configurations 类
• ✅ 使用迁移管理数据库变更

可维护性

• ✅ 使用 Fluent API 配置实体
• ✅ 遵循命名约定
• ✅ 添加适当的索引
• ✅ 编写单元测试（使用 InMemory Provider）
• ✅ 记录复杂查询的注释

相关资源

• EF Core 官方文档
• EF Core GitHub
• Pomelo MySQL Provider
• Npgsql PostgreSQL Provider