Update is not working for many-to-many relationship. Entity Framework Core 2.2

Question

I have many to many relationships between games and Categories. I am Using Sql Server. One game can have multiple categories. Create operation working but the update is not working. Getting an error:

InnerException: Violation of PRIMARY KEY constraint 'PK_GameCategories'. Cannot insert duplicate key in object 'dbo.GameCategories'. The duplicate key value is (1003, 1).\r\nThe statement has been terminated.

public class Game
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Display(Name = "Game Name")]
    public string Name { get; set; }

    [Display(Name = "Game Categories")]
    public ICollection<GameCategory> GameCategories { get; set; }
}

public class Category
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Display(Name = "Game Category")]
    public string Name { get; set; }

    [Display(Name = "Game Categories")]
    public ICollection<GameCategory> GameCategories { get; set; }
}

public class GameCategory
{
    public int GameId { get; set; }
    public Game Game { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

// --------------------------
GameController/Edit Action [Working Perfectly]
// -------------------------
[HttpGet]
[Route("edit-game/{id}")]
public async Task<IActionResult> EditGame(int id)
{
      try
      {
                if (id == null) return NotFound();
                var gameInDb = await _db.Games.FirstOrDefaultAsync(g => g.Id == id);
                if (gameInDb == null) return NotFound();

                var model = new GameChangeViewModel()
                {
                    Title = $"Edit {gameInDb.Name}",
                    Name = gameInDb.Name,
                    // Displaying list of categories
                    GameCategoryList = _db.Categories
                        .Select(c => new SelectListItem() { Text = c.Name, Value = c.Id.ToString()}).ToList(),
                    // loading selected values
                    GameCategoryId = _db.GameCategories.Where(c=>c.GameId == gameInDb.Id).Select(s=>s.CategoryId).ToList()
                };

                return View(model);
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return NotFound();
            }
        }

The below code is not working and raising the above exception.

[HttpPost]
        [Route("edit-game-post")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> EditGamePost(GameChangeViewModel model)
        {
            try
            {
                if (!ModelState.IsValid) return View(nameof(EditGame), model);

                var gameInDb = await _db.Games.FirstOrDefaultAsync(g => g.Id == model.Id);
                if (gameInDb == null)
                {
                    ModelState.AddModelError("", "No Data");
                    return View(nameof(EditGame), model);
                }

                _db.GameCategories.RemoveRange(gameInDb.GameCategories);
                _db.SaveChanges();

                gameInDb.Name = model.Name;
                var category = _db.Categories.Where(c => model.GameCategoryId.Contains(c.Id)).ToList();
                foreach (Category cat in category)
                {
                    gameInDb.GameCategories.Add(new GameCategory()
                    {
                        Category = cat, Game = gameInDb
                    });
                }

                await _db.SaveChangesAsync();
                return RedirectToAction(nameof(Index));

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                ModelState.AddModelError("", "Exception Happen");
                return View(nameof(EditGame), model);
            }
        }

Unfortunately, I have not found any suitable CRUD operations on many-to-many relationships. Thank you

Answer 1

Try to create new column ID as primary key in GameCategory table.