EF多对多关系更新抛出重复键

Question

我正在使用.NET5，并使用EF Core和MSSQL作为数据库。

我有两个类，他们之间有很多对很多的关系。这些都是从模型迁移到数据库的。DB表示例

迁移：

migrationBuilder.CreateTable(
            name: "TournamentUser",
            columns: table => new
            {
                TournamentsId = table.Column<int>(type: "int", nullable: false),
                UsersId = table.Column<int>(type: "int", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_TournamentUser", x => new { x.TournamentsId, x.UsersId });
                table.ForeignKey(
                    name: "FK_TournamentUser_Tournaments_TournamentsId",
                    column: x => x.TournamentsId,
                    principalTable: "Tournaments",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
                table.ForeignKey(
                    name: "FK_TournamentUser_Users_UsersId",
                    column: x => x.UsersId,
                    principalTable: "Users",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

型号：

public class Tournament
{
    [Key]
    public int Id { get; set; }
    [Required, MaxLength(20)]
    public string Name { get; set; }
    [Required]
    public int MaxPlayers { get; set; }
    public int PlayersCount { get; set; }
    [Required]
    public DateTime Start { get; set; }
    public bool IsStarted { get; set; }
    public bool IsEnded { get; set; }
    public List<User> Users { get; set; }
}

public class User
{
    [Key]
    public int Id { get; set; }
    [Required, MaxLength(15)]
    public string Username { get; set; }
    [Required]
    public string Password { get; set; }
    [Required, MaxLength(20)]
    public string Name { get; set; }
    [Required, MaxLength(20)]
    public string Surname { get; set; }
    [JsonIgnore]
    public List<Tournament> Tournaments { get; set; }
}

所以当我试图更新比赛模型

[HttpPut]
    public async Task<ActionResult<Tournament>> Put([FromBody] Tournament tournament)
    {
        _context.Tournaments.Update(tournament);
        await _context.SaveChangesAsync();

        return CreatedAtAction(nameof(GetTournament), new { id = tournament.Id }, tournament);
    }

我得到这个错误

Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_TournamentUser'. Cannot insert duplicate key in object 'dbo.TournamentUser'. The duplicate key value is (1, 1).

对此有什么可能的解决办法？我想保持我的多对多表UDPAT，因为每次更新锦标赛时，锦标赛的用户都可以被删除或添加。

可更新的锦标赛对象示例

Answer 1

如果要更新tournment,请尝试从torment对象中删除用户。

 public async Task<ActionResult<Tournament>> Put([FromBody] Tournament tournament)
    {
         tournament.Users=null;
        _context.Tournaments.Update(tournament);
        await _context.SaveChangesAsync();
.....
   
    }

或者如果您想将用户添加到您的锦标赛中

var existing= _context
              .Tournaments
              .Include(u=>u.Users)
              .FirstOrDefault(t=> t.Id== tournament.Id);

if(existing==null) return ...error

if( existing.Users==null) existing.User=new List<User>();

if( existing.Users.Any(u=>u.Id=tournment.User.Id) return ...error

existing.Users.Add(tournment.User);
_context.Entry(existing).State = EntityState.Modified;
_context.SaveChanges();

它看起来很简单，但您为不显式添加多对多关系表而付出了代价。如果使用UserTournment表代码将更短。

你可以试试这个。我从没试过。如果有用，请告诉我：

var existing = _context
              .Tournaments
              .Include(u=>u.Users)
              .FirstOrDefault(t=> t.Id== tournament.Id);

if(existing==null) return ...error

_context.Entry(existing).CurrentValues.SetValues(tournment);

_context.SaveChanges();