How to create Many-to-Many relationship entities and seed them?

Question

If I want to have two tables with many-to-many relationship, how do I create models and seed the database?

For example classic tables Actors and Movies. I tried it like this

public class Actor 
{
   public Actor()
   {
     this.Movies = new HashSet<Movies>;
   }
   public int Id { get; set; }
   public string Name { get; set; }
   public string Surname { get; set;}
   public virtual ICollection<Movie> Movies { get; set; }
}

public class Movie 
{
   public Movie()
   {
     this.Actors = new HashSet<Actors>;
   }
   public int Id { get; set; }
   public string Name { get; set; }
   public virtual ICollection<Actor> Actors { get; set; }
}

As I understood Entity Framework creates automatically new table ActorMovies with two columns with IDs of Actor and Movie.

How I seed it:

Actor actor = new Actor { Name="John", Surname="Doe" };
Movie movie = new Movie { Name="John Doe and his garden hoe" };
actor.Movies.Add(movie);
dbContext.Add(movie); ( call from separate repository )

This automatically seeds Actor table as well and pretty much duplicates all data in both tables. Every time I add new Actor with same Movie, it creates new row in Movie table with same Name but just different ID, which is completely wrong.

My ApplicationDbContext is standard :

public class ApplicationDbContext : IdentityDbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
        public DbSet<Actor> Actors { get; set; }
        public DbSet<Movie> Movies { get; set; }
    }

So what is the proper way of creating, initializing and seeding many-to-many relationship?

Answer 1

You need to access the "Shadow Entity". By "convention" the joining table is the concatenation of the two names (ActorMovie). With the modelbuilder I can use the .HasData method to seed the data. Also by convention the foreign keys in the joining table is the plural name of the Entity + "Id" (ActorsId and MoviesId)

public class SomeDbContext : DbContext
{
    public SomeDbContext (DbContextOptions<SomeDbContext > options)
        : base(options) { }

    public DbSet<Actor> Actors { get; set; }
    public DbSet<Movie> Movies { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        var actorBuilder = builder.Entity<Actor>();
        var movieBuilder = builder.Entity<Movie>();
        var actorMovieBuilder = builder.Entity("ActorMovie");

        var margo = new Actor { Id = 1, Name = "Margot", Surname = "Robbie" };
        var idris = new Actor { Id = 2, Name = "Idris", Surname = "Elba" };

        var suicideSquad = new Movie { Id = 1, Name = "Suicide Squad" };
        var amsterdam = new Movie { Id = 2, Name = "Amsterdam" };

        actorBuilder.HasData(margo,idris);

        movieBuilder.HasData(suicideSquad, amsterdam);

        actorMovieBuilder.HasData(
            new { ActorsId = margo.Id, MoviesId = suicideSquad.Id },
            new { ActorsId = idris.Id, MoviesId = suicideSquad.Id },
            new { ActorsId = margo.Id, MoviesId = amsterdam.Id });
    }
}

Seeded data in table

Note: You can override the "Convention" names.

Answer 2

You need to specify an Id to avoid duplicates, or let the database assign Id for you by changing the Id properties of both classes to the following:

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

As for seeding initial data to database, this is one of the ways to do it:

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    public DbSet<Actor> Actors { get; set; }
    public DbSet<Movie> Movies { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        var actor = new Actor { Id = 1, Name = "John", Surname = "Doe" };
        var movie = new Movie { Id = 1, Name = "John Doe and his garden hoe" };

        builder.Entity<Actor>().HasData(actor);
        builder.Entity<Movie>().HasData(movie);

        // Add relation data
        // Column id = the collection property name + "Id"
        // e.g. public virtual ICollection<Movie> Films { get; set; }
        //      column id = FilmsId
        builder
            .Entity<Actor>()
            .HasMany(p => p.Movies)
            .WithMany(p => p.Actors)
            .UsingEntity(j => j.HasData(new
            {
                ActorsId = actor.Id,
                MoviesId = movie.Id
            }));

        base.OnModelCreating(builder);
    }
}

Entity Classes:

public class Actor
{
    public Actor()
    {
        Movies = new HashSet<Movie>();
    }

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

    public string Name { get; set; }
    public string Surname { get; set; }
    public virtual ICollection<Movie> Movies { get; set; }
}

public class Movie
{
    public Movie()
    {
        Actors = new HashSet<Actor>();
    }

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

    public string Name { get; set; }
    public virtual ICollection<Actor> Actors { get; set; }
}

Visualization:
ActorMovie table is auto-generated by Entity Framework for many-to-many relationship between Actor and Movie entities.