Entity Framework Core

EF Core 核心概念

Blog 系统数据模型

// 实体定义
public class Blog
{
    public int    Id       { get; set; }
    public string Title   { get; set; } = string.Empty;
    public string Slug    { get; set; } = string.Empty;
    public DateTimeOffset CreatedAt { get; set; }

    // 导航属性
    public ICollection<Post> Posts { get; } = [];
}

public class Post
{
    public int    Id       { get; set; }
    public string Title   { get; set; } = string.Empty;
    public string Content { get; set; } = string.Empty;
    public bool   IsPublished { get; set; }
    public DateTimeOffset PublishedAt { get; set; }

    // 外键
    public int    BlogId  { get; set; }
    public Blog   Blog    { get; set; } = null!;
    public int    AuthorId { get; set; }
    public Author Author  { get; set; } = null!;

    // 多对多：文章 ↔ 标签
    public ICollection<Tag> Tags     { get; } = [];
    public ICollection<Comment> Comments { get; } = [];

    // 并发令牌（乐观并发控制）
    [Timestamp]
    public byte[] RowVersion { get; set; } = [];
}

DbContext 与 Fluent API 配置

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

    public DbSet<Blog>    Blogs    => Set<Blog>();
    public DbSet<Post>    Posts    => Set<Post>();
    public DbSet<Tag>     Tags     => Set<Tag>();
    public DbSet<Comment> Comments => Set<Comment>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Post 实体详细配置
        modelBuilder.Entity<Post>(entity =>
        {
            entity.ToTable("posts");
            entity.HasKey(p => p.Id);
            entity.Property(p => p.Title)
                .IsRequired()
                .HasMaxLength(200);
            entity.Property(p => p.Content).HasColumnType("text");

            // 索引
            entity.HasIndex(p => p.IsPublished).HasFilter("is_published = true");

            // 一对多关系
            entity.HasOne(p => p.Blog)
                .WithMany(b => b.Posts)
                .HasForeignKey(p => p.BlogId)
                .OnDelete(DeleteBehavior.Cascade);

            // 多对多（EF Core 5+ 自动创建连接表）
            entity.HasMany(p => p.Tags)
                .WithMany(t => t.Posts)
                .UsingEntity("post_tags");
        });

        // 值对象：作者地址（不单独建表）
        modelBuilder.Entity<Author>()
            .OwnsOne(a => a.Address, addr =>
            {
                addr.Property(a => a.City).HasMaxLength(100);
                addr.Property(a => a.Country).HasMaxLength(2);
            });
    }
}

查询优化

AsNoTracking：只读查询

// 不需要修改的查询：加 AsNoTracking() 提升性能
var posts = await _db.Posts
    .AsNoTracking()              // 不追踪，减少内存和 CPU 开销
    .Where(p => p.IsPublished)
    .OrderByDescending(p => p.PublishedAt)
    .Take(20)
    .Select(p => new PostSummaryDto(  // Select 投影：只取需要的字段
        p.Id, p.Title, p.PublishedAt, p.Author.Name
    ))
    .ToListAsync(ct);

Include/ThenInclude：预加载关联数据

// 预加载：避免 N+1 问题
var blog = await _db.Blogs
    .Include(b => b.Posts)                  // 加载文章
        .ThenInclude(p => p.Tags)            // 加载文章的标签
    .Include(b => b.Posts)
        .ThenInclude(p => p.Author)          // 加载文章的作者
    .FirstOrDefaultAsync(b => b.Slug == slug, ct);

// ❌ N+1 问题示例（每篇文章额外查询一次作者）
var posts = await _db.Posts.ToListAsync();
foreach (var post in posts)
    Console.WriteLine(post.Author.Name);  // 每次访问 Author 触发额外查询！

数据迁移

# 安装 EF Core CLI 工具
dotnet tool install --global dotnet-ef

# 创建迁移
dotnet ef migrations add InitialCreate --project src/Data --startup-project src/Api

# 应用迁移到数据库
dotnet ef database update

# 回滚到上一个迁移
dotnet ef database update PreviousMigrationName

# 生成 SQL 脚本（生产部署用）
dotnet ef migrations script --idempotent -o migrations.sql

// 程序启动时自动应用迁移（仅开发/CI 环境推荐）
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<BlogDbContext>();
    await db.Database.MigrateAsync();
}

乐观并发控制

当多个用户同时编辑同一条记录时，乐观并发控制确保只有第一个提交的人成功，其他人收到冲突错误。EF Core 通过 RowVersion 或 [ConcurrencyCheck] 属性实现。

public async Task<Post> UpdatePostAsync(UpdatePostRequest req, CancellationToken ct)
{
    var post = await _db.Posts.FindAsync([req.Id], ct)
        ?? throw new NotFoundException($"Post {req.Id} not found");

    post.Title   = req.Title;
    post.Content = req.Content;

    // 设置客户端传来的 RowVersion，EF Core 在 WHERE 子句中加入版本校验
    _db.Entry(post).OriginalValues["RowVersion"] = req.RowVersion;

    try
    {
        await _db.SaveChangesAsync(ct);
        return post;
    }
    catch (DbUpdateConcurrencyException ex)
    {
        // 并发冲突：通知用户重新加载最新数据
        var current = ex.Entries.First();
        var db = await current.GetDatabaseValuesAsync(ct);
        throw new ConflictException("该文章已被其他人修改，请刷新后重试", db!);
    }
}

原生 SQL 与存储过程

// FromSqlRaw：将 SQL 结果映射到实体
var posts = await _db.Posts
    .FromSqlRaw("SELECT * FROM posts WHERE author_id = {0}", authorId)
    .AsNoTracking()
    .ToListAsync(ct);

// ExecuteSqlRaw：执行 DML（INSERT/UPDATE/DELETE）
int affected = await _db.Database
    .ExecuteSqlRawAsync(
        "UPDATE posts SET view_count = view_count + 1 WHERE id = {0}",
        postId);

// 参数化查询（防 SQL 注入，推荐）
var slug = "hello-world";
var post = await _db.Posts
    .FromSql($"SELECT * FROM posts WHERE slug = {slug}")  // 自动参数化
    .FirstOrDefaultAsync(ct);