Error In create constraint or index on asp.net

Question

I have Three Table ApplicationUser, Posts, Comments

there is a one to many between users and Posts

there is a one to many between Posts and Comments

these Relations Has CasCade Delete and working Successfully

but when i'm trying to add a Relation Between one to many between Users And Comments for registering who has Creating This Comment , i want when deleting the user Also his comments being deleted

but it give me this error Introducing FOREIGN KEY constraint 'FK_AcademyPosts_AspNetUsers_AcademyId' on table 'AcademyPosts' 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.

this is my code

ApplicationUser Model Code

public virtual ICollection<Comments> Comments { get; set; }

Comments Model Code

public ApplicationUser User { get; set; }

public string UserId { get; set; } 

Configuration Code

builder
    .HasOne(user => user.User)
    .WithMany(comment => comment.Comments)
    .HasForeignKey(user => user.UserId)
    .OnDelete(DeleteBehavior.Cascade);

Answer 1

It is a cascade deleting issue when using EF Core, and it is the restriction of MS SQL Server itself.

Your one-to-many relationship of the three tables will enable cascade deleting of user – post – comment, meanwhile your new relationship of user/comment will also delete the comment when delete the comment, which is not allowed in the server.

Your configuration enables the cascade deleting between user and comments, and we’d better avoid it.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Comment>()
        .HasOne(e => e.User)
        .WithMany(e => e.Comments)
        .OnDelete(DeleteBehavior.ClientCascade);
}

Then you need to manually modify the deleting method. Load the comment to cascade delete.

// POST: Users/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public async Task<IActionResult> DeleteConfirmed(int id)
{
    if (_context.User == null)
    {
        return Problem("Entity set 'CascadeDeletingContext.User'  is null.");
    }
    var user = await _context.User.FindAsync(id);
    //Here is the loading
    var comments = await _context.Comment.Where(e => e.UserId == id).ToListAsync();
    if (user != null)
    {
        _context.User.Remove(user);
    }
    
    await _context.SaveChangesAsync();
    return RedirectToAction(nameof(Index));
}

Create a new app and database cause in my test the migration history will affect the database and the error always exists.

You can also refer to the official document, which has a sample that is exactly the same with your problem. https://learn.microsoft.com/en-us/ef/core/saving/cascade-delete