多对多关系配置 Many-to-Many

多对多关系基础

概念理解

多对多关系(Many-to-Many) 是指两个实体集合之间可以相互关联多个实例的关系。

csharp

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    // 一个学生可以选修多门课程
    public ICollection<Course> Courses { get; set; }
}

public class Course
{
    public int Id { get; set; }
    public string Title { get; set; }
    
    // 一门课程可以被多个学生选修
    public ICollection<Student> Students { get; set; }
}

// 关系:
// Student (*) ←→ (*) Course
// 一个学生可以选多门课
// 一门课可以有多个学生

数据库实现

传统方式: 需要联接表(Junction Table)

sql

-- Students 表
CREATE TABLE Students (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL
);

-- Courses 表
CREATE TABLE Courses (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Title NVARCHAR(200) NOT NULL
);

-- 联接表(StudentCourses)
CREATE TABLE StudentCourses (
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    
    -- 复合主键
    CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentId, CourseId),
    
    -- 外键约束
    CONSTRAINT FK_StudentCourses_Students 
        FOREIGN KEY (StudentId) REFERENCES Students(Id) ON DELETE CASCADE,
    CONSTRAINT FK_StudentCourses_Courses 
        FOREIGN KEY (CourseId) REFERENCES Courses(Id) ON DELETE CASCADE
);

-- 唯一索引(防止重复选课)
CREATE UNIQUE INDEX IX_StudentCourses_Student_Course 
ON StudentCourses (StudentId, CourseId);

示例数据:

Students:
| Id | Name   |
|----|--------|
| 1  | Alice  |
| 2  | Bob    |

Courses:
| Id | Title      |
|----|------------|
| 1  | Math       |
| 2  | Physics    |

StudentCourses (联接表):
| StudentId | CourseId |
|-----------|----------|
| 1         | 1        |  ← Alice 选修 Math
| 1         | 2        |  ← Alice 选修 Physics
| 2         | 1        |  ← Bob 选修 Math

.NET 5+ 隐式联接表

自动管理联接表

.NET 5+ / EF Core 5.0+ 引入了简化的多对多配置,无需显式定义联接表实体。

csharp

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Course> Courses { get; set; } = new List<Course>();
}

public class Course
{
    public int Id { get; set; }
    public string Title { get; set; }
    public ICollection<Student> Students { get; set; } = new List<Student>();
}

// 配置(最简单的方式)
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
        .HasMany(s => s.Courses)
        .WithMany(c => c.Students);
}

// EF Core 自动创建和管理联接表!
// 表名: CourseStudent (按字母顺序)
// 列名: CoursesId, StudentsId

自定义联接表名称

csharp

modelBuilder.Entity<Student>()
    .HasMany(s => s.Courses)
    .WithMany(c => c.Students)
    .UsingEntity(j => j.ToTable("Enrollments"));  // 自定义表名

// 生成的表: Enrollments(StudentId, CourseId)

自定义联接表列名

csharp

modelBuilder.Entity<Student>()
    .HasMany(s => s.Courses)
    .WithMany(c => c.Students)
    .UsingEntity<Dictionary<string, object>>(
        "Enrollment",  // 表名
        j => j.HasOne<Course>().WithMany().HasForeignKey("CourseId"),
        j => j.HasOne<Student>().WithMany().HasForeignKey("StudentId")
    );

// 生成的表: Enrollment(StudentId, CourseId)

基本 CRUD 操作

创建关系

csharp

// 方式 1: 添加到导航集合
var student = await context.Students.FindAsync(1);
var course = await context.Courses.FindAsync(1);

student.Courses.Add(course);
await context.SaveChangesAsync();

// SQL:
// INSERT INTO StudentCourses (StudentId, CourseId) VALUES (1, 1);

查询关系

csharp

// 查询学生及其课程
var student = await context.Students
    .Include(s => s.Courses)
    .FirstOrDefaultAsync(s => s.Id == 1);

foreach (var course in student.Courses)
{
    Console.WriteLine($"{student.Name} enrolled in {course.Title}");
}

// 查询课程及其学生
var course = await context.Courses
    .Include(c => c.Students)
    .FirstOrDefaultAsync(c => c.Id == 1);

