SqlException: The DELETE statement conflicted with the REFERENCE constraint, can't seem to get cascade delete to work

Question

I am getting this error:

SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Games_AspNetUsers_VideoGamesUserId". The conflict occurred in database "VideoGames", table "dbo.Games", column 'VideoGamesUserId'. The statement has been terminated.

when trying to remove an instance of VideoGamesUser from my database.

  public class VideoGamesUser : IdentityUser
{
    public List<Game> UserGameLibrary { get; set; }

    public VideoGamesUser(List<Game> games)
    {
        UserGameLibrary = games;
    }

    public VideoGamesUser()
    {

    }
}

And here is the Game class as well

 public class Game
{
    public long GameId { get; set; }
    [Required]
    public string Name { get; set; }
    public string Genre { get; set; }
    public bool Completed { get; set; }
    public VideoGamesUser VideoGamesUser { get; set; }
    

    public Game(string name, string genre, bool completed, int id)
    {
        GameId = id;
        Name = name;
        Genre = genre;
        Completed = completed;

    }

    public Game()
    {
       

    }

}

I've searched through related questions and I believe the issue is because the Games table

CREATE TABLE [dbo].[Games] (
[GameId]           BIGINT         IDENTITY (1, 1) NOT NULL,
[Name]             NVARCHAR (MAX) NOT NULL,
[Genre]            NVARCHAR (MAX) NULL,
[Completed]        BIT            NOT NULL,
[VideoGamesUserId] NVARCHAR (450) NULL,
CONSTRAINT [PK_Games] PRIMARY KEY CLUSTERED ([GameId] ASC),
CONSTRAINT [FK_Games_AspNetUsers_VideoGamesUserId] FOREIGN KEY ([VideoGamesUserId]) REFERENCES [dbo].[AspNetUsers] ([Id])

is referencing the VideoGamesUser class via a foreign key and thus I cannot delete the VideoGamesUser without first deleting the content of the Games table. But to my understanding, cascade delete should enable me to remove both at the same time.

So my issue is even after following this

https://www.tektutorialshub.com/entity-framework-core/cascade-delete-in-entity-framework-core/

the resulting OnModelCreating method from my dbcontext class:

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

        builder.Entity<Game>()
            .HasOne<VideoGamesUser>(u => u.VideoGamesUser)
            .WithMany(g => g.UserGameLibrary)
            .HasForeignKey("VideoGamesUserId")
            .IsRequired(true)
            .OnDelete(DeleteBehavior.Cascade);

    }
}

I am still getting the error. I guess I am just not understanding something here. Any ideas or direction would be appreciated. Thanks.

Edit:

The delete method is the built in one provided when I scaffolded in ASP.Net Identity

 public async Task<IActionResult> OnPostAsync()
    {
        var user = await _userManager.GetUserAsync(User);
        if (user == null)
        {
            return NotFound($"Unable to load user with ID '{_userManager.GetUserId(User)}'.");
        }

        RequirePassword = await _userManager.HasPasswordAsync(user);
        if (RequirePassword)
        {
            if (!await _userManager.CheckPasswordAsync(user, Input.Password))
            {
                ModelState.AddModelError(string.Empty, "Incorrect password.");
                return Page();
            }
        }
      
        var result = await _userManager.DeleteAsync(user);
        var userId = await _userManager.GetUserIdAsync(user);
        if (!result.Succeeded)
        {
            throw new InvalidOperationException($"Unexpected error occurred deleting user with ID '{userId}'.");
        }

        await _signInManager.SignOutAsync();

        _logger.LogInformation("User with ID '{UserId}' deleted themselves.", userId);

        return Redirect("~/");
    }

Answer 1

You can try to use

    .OnDelete(DeleteBehavior.ClientSetNull)

but it is not reliable too. It is always better to keep everything under control:

Fix your game class

public class Game
{
    [Key]
    public long GameId { get; set; }

    [Required]
    public string Name { get; set; }

    public string Genre { get; set; }
    public bool Completed { get; set; }
     public int VideoGamesUserId { get; set; }

    [ForeignKey(nameof(VideoGamesUserId ))]
    [InverseProperty("UserGameLibrary")]
    public VideoGamesUser VideoGamesUser { get; set; }

     .......

}


  public class VideoGamesUser : IdentityUser
{
   
    [InverseProperty(nameof(Game.VideoGamesUser ))]
    public List<Game> UserGameLibrary { get; set; }
    
    .......

}

and action too

....


 

 var videoGames =  await _context.Games.Where(i=> i.VideoGamesUserId == user.Id ) 
.ToListAsync();

if(videoGames!=null)  
{
  foreach(var game in videoGames)
  {
   game.VideoGamesUserId=null);
    _context.Entry(game).State = EntityState.Modified;
  }
   await _context.SaveChangesAsync();
}

var userId = await _userManager.GetUserIdAsync(user);

  var result = await _userManager.DeleteAsync(user);

.....