Updating many-to-many relationships in EF Core 5 and above

EF Core 5 added a direct many-to-many relationship with zero configuration (hurrah!). This article describes how to set this direct many-to-many relationship and why (and how) you might want to configure this direct many-to-many. I also include the original many-to-many relationship (referred to as indirect many-to-many from now on) where you need to create the class that acts as the linking table between the two classes.

You might be mildly interested that this is the third iteration of this article.  I wrote the first article on many-to-many on EF6.x in 2014, and another many-to-many article for EF Core in 2017. All of these got a lot of views, so I had to write a new article once EF Core 5 came out. I hope you find it useful.

All the information and the code come from Chapter 2 of the second edition of my book, Entity Framework Core in Action, which cover EF Core 5. In this book I build a book selling site, called Book App, where each book has two, many-to-many relationships:

1. A direct many-to-many relationship to a Tag entity class (I refer to classes that EF Core maps to a database as entity classes). A Tag holds a category (for instance: Microsoft .NET or Web) which allows users to pick books by their topic.
2. An indirect many-to-many relationship to Author entity class, which provides an ordered list of Author’s on the book, for instance: by Dino Esposito, Andrea Saltarello.

Here is an example of how it displays each book to the user – this is a fictitious book I used for many of my tests.

Overall summary and links to each section summary

For people who are in a hurry I have a ended each section with a summary. Here are the links to the summaries:

The overall summary is:

• Direct many-to many relationships are super simple to configure and use, but by default you can’t access the linking table.
• Indirect many-to many relationships takes more work to set up, but you can access the linking table. This allows you to put specific data in the linking table, such as an order in which you want to read them back.  

Here are the individual summaries (with links).

• Direct many-to-many setup
  • Normal setup. Really easy
  • When you want to define the linking table. More work than an indirect M2M
• Direct many-to-many usage
  • Querying. Really easy
  • Add a new link. Really easy
  • Remove a link. Really easy
  • Create Book with Tags. Easy.
• Indirect many-to-many setup – configuring the linking table. Moderate
• Indirect many-to-many usage
  • Querying. Easy.
  • Add a new link. Easy.
  • Remove a link. Easy.
  • Create Book with Authors. Easy.

NOTE: All the code you see in this article comes the companion GitHub repo to my book Entity Framework Core in Action. Here is link to the directory with the entity classes are in, and many of code examples comes from the Ch03_ManyToManyUpdate unit test class and Ch03_ManyToManyCreate.

Setting the scene – the database and the query

Let’s start by seeing the finished database and how the query works. You can skip this, but maybe having an overall view of what is going on will help you when you are looking at the detailed part you are looking at the specific part you are interested in. Let’s start with the database.

This shows the two many-to-many – both have a linking table, but the direct many-to-many has its linking table created by EF Core.

Next, let’s see the many-to-many queries and how they relate to the book display in the figure below.

You can see that the Book’s Authors (top left) needs to be ordered – that Order property (a byte) is in the linking entity class. But for the Book’s Tags (bottom left), which don’t have an order, the query is much simpler to write because EF Core will automatically add the extra SQL needed to use the hidden linking table.

Now we get into the detail of setting up and using both of these types of many-to-many relationships.

Direct many-to-many setup – normal setup.

The setting up of the direct many-to-many relationship is done automatically (this is known as By Convention configuration in EF Core).  And when you create your database via EF Core, then it will add the linking table for you.

This is super simple to do – so much easier than the indirect many-to-many. But if you want to add extra data in the linking table, say for ordering or filtering, then you either alter the direct many-to-many or use the indirect many-to-many approach.

NOTE: The direct many-to-many relationship is only automatically configured if you have a collection navigational property on both ends. If you only want a navigational property on one end, then you will need to use the Fluent API configure (see next section), for instance …HasMany(x => x.Tags) .WithMany() where the Tags has no navigational property back to the Books.

Direct many-to-many setup: When you want to define the linking table

You can define an entity class and configure the linking table, but I will say that if you are going to do that you might as well use the indirect approach as I think it’s easier to set up and use.

Typically, you would only define the linking table if you wanted to add extra data. There are two steps in this process:

1. Creating a class to map to the linking table

Your entity class must have the two primary/foreign key from each ends of the many-to-many link, in this case it’s the BookId and the TagId. The code below defines the minimum properties to be the linking table – can add extra properties as normal, but I leave that you to do that.

public class BookTag
{
    public int BookId { get; set; }

    [Required]
    [MaxLength(40)]
    public string TagId { get; set; }

    //You can add extra properties here

    //relationships

    public Book Book { get; private set; }
    public Tag Tag { get; private set; }
} 

You could add properties such as the Order property needed for the Author ordering, or maybe a property to use for soft delete. That’s up to you and doesn’t affect the configuration step that comes next.

2. Configuring the linking table in the OnModelCreating method

Now you have to configure the many-to-many linking class/table with the UsingEntity method in the OnModelCreating method in your application’s DbContext, as shown in the code below.