foreach (var student in course.Students)
{
    Console.WriteLine($"{student.Name} is in {course.Title}");
}

删除关系

csharp

// 取消选课
var student = await context.Students
    .Include(s => s.Courses)
    .FirstOrDefaultAsync(s => s.Id == 1);

var course = student.Courses.FirstOrDefault(c => c.Id == 1);
if (course != null)
{
    student.Courses.Remove(course);
    await context.SaveChangesAsync();
}

// SQL:
// DELETE FROM StudentCourses WHERE StudentId = 1 AND CourseId = 1;

显式联接表配置

为什么需要显式联接表?

虽然隐式联接表很方便,但以下场景需要显式定义:

✅ 联接表有额外字段(如 enrolment date, grade)
✅ 需要直接查询联接表
✅ 需要对联接表应用过滤器
✅ 复杂的业务逻辑

完整示例: 带成绩的学生选课系统

csharp

// 显式定义联接表实体
public class Enrollment
{
    public int StudentId { get; set; }
    public int CourseId { get; set; }
    
    // 额外字段
    public DateTime EnrollmentDate { get; set; }
    public decimal? Grade { get; set; }
    public string Status { get; set; }  // Active, Dropped, Completed
    
    // 导航属性
    public Student Student { get; set; }
    public Course Course { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    // 通过联接表访问课程
    public ICollection<Enrollment> Enrollments { get; set; } = new List<Enrollment>();
    
    // 便捷属性: 直接获取课程
    public ICollection<Course> Courses 
        => Enrollments.Select(e => e.Course).ToList();
}

public class Course
{
    public int Id { get; set; }
    public string Title { get; set; }
    public int Credits { get; set; }
    
    public ICollection<Enrollment> Enrollments { get; set; } = new List<Enrollment>();
    
    public ICollection<Student> Students 
        => Enrollments.Select(e => e.Student).ToList();
}

// 配置
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 配置联接表
    modelBuilder.Entity<Enrollment>()
        .HasKey(e => new { e.StudentId, e.CourseId });  // 复合主键
    
    modelBuilder.Entity<Enrollment>()
        .HasOne(e => e.Student)
        .WithMany(s => s.Enrollments)
        .HasForeignKey(e => e.StudentId);
    
    modelBuilder.Entity<Enrollment>()
        .HasOne(e => e.Course)
        .WithMany(c => c.Enrollments)
        .HasForeignKey(e => e.CourseId);
}

CRUD 操作(显式联接表)

创建关系

csharp

// 选课并记录成绩
var enrollment = new Enrollment
{
    StudentId = 1,
    CourseId = 1,
    EnrollmentDate = DateTime.UtcNow,
    Status = "Active"
};

context.Enrollments.Add(enrollment);
await context.SaveChangesAsync();

// 或者通过导航属性
var student = await context.Students.FindAsync(1);
student.Enrollments.Add(new Enrollment
{
    CourseId = 2,
    EnrollmentDate = DateTime.UtcNow,
    Status = "Active"
});

await context.SaveChangesAsync();

查询关系

csharp

// 查询学生的所有课程及成绩
var enrollments = await context.Enrollments
    .Include(e => e.Student)
    .Include(e => e.Course)
    .Where(e => e.StudentId == 1)
    .ToListAsync();

foreach (var enrollment in enrollments)
{
    Console.WriteLine($"{enrollment.Student.Name} - {enrollment.Course.Title}: {enrollment.Grade}");
}

// 查询某课程的所有学生
var students = await context.Enrollments
    .Include(e => e.Student)
    .Include(e => e.Course)
    .Where(e => e.CourseId == 1 && e.Status == "Active")
    .Select(e => e.Student)
    .ToListAsync();

更新关系

csharp

// 更新成绩
var enrollment = await context.Enrollments
    .FirstOrDefaultAsync(e => e.StudentId == 1 && e.CourseId == 1);

if (enrollment != null)
{
    enrollment.Grade = 95.5m;
    enrollment.Status = "Completed";
    await context.SaveChangesAsync();
}

删除关系

csharp

// 退课
var enrollment = await context.Enrollments
    .FirstOrDefaultAsync(e => e.StudentId == 1 && e.CourseId == 1);

