EF Core 7中Employee多外键关联CodeMaster插入报错排查

问题场景 #

我拥有CodeMaster和Employee两个实体，Employee中包含多个可选外键属性用于关联CodeMaster实体。向Employee插入数据时出现外键约束冲突错误。

错误信息 #

Inner Exception 1:
SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Employees_CodeMasters_BloodGroupId". The conflict occurred in database "AproPayrollDB", table "dbo.CodeMasters", column 'Id'

实体类代码 #

CodeMaster类 #

public class CodeMaster : AuditableEntity
{
    public long Id { get; set; }
    public long CodeTypeId { get; set; }

    public string Value { get; set; }

    public bool IsActive { get; set; }

    public string Remarks { get; set; }

    public virtual CodeType CodeType { get; set; }   
    public ICollection<Employee> Employees { get; set; }
}

Employee类 #

public class Employee : AuditableEntity
{
    public long Id { get; set; }

    public string EmployeeFullName { get; set; }
    public string WorkName { get; set; }
    public string FatherName { get; set; }
    public string MotherName { get; set; }

    public string EmployeeId { get; set; } = null!;
    public long? EmpTypeId { get; set; }

    public long? DepartmentId { get; set; }
    public long? DesignationId { get; set; }

    public DateTime? Dob { get; set; }
    public int? Age { get; set; }

    public long? BloodGroupId { get; set; }

    public string AprosoftEmailId { get; set; }
    public string PersonalEmailId { get; set; }
    public string AprosoftSkypeId { get; set; }

    public string MsteamsId { get; set; }
    public string ContactNumber { get; set; }
    public string EmergencyContactPerson { get; set; }
    public long? RelationshipId { get; set; }
    public string EmergencyNumber { get; set; }
    public long? LocationOfRemoteWorkStatus { get; set; }
    public long? UpazilaId { get; set; }
    public long? DistrictId { get; set; }
    public long? DivisionId { get; set; }

    public DateTime? EnrollmentDate { get; set; }
    public DateTime? RetirementDate { get; set; }

    public long? EmpStatusId { get; set; }
    public string Remarks { get; set; }

    [ForeignKey(nameof(DepartmentId))]
    public virtual CodeMaster Department { get; set; }

    [ForeignKey(nameof(BloodGroupId))]
    public virtual CodeMaster BloodGroup { get; set; }

    [ForeignKey(nameof(DistrictId))]
    public virtual CodeMaster District { get; set; }
}

Fluent API配置代码 #

builder.Entity<Employee>()
              .HasOne(e => e.Department)
              .WithMany()
              .HasForeignKey(e => e.DepartmentId);

builder.Entity<Employee>()
            .HasOne(e => e.District)
            .WithMany()
            .HasForeignKey(e => e.DistrictId);

builder.Entity<Employee>()
            .HasOne(e => e.BloodGroup)
            .WithMany()
            .HasForeignKey(e => e.BloodGroupId);

问题分析与解决 #

1. 插入的BloodGroupId无效 #

检查插入的Employee数据中BloodGroupId的值：

• 若该字段不需要赋值，确保其值为null，不要传入非null的无效Id；
• 若需要赋值，确认该Id已存在于CodeMaster表的Id列中。

2. CodeMaster分类记录缺失 #

CodeMaster通过CodeTypeId区分不同类型（如血型、部门、地区），需确保：

• 已在CodeMaster中创建了血型对应的CodeType记录；
• 插入的BloodGroupId属于该CodeType分类下的有效Id。

3. Fluent API配置优化 #

当前配置未指定CodeMaster端的导航属性，且未显式声明外键可选，可能导致EF Core关联逻辑异常。修改配置如下：

// Department关联配置
builder.Entity<Employee>()
      .HasOne(e => e.Department)
      .WithMany(cm => cm.Employees)
      .HasForeignKey(e => e.DepartmentId)
      .IsRequired(false);

// District关联配置
builder.Entity<Employee>()
      .HasOne(e => e.District)
      .WithMany(cm => cm.Employees)
      .HasForeignKey(e => e.DistrictId)
      .IsRequired(false);

// BloodGroup关联配置
builder.Entity<Employee>()
      .HasOne(e => e.BloodGroup)
      .WithMany(cm => cm.Employees)
      .HasForeignKey(e => e.BloodGroupId)
      .IsRequired(false);

• WithMany(cm => cm.Employees)显式指定CodeMaster端的导航集合，确保EF Core正确识别关联关系；
• IsRequired(false)明确声明外键为可选，避免EF Core默认将可空字段推断为必填约束。

4. 数据库架构不一致 #

检查数据库中Employees表的BloodGroupId字段是否为可空类型：

• 若数据库中字段被设置为NOT NULL，即使实体类中是可空类型，也会导致插入失败；
• 若存在架构不一致，重新生成迁移脚本并更新数据库。

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