Many-to-Many relations in Entity Framework Core 3.1 and 5

Ruben Jonker — Fri, 26 Jun 2020 09:41:42 +0000

Many-to-Many relations are possible in Entity Framework Core but not in the way it worked in Entity Framework 6.

EF Core 3.1

Lets use the example of books and genres. Where a book can have multiple genres and a genre belongs to multiple books.

public class Book{
   public int BookId {get;set;}
   public string Name {get;set;}

   public virtual ICollection<BookGenre> BookGenres {get;set;}
}

public class Genre{
   public int GenreId {get;set;}
   public string Name {get;set;}

   public virtual ICollection<BookGenre> BookGenres {get;set;}
}

public class BookGenre{
   public int BookId {get;set;}
   public int GenreId {get;set;}

   public virtual Book Book {get;set;}
   public virtual Genre Genre {get;set;}
}

We have to define the join table and also need to use it in our navigation properties. After defining the model we can do the wire-up with the fluent api in the OnModelCreating method.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BookGenre>()
        .HasKey(bc => new { bg.BookId, bg.GenreId });

    modelBuilder.Entity<BookGenre>()
        .HasOne(bg => bg.Book)
        .WithMany(b => b.BookGenres)
        .HasForeignKey(bg => bg.BookId); 

    modelBuilder.Entity<BookGenre>()
        .HasOne(bg => bc.Genre)
        .WithMany(g => g.BookGenres)
        .HasForeignKey(bg => bg.GenreId);
}

Thats it. The only solution for now. It works the same as it did in EF 6 with data annotations, but it definitely isn't that elegant.

EF Core 5.0

Luckily Entity Framework Core 5.0 will have full support for many-to-many relations (It is currently the most requested feature for EF Core on GitHub). The navigation properties skip the join table and directly point to the other entity. This will result in writing cleaner queries and simplify the use of the query result.

If we project that to the previous example. The entities for Book and Genre change their navigation properties.


public class Book{
   public int BookId {get;set;}
   public string Name {get;set;}

   public virtual ICollection<Genre> Genres {get;set;}
}

public class Genre{
   public int GenreId {get;set;}
   public string Name {get;set;}

   public virtual ICollection<Book> Books {get;set;}
}

The OnModelCreating method will change a bit (this is still an EF Core 5.0 preview version, so it may differ in the version)

modelBuilder.Entity<Genre>()
                .HasMany(e => e.Books)
                .WithMany(e => e.Genres)
                .UsingEntity<BookGenre>(
                bg => bg
                    .HasOne(bg => bg.Book)
                    .WithMany()
                    .HasForeignKey("BookId"),
                bg => bg
                    .HasOne(bg => bg.Genre)
                    .WithMany()
                    .HasForeignKey("GenreId"))
                .ToTable("BookGenres")
                .HasKey(bg => new { bg.BookId, bg.GenreId });

As you can see not much changed, but the model looks more logical now and this will benefit you a lot in writing Linq queries and handling the results. For instance using ".Include" instead of Include and ThenInclude.

Using MySQL with Entity Framework Core and ASP.NET Core

Ruben Jonker — Thu, 18 Jun 2020 09:22:43 +0000

With the introduction of Entity Framework Core also the support of other databases then SQL got a boost. Off course there was support for MySQL in Entity Framework, but the NuGet package still had some problems and you could end up with a large variety of runtime errors when executing even the simplest of queries. Entity Framework Core added better support for database providers and you can see this in the quality and quantity of providers. Database Providers - EF Core

Using these providers is also a breeze. If we look at the default template of an ASP.NET Core application with EF Core and SQL. You will see something like this in the ConfigureServices method of the StartUp class.

services.AddDbContext<ApplicationDbContext>(options =>
options.UseSqlServer(config.GetConnectionString("DefaultConnection")));

To start using MySQL the only thing needed is to configure another Database Provider. You can use the MySql.Data.EntityFrameworkCore package (or the Pomelo.EntityFrameworkCore.MySql) for this. When installed you can just change this configuration to:

services.AddDbContext<ApplicationDbContext>(options =>
options.UseMySQL(config.GetConnectionString("DefaultConnection")));

After updating the connection string to your MySQL instance try running your migrations and see if it works. I had some problems with the key of the default IdentityUser that didn’t had a MaxLength. Those were all small problems that were fixed easily. After this you should be all setup to continue developing against a MySQL database.

Forem: Ruben Jonker

Many-to-Many relations in Entity Framework Core 3.1 and 5

EF Core 3.1

EF Core 5.0

other links

Using MySQL with Entity Framework Core and ASP.NET Core