if (enrollment != null)
{
    context.Enrollments.Remove(enrollment);
    await context.SaveChangesAsync();
}

高级查询与操作

复杂查询

1. 过滤查询

csharp

// 查询学生已完成的课程
var completedCourses = await context.Students
    .Where(s => s.Id == 1)
    .SelectMany(s => s.Enrollments
        .Where(e => e.Status == "Completed")
        .Select(e => e.Course))
    .ToListAsync();

// 查询 GPA > 3.0 的学生
var highGpaStudents = await context.Enrollments
    .GroupBy(e => e.StudentId)
    .Where(g => g.Average(e => e.Grade) > 80)
    .Select(g => g.Key)
    .ToListAsync();

2. 统计查询

csharp

// 每个学生的选课数
var courseCounts = await context.Students
    .Select(s => new 
    {
        s.Name,
        CourseCount = s.Enrollments.Count()
    })
    .ToListAsync();

// 每门课的选课人数
var enrollmentStats = await context.Courses
    .Select(c => new 
    {
        c.Title,
        StudentCount = c.Enrollments.Count(e => e.Status == "Active"),
        AverageGrade = c.Enrollments.Average(e => e.Grade)
    })
    .ToListAsync();

3. 分页查询

csharp

// 查询学生的课程(分页)
var studentId = 1;
var pageNumber = 1;
var pageSize = 10;

var courses = await context.Enrollments
    .Where(e => e.StudentId == studentId)
    .OrderByDescending(e => e.EnrollmentDate)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .Include(e => e.Course)
    .Select(e => e.Course)
    .ToListAsync();

var totalCount = await context.Enrollments
    .CountAsync(e => e.StudentId == studentId);

4. 批量操作

csharp

// 批量更新成绩(EF Core 7+)
await context.Enrollments
    .Where(e => e.CourseId == 1 && e.Status == "Completed")
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(e => e.Grade, e => e.Grade * 1.1m));  // 加分 10%

// 批量退课
await context.Enrollments
    .Where(e => e.StudentId == 1 && e.Status == "Active")
    .ExecuteDeleteAsync();

性能优化

1. 避免 N+1 查询

csharp

// ❌ 错误: N+1 查询
var students = await context.Students.ToListAsync();
foreach (var student in students)
{
    var courses = await context.Courses  // 💥 每次循环都查询
        .Where(c => c.Students.Any(s => s.Id == student.Id))
        .ToListAsync();
}

// ✅ 正确: Include 预加载
var students = await context.Students
    .Include(s => s.Enrollments)
        .ThenInclude(e => e.Course)
    .ToListAsync();

2. 使用投影查询

csharp

// ✅✅ 最佳: 投影查询
var studentCourses = await context.Students
    .Select(s => new 
    {
        s.Name,
        Courses = s.Enrollments.Select(e => new 
        {
            e.Course.Title,
            e.Grade,
            e.Status
        }).ToList()
    })
    .ToListAsync();

3. 为外键创建索引

csharp

modelBuilder.Entity<Enrollment>()
    .HasIndex(e => e.StudentId);

modelBuilder.Entity<Enrollment>()
    .HasIndex(e => e.CourseId);

// 提升 JOIN 查询性能

.NET 8/9/10 新特性

.NET 8: 改进的多对多性能

csharp

// .NET 8 优化了多对多查询的 SQL 生成
// 对于隐式联接表,查询性能提升 20-30%

var students = await context.Students
    .Include(s => s.Courses)
    .ToListAsync();

// .NET 8 生成更优化的 JOIN 语句

.NET 9: 增强的联接表配置

csharp

// .NET 9 支持更灵活的联接表配置
modelBuilder.Entity<Student>()
    .HasMany(s => s.Courses)
    .WithMany(c => c.Students)
    .UsingEntity(j => 
    {
        j.ToTable("Enrollments");
        j.Property<DateTime>("CreatedAt").HasDefaultValueSql("GETUTCDATE()");
        j.HasIndex(new[] { "StudentId" }).IsDescending();
    });

.NET 10: 智能多对多优化(路线图)

预计特性:

自动检测多对多性能问题
基于访问模式的加载策略优化
多对多关系可视化分析工具

