c# - Cascade Delete doesn't work in Asp.net 5 and EF Core

Question

I'm trying to delete User from database, User has its own Recipes. Cascade delete doesn't work even though I configured it in model builder.

The error I'm getting is:

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Recipes_AspNetUsers_ApplicationUserId". The conflict occurred in database "master", table "dbo.Recipes", column 'ApplicationUserId'.

AplicationUser.cs :

namespace WebApplication.Models
{
    public class ApplicationUser : IdentityUser
    {
        public ICollection<Recipe> Recipes { get; set; }
    }
}

Recipe.cs

namespace WebApplication.Models
{
    public class Recipe
    {
        [Key]
        public Guid Id { get; set; }
        [Required]
        public string RecipeName { get; set; }
        [Required]
        public string RecipeContent { get; set; }
        public string ApplicationUserId { get; set; }
        public ApplicationUser ApplicationUser { get; set; }
    }
}

ApplicationDbContext.cs

namespace WebApplication.Models
{
    public class ApplicationDbContext : IdentityDbContext
    {
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<ApplicationUser>().HasMany(e => e.Recipes).WithOne(e => e.ApplicationUser)
                .OnDelete(DeleteBehavior.ClientCascade);


        }

        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {
        }

        public DbSet<Recipe> Recipes { get; set; }
        public DbSet<ApplicationUser> ApplicationUsers { get; set; }
    }
}

UserController.cs

public IActionResult Delete(string id)
{
    var user = _applicationDbContext.Users
                                    .Where(u => u.Id == id).FirstOrDefault();
    return View(user);
}
      
[HttpPost]
public IActionResult Delete(ApplicationUser appuser)
{
    var user = _applicationDbContext.Users
                                    .Where(u => u.Id == appuser.Id).FirstOrDefault();
    _applicationDbContext.Users.Remove(user);
    _applicationDbContext.SaveChanges();

    return RedirectToAction("Index");
}

I've been looking for solution to that for like an hour and nothing works.

Configuring modulbuilder seems to be solution for almost everyone but certainly doesn't work for me.

question from:https://stackoverflow.com/questions/65909283/cascade-delete-doesnt-work-in-asp-net-5-and-ef-core

Answer 1

ClientCascade will only delete related entities that are tracked by the DbContext, where-as Cascade will also set up a Delete Orphans on the FK Constraint in the database if EF is set up to manage the schema.

You will need to ensure hte DbContext is tracking the related entities for them to be deleted:

var user = _applicationDbContext.Users
    .Include(x => x.Recipies)
    .Where(u => u.Id == appuser.Id)
    .SingleOrDefault();

This ensures the DbContext is aware of the related entities and should delete those prior to the user being deleted. You also need to be aware of any other entity that might have a reference to a User record or FK. For instance if other records are tracking something like a CreatedBy then these will likely be set up to have FKs back to User. Relying on ClientCascade can be problematic here, and Cascade Deletes may not be desirable, opting for a Soft-Delete model instead. (I.e. IsActive flag on ApplicationUser rather than Deleting the row)

Also, avoid using the First methods unless you are expecting multiple rows and specifically want the first one. These methods should also only ever be used in combination with OrderBy methods. If you expect 1 row, use Single. If you expect 0 or 1 row, use SingleOrDefault. This helps ensure that any unexpected data state results in a meaningful exception rather than silently doing something unexpected.