public class EfCoreContext : DbContext
{
    //Other code left out to focus on many-to-many
 
    protected override OnModelCreating(ModelBuilder modelBuilder) 
    {
        //Other configuration left out to focus on many-to-many
 
        modelBuilder.Entity<Book>().HasMany(x => x.Tags)
                .WithMany(x => x.Books)
                .UsingEntity<BookTag>(
                    x => x.HasOne(x => x.Tag)
                    .WithMany().HasForeignKey(x => x.TagId),
                    x => x.HasOne(x => x.Book)
                   .WithMany().HasForeignKey(x => x.BookId));
    }
}

You can see the EF Core document on this here.

NOTE: I really recommend an excellent video produced by the EF Core team which has a long section on the new, direct many-to-many, including how to configure it to include extra data.

Direct many-to-many usage – querying

Querying the direct many-to-many relationships is quite normal. Here are some queries

• Load all the Books with their Tags
  var books = context.Books.Include(b => b.Tags).ToList()
• Get all the books with the TagId (which holds the category name)
  var books = context.Books.Tags.Select(t => t.TagId).ToList()

EF Core will detect that your query is using a direct many-to-many relationship and add the extra SQL to use the hidden linking table to get the correct entity instances on the other end of the many-to-many relationship.

Direct many-to-many usage: Add a new link

To add another many-to-many link to an existing entity class is easy – you just add the existing entry into the direct many-to-many navigational collection property. The code below shows how to add an existing Tag to a book that already had one Tag already.

var book = context.Books
    .Include(p => p.Tags)
    .Single(p => p.Title == "Quantum Networking"); 

var existingTag = context.Tags         
    .Single(p => p.TagId == "Editor's Choice");

book.Tags.Add(existingTag);
context.SaveChanges();

When you add the existing Tag into the Tags collection EF Core works out you want a linking entry created between the Book and the Tag. It then creates that new link.

A few things to say about this:

• You should load the existing Tags using the Include method, otherwise you could lose any existing links to Tags.
• You MUST load the existing Tag from the database to add to the Tags navigational collection. If you simply created a new Tag, then EF Core will add that new Tag to the database.

ADVANCE NOTES on navigational collection properties

Point 1: Let me explain why I say “You should load the existing Tags…” above. There are two situations:

• If you add an empty navigational collection on the initialization of the class, then you don’t have add the Include method, as an Add will work (but I don’t recommend this – see below).
• If your navigational collection is null after construction, then you MUST load the navigational collection, otherwise your code will fail.

Overall, I recommend loading the navigational collection using the Include method even if you have navigational collection has been set to an empty collection because the entity doesn’t match the database, which I try not to do as a future refactor might assume it did match the database.

Point 2: If you are adding a new entry (or removing an existing linking relationship) in a collection with LOTs of items in the collection, then you might have a performance issue with using an Include. In this case you can create (or delete for remove link – see below) the linking table entry. For a direct many-to-many relationship, you would need to create a property bag of the right form to add.

NOTE These ADVANCE NOTES also apply to the adding a new indirect many-to-many link.

Direct many-to-many usage: Remove a link

Removing a link to an entity that is already in the navigation property collection you simply remove that entity instance from the collection. The code below shows removing an existing Tag using the Remove method.

var book = context.Books
    .Include(p => p.Tags)
    .First();

var tagToRemove = book.Tags
    .Single(x => x.TagId == "Editor's Choice");
book.Tags.Remove(tagToRemove);
context.SaveChanges();

This just like the adding of a link, but in this case EF Core works out you what linking entity that needs to be deleted to remove this relationship.

Direct many-to-many usage: Create Book with tags

To add tags when you first create a book you just add each tag to the Tags collection. The code below adds two existing Tags to a new book (note that I haven’t set up the Author – see this part for how you do that).

var existingTag1 = context.Tags.Single(t => t.TagId == "Tag1");
var existingTag2 = context.Tags.Single(t => t.TagId == "Tag2");
var newBook = new Book()
{
    Title = "My Book",
    //... other property settings left out
    
    //Set your Tags property to an empty collection
    Tags = new List<Tag>()
};
newBook.Tags.Add(existingTag1);
newBook.Tags.Add(existingTag2);
context.Add(newBook);
context.SaveChanges();

Indirect many-to-many setup – configuring the linking table

An indirect many-to-many relationship takes a bit more work, but it does allow you to use extra data that you can put into the linking table. The figure below shows the three entity classes, Book, BookAuthor, and Author, with define the many-to-many relationship.

This is more complex because you need to define the linking entity class, BookAuthor, so that you can add extra data in the linking table and also excess that extra data when you query the data.

EF Core will automatically detect the relationships because of all the navigational properties. But the one thing it can’t automatically detect is the composite primary key in the BookAuthor entity class. This code below shows how to do that.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<BookAuthor>() 
        .HasKey(x => new {x.BookId, x.AuthorId});
} 

