EF添加Employee时触发Projects主键约束冲突，如何解决？

实体模型代码 #

public class Project
{
    public Guid ProjectId { get; set; }
    public string ProjectName { get; set; }
    public string ProjectInfo { get; set; }
    public Guid DepartMentId { get; set; }

    public ICollection<Employee> Employees { get; set; } = new List<Employee>();

    public Department Department { get; set; }
}

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PayrollNumber { get; set; }
    public int Seniority { get; set; }
    [Column(TypeName = "decimal(10,2)")]
    public decimal Salary { get; set; }
    public int Rank { get; set; }
    public int Hours { get; set; }
    public ICollection<Project> Project { get; set; }
}

public class Department
{
    public Guid DepartmentId { get; set; }
    public string DepartmentName { get; set; }
    public string DepartmentInfo { get; set; }
    public int Rank { get; set; }
    public List<Project> Projects { get; set; } = new List<Project>();
}

添加员工的代码 #

public void AddEmployee(string fname, string lname, string payNum, int seniority, decimal salary, int hours, string projname)
{
    var proj = _projectController.GetProjects().Where(x => x.ProjectName == projname).SingleOrDefault();
    var projects = new List<Project>();
    projects.Add(proj);

    var emp = new Employee { EmployeeId = Guid.NewGuid(), FirstName = fname, LastName = lname, PayrollNumber = payNum, Seniority = seniority, Salary = salary, Hours = hours, Project = projects };

    _employeeController.AddEmployee(emp);
}

触发的异常信息 #

SqlException: Violation of PRIMARY KEY constraint 'PK_Projects'. Cannot insert duplicate key in object 'dbo.Projects'. The duplicate key value is (10bc054b-b9a7-47da-eb14-08dac1b7a7fe).
The statement has been terminated.

解决方案 #

问题根源 #

你从_projectController.GetProjects()获取的Project对象，处于当前DbContext未跟踪的状态。当你把这个未被跟踪的实体关联到新创建的Employee并执行SaveChanges()时，EF的变更追踪系统会误以为这是一个全新的Project实体，尝试向数据库插入一条主键重复的记录，从而触发主键约束异常。

具体解决方法 #

方法一：关联已存在实体的主键并附加到上下文

不需要传递完整的Project对象，只需要获取其主键，创建一个仅包含主键的Project实例，再附加到当前DbContext标记为已存在状态：

public void AddEmployee(string fname, string lname, string payNum, int seniority, decimal salary, int hours, string projname)
{
    // 仅获取项目主键
    var projId = _projectController.GetProjects()
                                   .Where(x => x.ProjectName == projname)
                                   .Select(x => x.ProjectId)
                                   .SingleOrDefault();

    if (projId == Guid.Empty)
    {
        // 处理项目不存在的异常情况
        return;
    }

    // 创建仅含主键的Project实例
    var proj = new Project { ProjectId = projId };
    // 附加到上下文，标记为未修改状态
    _dbContext.Attach(proj);

    var emp = new Employee 
    { 
        EmployeeId = Guid.NewGuid(), 
        FirstName = fname, 
        LastName = lname, 
        PayrollNumber = payNum, 
        Seniority = seniority, 
        Salary = salary, 
        Hours = hours, 
        Project = new List<Project> { proj } 
    };

    _employeeController.AddEmployee(emp);
}

方法二：确保获取的Project处于当前上下文跟踪状态

如果_projectController.GetProjects()返回的是未跟踪实体（比如使用了AsNoTracking()），可以直接在当前操作的DbContext中重新查询项目，确保实体被上下文跟踪：

public void AddEmployee(string fname, string lname, string payNum, int seniority, decimal salary, int hours, string projname)
{
    // 直接从当前DbContext查询项目，确保实体被跟踪
    var proj = _dbContext.Projects
                         .Where(x => x.ProjectName == projname)
                         .SingleOrDefault();

    if (proj == null)
    {
        // 处理项目不存在的情况
        return;
    }

    var emp = new Employee 
    { 
        EmployeeId = Guid.NewGuid(), 
        FirstName = fname, 
        LastName = lname, 
        PayrollNumber = payNum, 
        Seniority = seniority, 
        Salary = salary, 
        Hours = hours, 
        Project = new List<Project> { proj } 
    };

    _employeeController.AddEmployee(emp);
}

方法三：直接操作多对多中间表（可选）

如果EF自动生成了员工与项目的多对多中间表，你可以直接向中间表插入关联记录，无需通过实体导航属性：

// 假设中间表DbSet名为EmployeeProject
public void AddEmployee(string fname, string lname, string payNum, int seniority, decimal salary, int hours, string projname)
{
    var projId = _projectController.GetProjects()
                                   .Where(x => x.ProjectName == projname)
                                   .Select(x => x.ProjectId)
                                   .SingleOrDefault();

    if (projId == Guid.Empty)
    {
        return;
    }

    var emp = new Employee 
    { 
        EmployeeId = Guid.NewGuid(), 
        FirstName = fname, 
        LastName = lname, 
        PayrollNumber = payNum, 
        Seniority = seniority, 
        Salary = salary, 
        Hours = hours 
    };

    _employeeController.AddEmployee(emp);
    // 插入中间表记录
    _dbContext.EmployeeProject.Add(new EmployeeProject 
    { 
        EmployeeId = emp.EmployeeId, 
        ProjectId = projId 
    });
    _dbContext.SaveChanges();
}

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