sql server - SqlException: On Delete Cascade not working ASP.NET

Question

When I try to delete a user from the ASP.NETUsers table I get SqlException:

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Applications_AspNetUsers_UserID". The conflict occurred in database "JobGuide", table "dbo.Applications", column 'UserID'.

This problem is occurring because the User's Id is the Foreign key in another table, but "On delete cascade" is not working. For more details this is my model:

My extended Identity User:

public class AppUser : IdentityUser
{
    public string Name { get; set; }
    public string City { get; set; }
    public string RoleName { get; set; }

    public virtual ICollection<Application> Applications { get; set; }
}

Application model (i.e. when a user applies for a job):

public class Application
{
    public int ApplicationID { get; set; }

    public int JobID { get; set; }
    public virtual Job Job { get; set; }
    
    public string UserID { get; set; }
    public virtual AppUser User { get; set; }
}

Job model:

public class Job
{
    public int JobID { get; set; }
    public string Title { get; set; }

    public virtual ICollection<Application> Applications { get; set; }
}

So up to here I created two One to Many relationships, AspNetUser one to many with Application and Job one to many with Application.

And this is my Fluent API mapping configuration:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<Application>()
        .HasKey(i => i.ApplicationID);

    builder.Entity<Application>()
           .HasOne<AppUser>(sc => sc.User)
           .WithMany(s => s.Applications)
           .HasForeignKey(sc => sc.UserID)
           .OnDelete(DeleteBehavior.Cascade);

    builder.Entity<Application>()
           .HasOne<Job>(sc => sc.Job)
           .WithMany(s => s.Applications)
           .HasForeignKey(sc => sc.JobID)
           .OnDelete(DeleteBehavior.Cascade);
}

Delete method from controller:

    var userInfo = await userManager.FindByIdAsync(user.Id);
    if (userInfo == null) 
    {
        return NotFound();
    }
    _ = await userManager.RemoveFromRoleAsync(userInfo, userInfo.RoleName);
    _ = await userManager.DeleteAsync(userInfo);
    
    int rowsAffected = await db.SaveChangesAsync();

Any idea why this error is not disappearing, is Fluent API good? or i need to type raw Sql to delete the Application with that User once and then the User? I have looked at almost all similar questions but none of them are working for me.

question from:https://stackoverflow.com/questions/65911623/sqlexception-on-delete-cascade-not-working-asp-net

Answer 1

It seems that the cascade delete is not configured in the application table, try to use SSMS to check it:

Open the SQL Server Object Explorer (or using Server Explorer), find the SQL Server Database, then right click the Applications table -> Script As -> CREATE To -> New Query Window, then check whether the table is configured Cascade delete, check this screenshot:

To solve this issue, after configuration Cascade Delete using Fluent API mapping, please remember to enable migration and update the database:

  Add-Migration AddCascadeDelete
  Update-Database

Besides, you could also configure the Cascade Delete by executing the following SQL command (via SSMS):

ALTER TABLE [dbo].[Applications]
    ADD CONSTRAINT [FK_Applications_AspNetUsers_UserID] FOREIGN KEY ([UserID]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;