Add data for many to many by reusing the same data from table

Question

Goal:

Apply data in the the table [Movies], [GenreMovie] and [Genres] in efficent approach (high performance).

Problem:

When you need to reuse the data that is already existing in the database by using Id but you get this error

" {"The instance of entity type 'Genre' cannot be tracked because another instance with the same key value for {'GenreId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values."} " that take place in the code "_schoolContext.AddRange("

        else
        {
            var comedy = new Genre() { GenreId = genreAsDictionary["Comedy"] };
            var action = new Genre() { GenreId = genreAsDictionary["Action"] };

            // Bug here
            _schoolContext.AddRange(
                new Movie() { Name = "Test test", GenresGenres = new List<Genre>() { comedy, action } });

            _schoolContext.SaveChanges();
        }

I do not want another duplicate of same data that is already existering in the database.

How should I solve this situation?

Thank you!

Sourcecode: https://www.learnentityframeworkcore5.com/whats-new-in-ef-core-5/many-to-many-relationship

---

using Microsoft.AspNetCore.Mvc;
using WebApplicationStudent.Database;

namespace WebApplicationStudent.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {
        private readonly ILogger<WeatherForecastController> _logger;
        private readonly SchoolContext _schoolContext;
        private readonly Repo _repo;

        public WeatherForecastController(ILogger<WeatherForecastController> logger, SchoolContext schoolContext, Repo repo)
        {
            _logger = logger;
            _schoolContext = schoolContext;
            _repo = repo;
        }

        [HttpGet(Name = "AddData")]
        public async Task<int> Get()
        {
            try
            {
                //_schoolContext.Database.EnsureCreated();

                var genreAsDictionary = await _repo.GetGenre();
                var movieAsDictionary = await _repo.GetMovie();

                if (!genreAsDictionary.Any() && !movieAsDictionary.Any())
                {
                    var comedy = new Genre() { GenreName = "Comedy" };
                    var action = new Genre() { GenreName = "Action" };
                    var horror = new Genre() { GenreName = "Horror" };
                    var scifi = new Genre() { GenreName = "Sci-fi" };

                    _schoolContext.AddRange(
                        new Movie() { Name = "Avengers", GenresGenres = new List<Genre>() { action, scifi } },
                        new Movie() { Name = "Ants", GenresGenres = new List<Genre>() { action, scifi } },
                        new Movie() { Name = "Satanic Panic", GenresGenres = new List<Genre>() { comedy, horror } });

                    _schoolContext.SaveChanges();
                }
                else
                {
                    var comedy = new Genre() { GenreId = genreAsDictionary["Comedy"] };
                    var action = new Genre() { GenreId = genreAsDictionary["Action"] };

                    // Bug here
                    _schoolContext.AddRange(
                        new Movie() { Name = "Test test", GenresGenres = new List<Genre>() { comedy, action } });

                    _schoolContext.SaveChanges();
                }
            }
            catch (Exception ex) 
            {
                int ff = 23;
            }

            return 1;
        }
    }
}

using Microsoft.EntityFrameworkCore;
using WebApplicationStudent.Controllers;
using WebApplicationStudent.Database;

namespace WebApplicationStudent
{
    public class Repo
    {
        private readonly ILogger<WeatherForecastController> _logger;
        private readonly SchoolContext _schoolContext;

        public Repo(ILogger<WeatherForecastController> logger, SchoolContext schoolContext)
        {
            _logger = logger;
            _schoolContext = schoolContext;
        }

        public async Task<Dictionary<string, int>> GetGenre()
        {
            return await _schoolContext.Genres.ToDictionaryAsync(a => a.GenreName ?? "", a => a.GenreId);
        }

        public async Task<Dictionary<string, int>> GetMovie()
        {
            return await _schoolContext.Movies.ToDictionaryAsync(a => a.Name ?? "", a => a.MovieId);
        }
    }
}

Scaffold-DbContext "Server=DESKTOP-TCK\MSSQLSERVER2022;Database=Testtest2;Integrated Security=true;Trusted_Connection=True;MultipleActiveResultSets=true;TrustServerCertificate=true;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir "Database" -ContextDir "Database" -DataAnnotations -Context SchoolContext -NoOnConfiguring -WarningAction:SilentlyContinue -Force -Project WebApplicationStudent -StartupProject WebApplicationStudent -schema dbo

CREATE TABLE [dbo].[Movies] (
    [MovieId] INT            IDENTITY (1, 1) NOT NULL,
    [Name]    NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_Movies] PRIMARY KEY CLUSTERED ([MovieId] ASC)
);

CREATE TABLE [dbo].[Genres] (
    [GenreId]   INT            IDENTITY (1, 1) NOT NULL,
    [GenreName] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_Genres] PRIMARY KEY CLUSTERED ([GenreId] ASC)
);

CREATE TABLE [dbo].[GenreMovie] (
    [GenresGenreId] INT NOT NULL,
    [MoviesMovieId] INT NOT NULL,
    CONSTRAINT [PK_GenreMovie] PRIMARY KEY CLUSTERED ([GenresGenreId] ASC, [MoviesMovieId] ASC),
    CONSTRAINT [FK_GenreMovie_Genres_GenresGenreId] FOREIGN KEY ([GenresGenreId]) REFERENCES [dbo].[Genres] ([GenreId]) ON DELETE CASCADE,
    CONSTRAINT [FK_GenreMovie_Movies_MoviesMovieId] FOREIGN KEY ([MoviesMovieId]) REFERENCES [dbo].[Movies] ([MovieId]) ON DELETE CASCADE
);

Answer 1

Hi JT4000,

The error happens because you’re creating new Genre objects (new Genre { GenreId = ... }) that Entity Framework treats as new entities, while it’s already tracking the existing ones from the database.

You should not create new Genre objects when they already exist. Instead, attach or query the existing ones from the context and reuse them.

Replace this

var comedy = new Genre() { GenreId = genreAsDictionary["Comedy"] };
var action = new Genre() { GenreId = genreAsDictionary["Action"] };

_schoolContext.AddRange(
    new Movie() { Name = "Test test", GenresGenres = new List<Genre>() { comedy, action } });

With This

var comedy = await _schoolContext.Genres.FindAsync(genreAsDictionary["Comedy"]);
var action = await _schoolContext.Genres.FindAsync(genreAsDictionary["Action"]);

var movie = new Movie()
{
    Name = "Test test",
    GenresGenres = new List<Genre> { comedy!, action! }
};

_schoolContext.Movies.Add(movie);
await _schoolContext.SaveChangesAsync();

FindAsync or FirstOrDefaultAsync returns the tracked entity from the database, so EF Core knows it already exists.

Creating a new Genre { GenreId = X } creates a second instance with the same key, causing the “entity already being tracked” error.

You only need to add the new Movie, not the existing Genre objects.

Always load existing Genre entities from the context before assigning them to a new Movie. Never create new objects with existing IDs.