ASP.NET Core MVC+EF配置多对多关系时主键重复错误求助

配置Employee和Project多对多关系时，创建关联操作抛出以下错误：

SqlException: Violation of PRIMARY KEY constraint 'PK_Employee_Projects'. Cannot insert duplicate key into object 'dbo.Employee_Projects'. Duplicate key value: (1, 1).

相关代码 #

实体类代码

namespace WebApp2.Models
{
    public class Employee
    {
        [Key]
        public int Emp_Id { get; set; }
        public string Emp_Name { get; set; }
        public string Emp_Email { get; set; }
        public string Emp_Mobile { get; set; }

        public virtual ICollection<Employee_Project> Employee_Projects { get; set; }
    }

    public class Project
    {
        [Key]
        public int Proj_Id { get; set; }
        public string Proj_Name { get; set; }
        public string Project_Details { get; set; }
        public virtual ICollection<Employee_Project> Employee_Projects { get; set; }
    }

    public class Employee_Project
    {
        [Key]
        [Column(Order =1)]
        public int Emp_Id { get; set; }
        [Key]
        [Column(Order = 2)]
        public int Proj_Id { get; set; }

        public virtual Employee Employee { get; set; }
        public virtual Project Project { get; set; }
    }
}

DbContext代码

namespace WebApp2.Data
{
    public class MyDbContext:DbContext
    {
        public MyDbContext(DbContextOptions<MyDbContext> option):base(option)
        {
        }

        public DbSet<Employee> Employees { get; set; }
        public DbSet<Project> Projects { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Employee_Project>().HasKey(pt => new { pt.Proj_Id, pt.Emp_Id });

            modelBuilder.Entity<Employee_Project>()
                        .HasOne(pt => pt.Employee)
                        .WithMany(pt => pt.Employee_Projects)
                        .HasForeignKey(p => p.Emp_Id);

            modelBuilder.Entity<Employee_Project>()
                        .HasOne(pt => pt.Project)
                        .WithMany(pt => pt.Employee_Projects)
                        .HasForeignKey(p => p.Proj_Id);
        }

        public DbSet<Employee_Project> Employee_Projects { get; set; }
    }
}

Emp_ProjController关键代码

[HttpPost]
public IActionResult Create(int empid, int [] projIds)
{
    foreach (var item in projIds)
    {
        Employee_Project emp = new Employee_Project();
        emp.Emp_Id = empid;
        emp.Proj_Id = item;
        _DbContext.Employee_Projects.Add(emp);
        _DbContext.SaveChanges();
    }
    return RedirectToAction("Index");
}

问题原因 #

1. 未检查关联是否已存在：当前代码直接向中间表插入新记录，完全没有判断Emp_Id和Proj_Id的组合是否已存在，重复提交或重复选择同一员工-项目组合时，就会触发主键重复错误。
2. 主键顺序不一致：实体类Employee_Project中复合主键顺序是Emp_Id（Order=1）、Proj_Id（Order=2），但DbContext中配置的主键顺序是Proj_Id, Emp_Id，虽然不会直接导致重复插入，但会造成主键约束逻辑与实体定义不一致，容易引发混淆。

解决方案 #

1. 插入前检查关联是否存在 #

修改Emp_ProjController的Post方法，先查询数据库确认关联不存在后再插入，同时统一提交修改减少数据库交互：

[HttpPost]
public IActionResult Create(int empid, int [] projIds)
{
    foreach (var item in projIds)
    {
        // 检查当前员工-项目组合是否已存在
        var existingLink = _DbContext.Employee_Projects
            .FirstOrDefault(ep => ep.Emp_Id == empid && ep.Proj_Id == item);
        
        if (existingLink == null)
        {
            Employee_Project empProj = new Employee_Project();
            empProj.Emp_Id = empid;
            empProj.Proj_Id = item;
            _DbContext.Employee_Projects.Add(empProj);
        }
    }
    // 统一保存所有新增的关联
    _DbContext.SaveChanges();
    return RedirectToAction("Index");
}

2. 统一复合主键顺序 #

修改DbContext中的主键配置，与实体类的主键顺序保持一致：

modelBuilder.Entity<Employee_Project>().HasKey(pt => new { pt.Emp_Id, pt.Proj_Id });

3. 优化视图交互（可选） #

在Create视图的Get方法中，可预先查询员工已关联的项目，将已选中的项目设置为勾选状态，避免用户重复选择：

public IActionResult Create()
{
    ViewBag.emp = _DbContext.Employees.ToList();
    ViewBag.pro = _DbContext.Projects.ToList();
    // 若为编辑场景，可传入empId查询已关联项目并传递到视图
    return View();
}

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