SqlException: DELETE语句与引用约束冲突，似乎无法使级联删除起作用

Question

我得到了这个错误：

SqlException: DELETE语句与引用约束"FK_Games_AspNetUsers_VideoGamesUserId“冲突。冲突发生在数据库"VideoGames“、表"dbo.Games”、列“VideoGamesUserId”中。该语句已终止。

当尝试从我的数据库中删除VideoGamesUser实例时。

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

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

    public VideoGamesUser()
    {

    }
}

这也是Game类

 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()
    {
       

    }

}

我已经搜索了相关的问题，我相信这个问题是因为游戏表

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])

是通过外键引用VideoGamesUser类的，因此如果不先删除VideoGamesUser表的内容，我就不能删除它。但据我所知，级联删除应该使我能够同时删除这两个。

所以我的问题是即使在遵循了这个

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

从我的dbcontext类中得到的OnModelCreating方法：

    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);

    }
}

我仍然收到这个错误。我想我只是不理解这里的一些东西。任何想法或方向都将不胜感激。谢谢。

编辑：

delete方法是我在ASP.Net标识中搭建时提供的内置方法

 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

您可以尝试使用

    .OnDelete(DeleteBehavior.ClientSetNull)

但它也不可靠。让一切都在控制之下总是更好的：

修复你的游戏类

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; }
    
    .......

}

还有行动

....


 

 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);

.....