Duplicates in database

Question

I'm using vs2017 with entityframework 6.1.0 and winforms . As for entityframework i use code-first. I need to make a movie app, I have made all classes for them Movie, Genre and Cast(actors). All Genres are pre inserted in the database. When using update-database everything is created including joining tables moviegenre and movie cast and also foreignkeys. When i insert a movie object. it links the id's from genre cast and movies but it also reinserts every genre which means i have duplicates. I only want the linking of course. So far this is my code.

movie class:

public class Movie
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]       
    public int MovieId { get; set; }

    [Required]
    [StringLength(255)]
    public string Name { get; set; }

    //[ForeignKey("GenreId")]
    public virtual List<Genre> Genre { get; set; }

    //[ForeignKey("CastId")]
    public virtual List<Cast> cast { get; set; }

    [Required]    
    public int GenreId { get; set; }

    [Required]      
    public int CastId { get; set; }

    [Display(Name = "Release Date")]
    public DateTime ReleaseDate { get; set; }

    public Movie()
    {
        Genre = new List<Genre>();
        cast = new List<Cast>();
    }
}

Genre and cast (the same for both classes)

public class Genre
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]       
    public int GenreId { get; set; }

    [Required]
    [StringLength(255)]
    public string Name { get; set; }

    public List<Movie> Movies { get; set; }
}

and of course my code (this piece of code is from the button click event to add a movie into the db.):

private void btnAddMovie_Click(object sender, EventArgs e)
{
    List<Genre> genrelist = new List<Genre>();
    List<Cast> castlist = new List<Cast>();

    var movie = new Movie();                       


    movie.Name = txtTitle.Text;
    movie.ReleaseDate = released;

    //creating lists
    foreach (string item in lbgenre2.Items)
    {
        var genre = new Genre();
        genre.Name = item;
        genrelist.Add(genre);
    }

    foreach (string item in lbCast2.Items)
    {
        var cast = new Cast();
        cast.Name = item;
        castlist.Add(cast);

    }

    movie.Genre = genrelist;
    movie.cast = castlist;


    _context.movies.Add(movie);

    Save();                        
}

private async void Save()
{
    await _context.SaveChangesAsync();
}

What am I doing wrong that it links and reinserts it?

Answer 1

Your problem is because you are creating the Generes again, and again every time you add a new movie and you have a Identity Key, so, no exception will throw.

foreach (string item in lbgenre2.Items)
{
    //Here is your problem, you are creating a new Genere instead of using the already created
    var genre = new Genre();
    genre.Name = item;        
    genrelist.Add(genre);
}

So, instead of creating, use ef to get the existing ones

foreach (string item in lbgenre2.Items)
{
    //try to get the genere from database
    var genre = _context.generes.FirstOrDefault(x => x.Name == item);    
    //if it doesn't exist..
    if(genre == null)
    {
        //Create it 
        genre = new Genre();
        //And set the name
        genre.Name = item;            
    }
    //but, if it already exist, you dont create a new one, just use the existing one
    genrelist.Add(genre);
}

Answer 2

Entity Framework cannot figure out if a Genre or Cast already exists, even if you make an instance of one of them, with identical properties to one which exists in the database. Instead you need to get the existing genres and cast from the database and apply them. And only create a new instance, if it is a completely new genre or cast, which is not in the database:

Pseudo-code:

SaveMovie(Movie movie)
{
    var existingGenres = GetGenresFromDatabase();
    var movieGenres = GetGenresFromListBox();

    foreach (var genre in movieGenres)
    {
        if (genre in existingGenres)
        {
            existingGenre = existingGenres.Where(genreId = genre.Id);
            movie.Genres.Add(existingGenre)
        }
        else 
        {
            movies.Add(genre)
        }
    }

    dbcontext.Add(movie);
    dbcontext.SaveChanges();
}