EF Core中Department与Subject多对多关系的表设计咨询

需求回顾 #

需要设计Department（部门）和Subject（科目）的多对多关系：一个科目可被一个或多个部门开设，一个部门可开设多个科目。

现有代码中的问题 #

你的代码思路方向正确，但存在拼写错误和逻辑冗余：

1. Subject类中多余的DepartmetnId（拼写错误）字段：多对多关系不需要在主实体中存储对方外键，否则会退化为一对多关系，违背需求
2. 类名/属性名拼写错误：SubmDepartmentsList、SubjecMapping、SubjectDeparmentMapping、subject（属性首字母未遵循C#大写规范）

修正后的完整实现 #

实体类定义

public class Department 
{ 
    public int Id { get; set; } 
    public string Name { get; set; } 
    // 导航属性：部门关联的所有科目关联记录
    public List<SubjectDepartment> SubjectDepartments { get; set; } = new List<SubjectDepartment>();
}

public class Subject 
{ 
    public int Id { get; set; } 
    public string Name { get; set; } 
    public int FacultyId { get; set; } 
    // 导航属性：科目关联的所有部门关联记录
    public List<SubjectDepartment> SubjectDepartments { get; set; } = new List<SubjectDepartment>();
}

// 多对多中间关联实体
public class SubjectDepartment
{
    // 外键：关联Department表
    public int DepartmentId { get; set; }
    public Department Department { get; set; }

    // 外键：关联Subject表
    public int SubjectId { get; set; }
    public Subject Subject { get; set; }
}

EF Core 映射配置

public class DepartmentMapping : IEntityTypeConfiguration<Department>
{
    public void Configure(EntityTypeBuilder<Department> builder) 
    { 
        builder.HasKey(x => x.Id);
        builder.Property(x => x.Name).IsRequired().HasMaxLength(100);
    }
}

public class SubjectMapping : IEntityTypeConfiguration<Subject>
{
    public void Configure(EntityTypeBuilder<Subject> builder) 
    { 
        builder.HasKey(x => x.Id);
        builder.Property(x => x.Name).IsRequired().HasMaxLength(100);
        builder.Property(x => x.FacultyId).IsRequired();
    }
}

public class SubjectDepartmentMapping : IEntityTypeConfiguration<SubjectDepartment>
{
    public void Configure(EntityTypeBuilder<SubjectDepartment> builder) 
    { 
        // 复合主键：确保同一部门-科目组合唯一
        builder.HasKey(x => new { x.DepartmentId, x.SubjectId });

        // 配置与Department的关联
        builder.HasOne(x => x.Department)
               .WithMany(x => x.SubjectDepartments)
               .HasForeignKey(x => x.DepartmentId)
               .OnDelete(DeleteBehavior.Cascade); // 可根据业务需求调整删除行为

        // 配置与Subject的关联
        builder.HasOne(x => x.Subject)
               .WithMany(x => x.SubjectDepartments)
               .HasForeignKey(x => x.SubjectId)
               .OnDelete(DeleteBehavior.Cascade);
    }
}

最终数据库表结构 #

生成的三张表结构如下：

• Departments表：

  • Id (INT, 主键, 自增)
  • Name (NVARCHAR(100), 非空)

• Subjects表：

  • Id (INT, 主键, 自增)
  • Name (NVARCHAR(100), 非空)
  • FacultyId (INT, 非空)

• SubjectDepartments表（中间关联表）：

  • DepartmentId (INT, 外键关联Departments.Id, 复合主键之一)
  • SubjectId (INT, 外键关联Subjects.Id, 复合主键之一)

关键说明 #

1. 中间表通过复合主键避免重复的部门-科目关联记录
2. 导航属性初始化= new List<SubjectDepartment>()可避免空引用异常
3. 删除行为（OnDelete）可根据业务调整：比如设置为DeleteBehavior.Restrict防止误删主表数据

内容的提问来源于stack exchange，提问作者Nasratllah safai