Tried Entity Framework Core 5.0's Many-to-Many support

One feature that was available in Entity Framework 6 and was missing in Entity Framework Core was Many-to-Many support, but it looks like the implementation is finally complete in 5.0.

It's a feature we've been hoping for in EF Core as well, since it's convenient to use it without having to worry about intermediate tables.

You can use it in Daily Build, as described on Twitter, but basic support was also included in preview8. ¹

A demo was also done on Community Standup on YouTube, so it looks like it's good to see. The documentation doesn't exist yet, of course, so I have a feeling this will be the only official information available.

It is a world where micro ORMs such as Dapper are preferred over full-featured ORMs such as Entity Framework Core, and where KVS is more advantageous in terms of scaling to handle in the cloud than RDB in the first place.

I think it's a good idea to use each of them properly.

In fact, Entity Framework Core is also quite advanced, and if you've touched it with 2.0 / 2.1 and given up on it, it's a good idea to catch up.

Preliminary

This is a bit off topic, but we need to prepare for the actual testing of the Many-to-Many support. Specifically, we just need to configure EF Core's Daily Build and add some basic code to install it.

All of this code was checked with 5.0.0-rc.1.20431.2. The version 6.0 is already up in Daily Build, so you'll have to be careful about that.

https://github.com/dotnet/aspnetcore/blob/master/docs/DailyBuilds.md

The basic code is as follows. It uses LocalDB, but if you don't want to install it, you can install Provider for SQLite.

It is assumed that you don't use DI around the settings, but just use the console app to make it run quickly.

public class AppDbContext : DbContext
{
    public DbSet<Entry> Entries { get; set; }
    public DbSet<Tag> Tags { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=ManyToManySample;Trusted_Connection=True;MultipleActiveResultSets=true");
    }
}

public class Entry
{
    public int Id { get; set; }
    public string Title { get; set; }
    public List<Tag> Tags { get; } = new List<Tag>();
}

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Entry> Entries { get; } = new List<Entry>();
}

The model needs no explanation. It allows you to add multiple tags to an entry, something we've used often in the past. This makes for a many-to-many model.

Use the intermediate tables without definition

The first one is the pattern often used in EF 6, which does not explicitly define intermediate tables, but leaves everything to Entity Framework Core.

The advantage of this pattern is that the necessary intermediate tables are automatically generated by EF Core and can be used without concern for the RDB side.

The definition itself is very simple on the EF Core side, as shown below. By the way, you only need to define one of them.

public class AppDbContext : DbContext
{
    public DbSet<Entry> Entries { get; set; }
    public DbSet<Tag> Tags { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=ManyToManySample;Trusted_Connection=True;MultipleActiveResultSets=true");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Entry>()
                    .HasMany(x => x.Tags)
                    .WithMany(x => x.Entries);
    }
}

If we create the migration code with Add-Migration and run Update-Database in this state, three tables will be created as follows.

Now that we have the settings and tables ready, let's actually add the items. This area is almost the same as EF 6, so you should be able to use it without any problems.

class Program
{
    static async Task Main(string[] args)
    {
        using var context = new AppDbContext();

        var entry = new Entry
        {
            Title = "buchizo"
        };

        entry.Tags.AddRange(new[]
        {
            new Tag { Name = "kosmosebi" },
            new Tag { Name = "rd" }
        });

        await context.AddAsync(entry);
        await context.SaveChangesAsync();
    }
}

When you run it, you can see that the data has been properly added to the intermediate table.

Finally, let's throw a query. Be careful not to use Include to explicitly specify loading, or you'll get an empty collection.

In EF 6, lazy loading was enabled by default, so I guess that's the part I didn't really care about.

class Program
{
    static async Task Main(string[] args)
    {
        using var context = new AppDbContext();

        var entry = await context.Entries
                                 .Include(x => x.Tags)
                                 .FirstAsync(x => x.Id == 1);
    }
}

When you run it, you can see that Tags also contains data. That was easy.

I think this will be the basic pattern, but Entity Framework Core allows for a bit more extensibility, so I'll give a quick summary of that as well.

Specifies the name of an intermediate table

If you let EF Core handle the intermediate table, you couldn't specify a name, but you can call UsingEntity<T> while defining it to specify a table name.

This specification is a bit tricky to write.

You can prepare a model for the intermediate table, but in EF Core, Dictionary<TKey, TValue> is available in various parts, so you can call it without defining a model.

public class AppDbContext : DbContext
{
    public DbSet<Entry> Entries { get; set; }
    public DbSet<Tag> Tags { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=ManyToManySample;Trusted_Connection=True;MultipleActiveResultSets=true")
                      .UseLazyLoadingProxies();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Entry>()
                    .HasMany(x => x.Tags)
                    .WithMany(x => x.Entries)
                    .UsingEntity<Dictionary<string, object>>(
                        "EntryTagMaps",
                        x => x.HasOne<Tag>().WithMany(),
                        x => x.HasOne<Entry>().WithMany());
    }
}

