ASP.NET Core+EF Core中外键SET NULL操作报错如何解决

问题场景 #

使用ASP.NET Core 6.0、EF Core 7.0.11和SQL Server 2019进行代码优先开发时，创建多外键关联遇到如下报错：

Cannot create the foreign key "FK_GkTempGks_GkAdminUser_VerifiedBy" with the SET NULL referential action, because one or more referencing columns are not nullable.

相关实体代码 #

主实体GkAdminUser：

public class GkAdminUser
{    
    public int Id { get; set; }
    public string? UserName { get; set; }
    public string? UserPassword { get; set; }
    public string? FirstName { get; set; }
    public string? LastName { get; set; }
    public string? FullName => $"{FirstName} {LastName}";
    public string Email { get; set; } = null!;
    public bool St { get; set; }   
    public int RoleId { get; set; }

    public virtual GkRole? GkRole { get; set; }        

    public virtual ICollection<GkTempGks>? GkTempGkOperators { get; set; }
    public virtual ICollection<GkTempGks>? GkTempGkVerifiedBy { get; set; }
}

事务实体GkTempGks：

public class GkTempGks
{   
    public int Id { get; set; }
    public string Question { get; set; } = null!;
    public string Option1 { get; set; } = null!;
    public string Option2 { get; set; } = null!;
    public string Option3 { get; set; } = null!;
    public string Option4 { get; set; } = null!;
    public int Answer { get; set; }
    public string? Remarks { get; set; }    
    public int Complexity { get; set; }     
    public DateTime EntryDate { get; set; }
    public bool IsVerified { get; set; }
    public DateTime? VerificationDate { get; set; }
    public string? ContentReference { get; set; }
    public int OperatorId { get; set; }      
    public int? VerifiedBy { get; set; }       
    public int CategoryId { get; set; }

    public virtual GkCategory GkCategory { get; set; } = null!;
    public virtual GkAdminUser GkOperator { get; set; } = null!;    
    public virtual GkAdminUser? GkDataVerifier { get; set; } = null!;
}

解决方案 #

1. 修正导航属性的可空性配置 #

问题核心在GkTempGks的GkDataVerifier导航属性：

public virtual GkAdminUser? GkDataVerifier { get; set; } = null!;

这里用null!强制标记导航属性为非空，但对应的外键字段VerifiedBy是可空类型int?，EF Core会根据导航属性的非空推断外键不可空，导致和SET NULL的删除动作冲突。

修改为：

public virtual GkAdminUser? GkDataVerifier { get; set; }

去掉null!，明确告诉EF Core该导航属性允许为null，匹配外键字段VerifiedBy的可空性。

2. 检查并修正Fluent API外键配置（如果有） #

如果在DbContext的OnModelCreating中手动配置了该外键，确保删除行为和字段可空性匹配：

modelBuilder.Entity<GkTempGks>()
    .HasOne(t => t.GkDataVerifier)
    .WithMany(u => u.GkTempGkVerifiedBy)
    .HasForeignKey(t => t.VerifiedBy)
    .OnDelete(DeleteBehavior.SetNull); // 仅当VerifiedBy为可空类型时可用

3. 清理错误的迁移文件（若已生成） #

如果之前已经生成了迁移文件，打开文件检查VerifiedBy列的定义，确保设置为nullable: true：

migrationBuilder.AddColumn<int>(
    name: "VerifiedBy",
    table: "GkTempGks",
    type: "int",
    nullable: true); // 确认此处为true

修正后重新执行迁移更新数据库。

内容的提问来源于stack exchange，提问作者Shiv Shankar Maiti