Database-update command is failing due to the cascading issue (cyclic dependency) between two tables in C# and Entity Framework

Question

I am working on a project, and I have two tables Users and Requests.

Scenario: a user can send multiple requests to the other users and other users can respond on that particular request.

I want that for completing one cycle of the request only one record should be created in the request table within the database.

By considering it, I have created Users and Requests model classes like this:

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; }

   // Foreign key for the user receiving the request
   [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, or Rejected

   // Method to update the request status
   public void UpdateStatus(string newStatus)
   {
      Status = newStatus;
      UpdatedAt = DateTime.UtcNow;
   }
}

MyAppDBContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Configure relationships, keys, etc.\
    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);
}

My migration Up and Down methods are shown here:

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");
}

  /// <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);
 }

This is the error in the Package Manager console that I get:

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): Introducing FOREIGN KEY constraint 'FK_Requests_Users_SenderId' on table 'Requests' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Error Number:1785,State:0,Class:16

Introducing FOREIGN KEY constraint **'FK_Requests_Users_SenderId' on table 'Requests' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Could not create constraint or index. See previous errors.

Answer 1

This issue can be solved by updating the following code. Replace "Restrict" with "ClientSetNull" in the MyAppDbContext.cs File.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   // Configure relationships, keys, etc.\
   modelBuilder.Entity<Request>()
       .HasOne(r => r.Sender)
       .WithMany(u => u.SentRequests)
       .HasForeignKey(r => r.SenderId)
       .OnDelete(DeleteBehavior.ClientSetNull);

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

After this build your code and re-run update-database command. If still fails then drop the database or create a new migration and update database.

It will work 100%. Thank you!