C# Entity Framework中两表级联循环依赖导致数据库更新失败

问题背景 #

我正在开发一个项目，包含Users和Requests两张表。
场景：用户可向其他用户发送多条请求，其他用户可响应特定请求，一次请求周期在请求表中仅创建一条记录。

模型类定义 #

public class User
{
    [Key]
    public Guid UserID { get; set; }

    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;

    public ICollection<Guid> FamilyId { get; set; } = new List<Guid>();
    public ICollection<UserRoles> Roles { get; set; } = new List<UserRoles>();
    public ICollection<Request> SentRequests { get; set; } = new List<Request>();
    public ICollection<Request> ReceivedRequests { get; set; } = new List<Request>();
}

public class Request
{
   [Key]
   public Guid RequestId { get; set; }  
   
   [Required]
   public Guid SenderId { get; set; }
   public User Sender { get; set; }

   // 接收请求用户的外键
   [Required]
   public Guid ReceiverId { get; set; }
   public User Receiver { get; set; }

   [Required]
   public string Message { get; set; } = "Please add into my family.";

   [Required]
   public string Status { get; set; } = "Pending"; // 状态：Pending（待处理）、Approved（已同意）、Rejected（已拒绝）

   // 更新请求状态的方法
   public void UpdateStatus(string newStatus)
   {
      Status = newStatus;
      UpdatedAt = DateTime.UtcNow;
   }
}

DbContext配置 #

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 配置关系、键等
    modelBuilder.Entity<Request>()
           .HasOne(r => r.Sender)
           .WithMany(u => u.SentRequests)
           .HasForeignKey(r => r.SenderId)
           .OnDelete(DeleteBehavior.Restrict);

    modelBuilder.Entity<Request>()
           .HasOne(r => r.Receiver)
           .WithMany(u => u.ReceivedRequests)
           .HasForeignKey(r => r.ReceiverId)
           .OnDelete(DeleteBehavior.Restrict);
     
    base.OnModelCreating(modelBuilder);
}

迁移代码 #

Up方法 #

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropForeignKey(
        name: "FK_Requests_Users_ReceiverId",
        table: "Requests");

    migrationBuilder.DropForeignKey(
        name: "FK_Requests_Users_SenderId",
        table: "Requests");

    migrationBuilder.AddForeignKey(
        name: "FK_Requests_Users_ReceiverId",
        table: "Requests",
        column: "ReceiverId",
        principalTable: "Users",
        principalColumn: "UserID");

    migrationBuilder.AddForeignKey(
        name: "FK_Requests_Users_SenderId",
        table: "Requests",
        column: "SenderId",
        principalTable: "Users",
        principalColumn: "UserID");
}

Down方法 #

/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropForeignKey(
       name: "FK_Requests_Users_ReceiverId",
       table: "Requests");

    migrationBuilder.DropForeignKey(
       name: "FK_Requests_Users_SenderId",
       table: "Requests");

    migrationBuilder.AddForeignKey(
       name: "FK_Requests_Users_ReceiverId",
       table: "Requests",
       column: "ReceiverId",
       principalTable: "Users",
       principalColumn: "UserID",
       onDelete: ReferentialAction.Restrict);

    migrationBuilder.AddForeignKey(
       name: "FK_Requests_Users_SenderId",
       table: "Requests",
       column: "SenderId",
       principalTable: "Users",
       principalColumn: "UserID",
       onDelete: ReferentialAction.Restrict);
 }

遇到的错误 #

Failed executing DbCommand (11ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

CREATE TABLE [Requests] 
(
     [RequestId] uniqueidentifier NOT NULL,
     [SenderId] uniqueidentifier NOT NULL,
     [ReceiverId] uniqueidentifier NOT NULL,
     [Message] nvarchar(max) NOT NULL,
     [Status] nvarchar(max) NOT NULL,
     [CreatedAt] datetime2 NOT NULL,
     [UpdatedAt] datetime2 NULL,

     CONSTRAINT [PK_Requests] PRIMARY KEY ([RequestId]),
     CONSTRAINT [FK_Requests_Users_ReceiverId] 
         FOREIGN KEY ([ReceiverId]) REFERENCES [Users] ([UserID]) 
                 ON DELETE CASCADE,
     CONSTRAINT [FK_Requests_Users_SenderId] 
         FOREIGN KEY ([SenderId]) REFERENCES [Users] ([UserID]) 
                 ON DELETE CASCADE
   );

Microsoft.Data.SqlClient.SqlException (0x80131904): 为表'Requests'引入外键约束'FK_Requests_Users_SenderId'可能会导致循环或多个级联路径。请指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他外键约束。

错误编号:1785,状态:0,级别:16
为表'Requests'引入外键约束'FK_Requests_Users_SenderId'可能会导致循环或多个级联路径。请指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他外键约束。
无法创建约束或索引。请查看先前的错误。

解决方案 #

问题核心是SQL Server不允许同一张表存在多条指向同一父表的级联删除路径。Requests表的SenderId和ReceiverId都关联到Users表，迁移代码的Up方法未显式指定删除行为，EF默认使用CASCADE，导致删除用户时会触发两条级联删除路径到Requests表，触发错误。

修复步骤 #

1. 修改迁移的Up方法，显式指定删除行为
   调整Up方法中的外键添加逻辑，明确指定onDelete: ReferentialAction.Restrict：

   protected override void Up(MigrationBuilder migrationBuilder)
   {
       migrationBuilder.DropForeignKey(
           name: "FK_Requests_Users_ReceiverId",
           table: "Requests");
   
       migrationBuilder.DropForeignKey(
           name: "FK_Requests_Users_SenderId",
           table: "Requests");
   
       migrationBuilder.AddForeignKey(
           name: "FK_Requests_Users_ReceiverId",
           table: "Requests",
           column: "ReceiverId",
           principalTable: "Users",
           principalColumn: "UserID",
           onDelete: ReferentialAction.Restrict);
   
       migrationBuilder.AddForeignKey(
           name: "FK_Requests_Users_SenderId",
           table: "Requests",
           column: "SenderId",
           principalTable: "Users",
           principalColumn: "UserID",
           onDelete: ReferentialAction.Restrict);
   }
   

2. 重新执行迁移
   修改完成后，直接执行更新命令：

   Update-Database
   

   若不想手动修改迁移代码，也可以删除当前迁移后重新生成：

   Remove-Migration
   Add-Migration AddRequestsTable
   Update-Database
   

3. 处理用户删除逻辑（可选）
   设置Restrict后，SQL Server会阻止删除仍有关联请求的用户。如果需要自动清理关联数据，可在应用层实现：删除用户前，先删除其所有发送和接收的请求记录。

内容的提问来源于stack exchange，提问作者Ashish-BeJovial