Tried Entity Framework Core 5.0's Many-to-Many support
Tags: efcore, dotnet, dotnetcore, csharp
Published: 2020-09-14 03:48:32

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. [^1]

[^1]: But note that there are still various unimplemented parts.

{% twitter 1296507302762041345 %}

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.

{% youtube W1sxepfIMRM %}

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.

```csharp
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.

```csharp
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.

![Created tables](https://dev-to-uploads.s3.amazonaws.com/i/74kqe38vm4970v6paxgy.png)

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.

```csharp
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.

![Intermediate table view](https://dev-to-uploads.s3.amazonaws.com/i/c55kn2dnspgdr1qn3ll1.png)

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.

```csharp
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.

![Tags debugger view](https://dev-to-uploads.s3.amazonaws.com/i/0ox0wdtbxlnzzcqawrhv.png)

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.

```csharp
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.

![Specify intermediate table name](https://dev-to-uploads.s3.amazonaws.com/i/69x1dketh9i4wvzewkd5.png)

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.

```csharp
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.

```csharp
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.

```csharp
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.

![Lazy loading](https://dev-to-uploads.s3.amazonaws.com/i/vp9fuuxiy2bwo5t1cadc.png)

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.