Entity Framework Core 2.2.6 Many-to-Many relationship (extra column at table creation)

Question

I'm trying to configure many-to-many relationship in Entity Core, based on that discussion:

https://github.com/aspnet/EntityFrameworkCore/issues/1368

After configuration database is created, with extra column (ProducerId in Movies table), as shown here: Movies. On other side relationship in Producers table, there is no additional column, as shown here: Producers

In project I have classes:

public class Movie
{
    public int Id { get; private set; }
    public string Title { get; set; }
    public Person Director { get; set; }
    public TypesOfGenre Genre { get; set; }
    public long Length { get; set; }
    public DateTime Year { get; set; }
    public Country CountryName { get; set; }
    public IList<Person> Actors { get; set; }

    //Configure enity:
    public int DirectorId { get; set; }
    public Country Country { get; set; }
    public IList<MovieProducer> MovieProducers { get; set; }
}

public class Producer
{
    public int Id { get; private set; }
    public string CompanyName { get; set; }
    public DateTime YearEstablished { get; set; }
    public long EstimatedCompanyValue { get; set; }
    public IList<Movie> Movies { get; set; }

    //Configure enity:
    public Country Country { get; set; }
    public IList<MovieProducer> MovieProducers { get; set; }
}

And I have joinig tabe:

public class MovieProducer
{
    public int ProducerId { get; set; }
    public Producer Producer { get; set; }

    public int MovieId { get; set; }
    public Movie Movie { get; set; }
}

All entities are configured in DbContext class including joining table:

 public void Configure(EntityTypeBuilder<MovieProducer> builder)
 {
        builder.HasKey(k => new { k.ProducerId, k.MovieId });

        builder.HasOne(m => m.Movie)
               .WithMany(mp => mp.MovieProducers)
               .HasForeignKey(m => m.MovieId);

        builder.HasOne(p => p.Producer)
            .WithMany(mp => mp.MovieProducers)
            .HasForeignKey(p => p.ProducerId);
 }

What is wrong with this configuration to create many to many relationship? There should be no extra column for ProducerId in Movies table.

every thing is ok and you don't have any mistak. in many to many relation between x and y we don't have one relation of x in y or vice versa.because we have many-to-many relation!!! you can access to Producer Table only with MovieProducers table from Movie table . hope you understand — masoud
Having IList<Movie> left in the Producer class effectively introduces another one-to-many. That is why ProducerId is added to the Movie table. — Dimitar

Damian Słupski Damian Słupski · Accepted Answer · 2019-09-17T10:43:49

Entity Framework Core 2.2 does not support many-to-many relations as EF6 does, so you should use 'MovieProducer' join table. Make sure to use 'virtual' for all your navigation properties and 'ICollection<>' instead of 'IList<>', e.g.:

public class Movie
{
    public int Id { get; private set; }
    public string Title { get; set; }
    public virtual Person Director { get; set; }
    public virtual TypesOfGenre Genre { get; set; }
    public long Length { get; set; }
    public DateTime Year { get; set; }
    public virtual Country CountryName { get; set; }
    public virtual ICollection<Person> Actors { get; set; }
    //Configure enity:
    public int DirectorId { get; set; }
    public virtual Country Country { get; set; }
    public virtual ICollection<MovieProducer> MovieProducers { get; set; }
}

public class Producer
{
    public int Id { get; private set; }
    public string CompanyName { get; set; }
    public DateTime YearEstablished { get; set; }
    public long EstimatedCompanyValue { get; set; }
    public virtual ICollection<Movie> Movies { get; set; }
    //Configure enity:
    public virtual Country Country { get; set; }
    public virtual ICollection<MovieProducer> MovieProducers { get; set; }
}

public class MovieProducer
{
    public int ProducerId { get; set; }
    public virtual Producer Producer { get; set; }

    public int MovieId { get; set; }
    public virtual Movie Movie { get; set; }
}

and configuration for DbContext builder:

public void Configure(EntityTypeBuilder<MovieProducer> builder)
{
   builder.Entity<Movie>(entity => {
      entity.HasKey(m => m.Id);

      // ...
   });

   builder.Entity<Producer>(entity => {
      entity.HasKey(p => p.Id);

      // ...
   });

   builder.Entity<MovieProducer>(entity => {
      entity.HasKey(mp => new { mp.ProducerId , mp.MovieId });

      entity.HasOne(mp => mp.Producer)
            .WithMany(p => p.MovieProducers)
            .HasForeignKey(mp => mp.ProducerId)
            .OnDelete(DeleteBehavior.Restrict); // Restrict or Cascade

      entity.HasOne(mp => mp.Movie)
            .WithMany(p => p.MovieProducers)
            .HasForeignKey(mp => mp.MovieId)
            .OnDelete(DeleteBehavior.Restrict); // Restrict or Cascade
   });
}

Do not forget to use 'add-migration' and 'update-database' to add changes to database.

Entity Framework Core 2.2.6 Many-to-Many relationship (extra column at table creation)

1 Answers