最佳实践与陷阱

最佳实践

1. 选择合适的实现方式

隐式联接表 vs 显式联接表:

选择隐式如果:
✓ 联接表只有外键,无额外字段
✓ 简单的多对多关系
✓ 快速原型开发

选择显式如果:
✓ 联接表有额外字段(时间、状态、分数等)
✓ 需要直接查询联接表
✓ 复杂的业务逻辑
✓ 需要审计跟踪

2. 初始化集合属性

csharp

// ✅ 推荐: 始终初始化集合
public class Student
{
    public ICollection<Course> Courses { get; set; } = new List<Course>();
}

// ❌ 避免: null 集合导致异常
public class Student
{
    public ICollection<Course> Courses { get; set; }  // 可能是 null
}

3. 防止重复关系

csharp

// ✅ 推荐: 检查是否已存在
public async Task EnrollStudentAsync(int studentId, int courseId)
{
    var exists = await context.Enrollments
        .AnyAsync(e => e.StudentId == studentId && e.CourseId == courseId);
    
    if (exists)
        throw new InvalidOperationException("Student already enrolled");
    
    context.Enrollments.Add(new Enrollment 
    { 
        StudentId = studentId, 
        CourseId = courseId,
        EnrollmentDate = DateTime.UtcNow 
    });
    
    await context.SaveChangesAsync();
}

4. 使用事务保证一致性

csharp

using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // 多个相关操作
    context.Enrollments.Add(enrollment1);
    context.Enrollments.Add(enrollment2);
    await context.SaveChangesAsync();
    
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

常见陷阱

陷阱 1: 循环引用序列化

csharp

// ❌ 错误: JSON 序列化循环引用
var student = await context.Students
    .Include(s => s.Courses)
    .FirstOrDefaultAsync(s => s.Id == 1);

return Results.Json(student);  
// 💥 Student → Courses → Students → ...

// ✅ 解决: 使用 DTO
var dto = new StudentDto
{
    Id = student.Id,
    Name = student.Name,
    Courses = student.Courses.Select(c => new CourseDto 
    { 
        Id = c.Id, 
        Title = c.Title 
    }).ToList()
};
return Results.Json(dto);

陷阱 2: 忘记 Include

csharp

// ❌ 错误: 访问未加载的集合
var student = await context.Students.FindAsync(1);
var count = student.Courses.Count;  // 💥 总是 0(未加载)

// ✅ 正确: Include 加载
var student = await context.Students
    .Include(s => s.Courses)
    .FirstOrDefaultAsync(s => s.Id == 1);

陷阱 3: 低效的查询

csharp

// ❌ 错误: 多次数据库往返
var student = await context.Students.FindAsync(1);
var courses = await context.Courses
    .Where(c => c.Students.Any(s => s.Id == 1))
    .ToListAsync();

// ✅ 正确: 单次查询
var student = await context.Students
    .Include(s => s.Courses)
    .FirstOrDefaultAsync(s => s.Id == 1);

陷阱 4: 级联删除冲突

csharp

// ❌ 错误: 双向 Cascade 导致冲突
modelBuilder.Entity<Enrollment>()
    .HasOne(e => e.Student)
    .WithMany(s => s.Enrollments)
    .HasForeignKey(e => e.StudentId)
    .OnDelete(DeleteBehavior.Cascade);  // 💥 可能与另一端冲突

modelBuilder.Entity<Enrollment>()
    .HasOne(e => e.Course)
    .WithMany(c => c.Enrollments)
    .HasForeignKey(e => e.CourseId)
    .OnDelete(DeleteBehavior.Cascade);  // 💥 循环级联!

// ✅ 解决: 一端 Cascade,另一端 Restrict
modelBuilder.Entity<Enrollment>()
    .HasOne(e => e.Student)
    .WithMany(s => s.Enrollments)
    .HasForeignKey(e => e.StudentId)
    .OnDelete(DeleteBehavior.Cascade);

modelBuilder.Entity<Enrollment>()
    .HasOne(e => e.Course)
    .WithMany(c => c.Enrollments)
    .HasForeignKey(e => e.CourseId)
    .OnDelete(DeleteBehavior.Restrict);

总结

核心要点

