ASP.NET Core Code First添加同表双外键报错，已配置DeleteBehavior.Restrict

我正在开发一个ASP.NET Core项目，使用Code First模式修改数据库设计时遇到问题：需要给Projects表添加两个指向Languages表的外键，即使已经配置了DeleteBehavior.Restrict，执行数据库更新时仍然报错。

模型代码 #

public class Languages
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Projects> PFromLanguages { get; set; }
    public virtual ICollection<Projects> PToLanguages { get; set; }
}

public class Projects
{
    public int Id { get; set; }
    public int FromLanguageId { get; set; }
    public int ToLanguageId { get; set; }

    public virtual Languages FromLanguage { get; set; }
    public virtual Languages ToLanguage { get; set; }
}

DbContext配置 #

protected override void OnModelCreating(ModelBuilder builder)
{
    builder.Entity<Projects>()
        .HasOne(p => p.FromLanguage)
        .WithMany(b => b.PFromLanguages)
        .HasForeignKey(b => b.FromLanguageId)
        .OnDelete(DeleteBehavior.Restrict);

    builder.Entity<Projects>()
        .HasOne(p => p.ToLanguage)
        .WithMany(b => b.PToLanguages)
        .HasForeignKey(b => b.ToLanguageId)
        .OnDelete(DeleteBehavior.Restrict);
}

迁移代码 #

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddForeignKey(
        name: "FK_Projects_Languages_FromLanguageId",
        table: "Projects",
        column: "FromLanguageId",
        principalTable: "Languages",
        principalColumn: "Id",
        onDelete: ReferentialAction.Restrict);

    migrationBuilder.AddForeignKey(
        name: "FK_Projects_Languages_ToLanguageId",
        table: "Projects",
        column: "ToLanguageId",
        principalTable: "Languages",
        principalColumn: "Id",
        onDelete: ReferentialAction.Restrict);
}

报错信息 #

Failed executing DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE [Projects] ADD CONSTRAINT [FK_Projects_Languages_FromLanguageId] FOREIGN KEY ([FromLanguageId]) REFERENCES [Languages] ([Id]) ON DELETE NO ACTION;

解决方法 #

1. 检查现有数据一致性 #

如果Projects表中已有数据，需确认FromLanguageId和ToLanguageId的所有值都能在Languages表中找到对应的Id。若存在无效值，先删除或修正这些数据，再执行迁移。

2. 拆分迁移步骤 #

自动生成的迁移可能一次性添加字段和约束导致冲突，可拆分两步操作：

• 第一步：创建迁移仅添加FromLanguageId和ToLanguageId字段，执行Update-Database
• 第二步：再创建迁移添加外键约束，再次执行Update-Database

3. 适配数据库的删除行为配置 #

部分数据库对Restrict的处理和NoAction一致，可尝试将DeleteBehavior.Restrict改为DeleteBehavior.NoAction，重新生成迁移后执行。

4. 验证导航属性关联 #

确认Languages类中的两个集合属性PFromLanguages、PToLanguages与Projects类的FromLanguage、ToLanguage导航属性一一对应，配置中的WithMany参数必须准确关联到对应集合。

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