It's a rather uncomfortable way to write it, but we were able to specify the table name without defining a model.

EF Core is quite different from EF 6 because you can also use Indexer to prepare models without defining any properties. While flexible, this is a feature that you don't want to use a lot because it confuses the schema.

Use an intermediate table by explicitly defining it

The last pattern is to define and use a model of an intermediate table.

If you want to achieve Many-to-Many prior to EF Core 5.0, I think I've mostly written about defining an intermediary table and handling it via the following documentation.

https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#many-to-many

If you want to change it for EF Core 5.0, the existing intermediate table schemas may be different from the auto-generated ones, so you can define them separately using UsingEntity<T>.

The following code ties each of the properties in the EntryTagMap class together.

public class EntryTagMap
{
    public int EntryId { get; set; }
    public Entry Entry { get; set; }

    public int TagId { get; set; }
    public Tag Tag { get; set; }
}

public class AppDbContext : DbContext
{
    public DbSet<Entry> Entries { get; set; }
    public DbSet<Tag> Tags { get; set; }
    public DbSet<EntryTagMap> EntryTagMaps { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=ManyToManySample;Trusted_Connection=True;MultipleActiveResultSets=true");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Entry>()
                    .HasMany(x => x.Tags)
                    .WithMany(x => x.Entries)
                    .UsingEntity<EntryTagMap>(
                        x => x.HasOne(xs => xs.Tag).WithMany(),
                        x => x.HasOne(xs => xs.Entry).WithMany())
                    .HasKey(x => new { x.EntryId, x.TagId });
    }
}

If a foreign key differs from the convention, add HasForeignKey after WithMany to specify it explicitly.

You can now set up Many-to-Many in a way that matches the existing intermediate table schema.

Use in combination with Lazy Loading

I'll use this together with Lazy Loading as a bonus.

Please use it in a moderate manner because it is easy to cause a N+1 problem. You don't need to specify Include, so it's convenient.

The usage is unchanged from previous versions, so please refer to the documentation and entries below to add your settings. Of course, the package needs to be adapted to the Daily Build version.

https://docs.microsoft.com/en-us/ef/core/querying/related-data#lazy-loading

Let's use Lazy Loading in its simplest definition.

That's it, just add a call to UseLazyLoadingProxies and change the navigation property to virtual modifier.

The missing configuration was useful because it was told by an error message when Add-Migration was executed.

public class AppDbContext : DbContext
{
    public DbSet<Entry> Entries { get; set; }
    public DbSet<Tag> Tags { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=ManyToManySample;Trusted_Connection=True;MultipleActiveResultSets=true")
                      .UseLazyLoadingProxies();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Entry>()
                    .HasMany(x => x.Tags)
                    .WithMany(x => x.Entries);
    }
}

public class Entry
{
    public int Id { get; set; }
    public string Title { get; set; }
    public virtual List<Tag> Tags { get; set; } = new List<Tag>();
}

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Entry> Entries { get; set; } = new List<Entry>();
}

Lazy loading is enabled so we can remove Include calls from the query.

class Program
{
    static async Task Main(string[] args)
    {
        using var context = new AppDbContext();

        var entry = await context.Entries
                                 .FirstAsync(x => x.Id == 1);

        var tags = entry.Tags;
    }
}

If you run it, you'll see that Tags is lazy loading. If you output the log, it should be easier to understand.

It's very unobtrusive, but you can decide whether you want to enable this area for your actual code.

This should make the transition from EF 6 to EF Core even smoother.

---

1. But note that there are still various unimplemented parts. ↩

Comments

[pantonis]

How do you update related entities (in your example how do you create a new entry with existing tags and delete existing tags from an existing entry)

[Tatsuro Shibamura]

There's no need to think too hard. You can use the same methods as you normally use with collections.

class Program
{
    static async Task Main(string[] args)
    {
        using var context = new AppDbContext();

        var entry = new Entry
        {
            Title = "buchizo"
        };

        // Get exists tag
        var existsTag = await context.Tags.FirstAsync(x => x.Name == "kosmosebi");

        entry.Tags.Add(existsTag);

        await context.AddAsync(entry);
        await context.SaveChangesAsync();

        var existsEntry = await context.Entries
                                       .Include(x => x.Tags)
                                       .FirstAsync(x => x.Id == 1);

        // Remove first tag
        existsEntry.Tags.RemoveAt(0);

        await context.SaveChangesAsync();
    }
}

[Ashley Robinson]

Is there a way to specify the schema?