NOTE: Like the direct many-to-many configuration, if you leave out any of the four navigational properties, then it won’t set up that part of the many-to-many. You will then have to add Fluent API commands to set up the relationships.

Indirect many-to-many usage – querying

The indirect query is more complex, but that’s because you want to order the Author’s Names.

• Load all the Books with their BookAuthor and Author entity classes
  var books = context.Books
     .Include(book => book.AuthorsLink).ThenInclude(ba => ba.Authors
     .ToList();
• Load all the Books with their BookAuthor and Author entity classes, and make sure the Authors are in the right order
  var books = context.Books
     .Include(book => book.AuthorsLink.OrderBy(ba => ba.Order))
     .ThenInclude(ba => ba.Authors
     .ToList();
• Get all the Books’ Title with the authors names ordered and then returned as a comma delimitated string
  var books = context.Books.Select(book => new
{
    Title = book.Title,
     AuthorsString = string.Join(", ",  
book.AuthorsLink.OrderBy(ba => ba.Order)
          .Select(ba => ba.Author.Name))
}).ToList();

NOTE: ordering within the Include method is also a new feature in EF Core 5.

Indirect many-to-many usage – add a new link

To add a new many-to-many relationship link you need to add a new instance of the linking entity class, in our example that is a BookAuthor entity class, and set up the two relationships, in this example filling in the Book and Author singleton navigational properties. This is shown in the code below, where we set the Order to a value that adds the new Author on the end (the first Author has an Order of 0, the second Author is 1, and so on).

var existingBook = context.Books                           
    .Include(p => p.AuthorsLink)                   
    .Single(p => p.Title == "Quantum Networking"); 

var existingAuthor = context.Authors          
    .Single(p => p.Name == "Martin Fowler");

existingBook.AuthorsLink.Add(new BookAuthor  
{
    Book = existingBook,  
    Author = existingAuthor,  
    // We set the Order to add this new Author on the end
    Order = (byte) book.AuthorsLink.Count
});
context.SaveChanges();

A few things to say about this (the first two are the same as the direct many-to-many add):

• You should load the Book’s AuthorsLink using the Include method, otherwise you will lose any existing links to Authors.
• You MUST load the existing Author from the database to add to the BookAuthor linking entity. If you simply created a new Author, then EF Core will add that new Author to the database.
• Technically you don’t need to set the BookAuthor’s Book navigational property because you added the new BookAuthor instance to the Book’s AuthorsLink, which also tells EF Core that this BookAuthor is linked to the Book. I put it in to make it clear what the Book navigational does.

Indirect many-to-many usage – removing a link

To remove a many-to-many link, you need to remove (delete) the linking entity. In this example I have a book with two Authors, and I remove the link to the last Author – see the code below.

var existingBook = context.Books
    .Include(book => book.AuthorsLink
        .OrderBy(x => x.Order))
    .Single(book => book.BookId == bookId);

var linkToRemove = existingBook.AuthorsLink.Last();
context.Remove(linkToRemove);
context.SaveChanges();

This works, but you have the problem of making sure the Order values are correct. In the example code I deleted the last BookAuthor linking entity so it wasn’t a problem, but if I deleted any BookAuthor other than the last I should recalculate the Order values for all the Authors, otherwise a later update might get the Order of the Authors wrong.

NOTE: You can remove the BookAuthor by removing it from the Book’s AuthorsLink collection, like you did for the direct many-to-many remove. Both approches work.

Indirect many-to-many usage – Create Book with Authors

To add Authors when you first create a book you need to add a BookAuthor linking class for each author in the book, setting the Order property to define the order that the Authors should be displayed in. The code below adds two existing Authors to a new book.

var existingAuthor1 = context.Authors
    .Single(a => a.Name == "Author1");
var existingAuthor2 = context.Authors
    .Single(a => a.Name == "Author2");
var newBook = new Book()
{
    Title = "My Book",
    //... other property settings left out

    //Set your AuthorsLink property to an empty collection
    AuthorsLink = new List<BookAuthor>()
};
newBook.AuthorsLink.Add(new BookAuthor
{
    Book = newBook,
    Author = existingAuthor1,
    Order = 0  //First author
});
newBook.AuthorsLink.Add(new BookAuthor
{
    Book = newBook,
    Author = existingAuthor2,
    Order = 1  //second author
});
context.Add(newBook);
context.SaveChanges();

Conclusion

So, since EF Core 5, you have two ways to set up a many-to-many – the original indirect approach (Book-BookAuthor-Author) and the new direct (Book-Tags) approach.  The new direct many-to-many is really easy to use, but as you have seen sometimes using the original indirect approach is the way to go when you want to do more than a simple link between to entity classes.

If you didn’t find this link before, I really recommend an excellent video produced by the EF Core team which has a long section on the new, direct many-to-many, including how to configure it to include extra data.

All the best with your EF Core coding and do have a look at my GitHub page to see the various libraries I have created to help build and test EF Core applications.

Happy coding.