隐式联接表: .NET 5+ 简化语法,适合简单场景
显式联接表: 支持额外字段,灵活可控
复合主键: 联接表通常使用复合主键
性能优化: Include 预加载,避免 N+1
防止重复: 添加唯一约束或业务层检查

实现方式对比

特性	隐式联接表	显式联接表
配置复杂度	简单	中等
额外字段	❌ 不支持	✅ 支持
直接查询	❌ 困难	✅ 容易
灵活性	低	高
适用场景	简单关联	复杂业务

代码模板

csharp

// 模板 1: 隐式联接表(.NET 5+)
modelBuilder.Entity<EntityA>()
    .HasMany(a => a.EntitiesB)
    .WithMany(b => b.EntitiesA)
    .UsingEntity(j => j.ToTable("JoinTableName"));

// 模板 2: 显式联接表
public class JoinEntity
{
    public int EntityAId { get; set; }
    public int EntityBId { get; set; }
    // 额外字段...
    public EntityA EntityA { get; set; }
    public EntityB EntityB { get; set; }
}

modelBuilder.Entity<JoinEntity>()
    .HasKey(j => new { j.EntityAId, j.EntityBId });

下一步

📖 阅读继承映射策略
🔧 学习 Owned Entities
🚀 了解值转换

多对多关系配置 Many-to-Many ​

目录 ​

多对多关系基础 ​

概念理解 ​

数据库实现 ​

传统方式: 需要联接表(Junction Table) ​

.NET 5+ 隐式联接表 ​

自动管理联接表 ​

自定义联接表名称 ​

自定义联接表列名 ​

基本 CRUD 操作 ​

创建关系 ​

查询关系 ​

删除关系 ​

显式联接表配置 ​

为什么需要显式联接表? ​

完整示例: 带成绩的学生选课系统 ​

CRUD 操作(显式联接表) ​

创建关系 ​

查询关系 ​

更新关系 ​

删除关系 ​

高级查询与操作 ​

复杂查询 ​

1. 过滤查询 ​

2. 统计查询 ​

3. 分页查询 ​

4. 批量操作 ​

性能优化 ​

1. 避免 N+1 查询 ​

2. 使用投影查询 ​

3. 为外键创建索引 ​

.NET 8/9/10 新特性 ​

.NET 8: 改进的多对多性能 ​

.NET 9: 增强的联接表配置 ​

.NET 10: 智能多对多优化(路线图) ​

最佳实践与陷阱 ​

最佳实践 ​

1. 选择合适的实现方式 ​

2. 初始化集合属性 ​

3. 防止重复关系 ​

4. 使用事务保证一致性 ​

常见陷阱 ​

陷阱 1: 循环引用序列化 ​

陷阱 2: 忘记 Include ​

陷阱 3: 低效的查询 ​

陷阱 4: 级联删除冲突 ​

总结 ​

核心要点 ​

实现方式对比 ​

代码模板 ​

下一步 ​

多对多关系配置 Many-to-Many

目录

多对多关系基础

概念理解

数据库实现

传统方式: 需要联接表(Junction Table)

.NET 5+ 隐式联接表

自动管理联接表

自定义联接表名称

自定义联接表列名

基本 CRUD 操作

创建关系

查询关系

删除关系

显式联接表配置

为什么需要显式联接表?

完整示例: 带成绩的学生选课系统

CRUD 操作(显式联接表)

创建关系

查询关系

更新关系

删除关系

高级查询与操作

复杂查询

1. 过滤查询

2. 统计查询

3. 分页查询

4. 批量操作

性能优化

1. 避免 N+1 查询

2. 使用投影查询

3. 为外键创建索引

.NET 8/9/10 新特性

.NET 8: 改进的多对多性能

.NET 9: 增强的联接表配置

.NET 10: 智能多对多优化(路线图)

最佳实践与陷阱

最佳实践

1. 选择合适的实现方式

2. 初始化集合属性

3. 防止重复关系

4. 使用事务保证一致性

常见陷阱

陷阱 1: 循环引用序列化

陷阱 2: 忘记 Include

陷阱 3: 低效的查询

陷阱 4: 级联删除冲突

总结

核心要点

实现方式对比

代码模板

下一步