SqlException: On Delete Cascade not working ASP.NET

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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:

SqlException: On Delete Cascade not working ASP.NET

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;

Method 2

Can you try setting it the other way around;

builder.Entity<User>()
    .HasMany<Application>(u => u.Applications)
    .WillCascadeOnDelete();

or use .WillCascadeOnDelete() on your code.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x