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

Last Updated: March 23, 2021 | Created: January 14, 2021

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.

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.  

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.

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.

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.

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.

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.

5 2 votes
Article Rating
Subscribe
Notify of
guest
27 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
Seva
Seva
24 days ago

Hi Jon! Thanks for the article.

I have a question. I already have set the direct many-to-many aproach.
But, what if a need another relational table based on the same relations?

Let me explain:

Book -> BookAuthor <- Author (Normal)
Book -> BookAuthorView <-Author (Normal but with diferent relation table name and amount of rows)

Basically i need the same direct relation but persisting diferent amount of data.
Can i indicate EF somehow to use diferent relational table?

Regards

Rono
Rono
2 months ago

I am setting up a direct many-to-many connection. I have a FollowUp object and an ActionTaken object with a many-to-many table between them named FollowUpActionTaken. When I configured it using the normal setup and ran it, it threw an error saying it couldn’t find the table ActionTakenFollowUp. Is there a simple way to get it to get it to swap the name components of that table?

Ramim
Ramim
3 months ago

Hi Jon !
Nice article, I have followed your instructions in the direct many to many configuration, but I encounter an error in the intellisense.
I have two models, Movie and WatchList; I create the join table MovieWatchList, and when I try to insert a movie in the WatchList navigation property “Movies” it throws an error in the Add() method. It says that it cannot convert from type Movie to MovieWatchList..

What do you think is the problem? How can I solve it?

Ramim
Ramim
3 months ago
Reply to  Jon P Smith

So, I have great news! I managed to successfully “add” a Movie into the WatchList by creating a “new” Join table, MovieWatchList, instance that passes two objects as values, one Movie and the other WatchList. After checking the database, the join table successfully creates the tuple with the values.
By the way I am applying the indirect apporach.
Now, when I load my WatchList view, I would like to iterate through the WatchList navigation property, Movies, to render each movie via a partial. I pass each movie instance, stored in the navigation property, to a partial. Here comes the problem, when I get to the partial, I would use Movie as a model, but an error is thrown saying that a MovieWatchList model is expected rather than a Movie model. How can I sort that out?

Regards,

Ramim.

Luke
Luke
4 months ago

Hi, great article – this helped far more than the Microsoft documentation!

This is the first time I’ve used this new functionality in EF Core 5 and I have run into an issue – in my application, when following the steps equivalently to yours in realtion to ‘Direct many-to-many usage: Add a new link’, I get ‘Object reference not set to an instance of an object’ on the line:

book.Tags.Add(existingTag);

I have exactly the same Model structure – i.e. a Navigation Property on both tables.

I have debugged the code and the ‘existingTag’ in your example loads fine – I can see all details associated.

Any idea what may be wrong, or am I heading for a StackOverflow question? 🙂

Thanks, Luke

Luke
Luke
4 months ago
Reply to  Jon P Smith

Hi Jon, thanks for that, I’m actually adding to a new book object which hasn’t yet been written to the DB. I’m wondering whether the I need to do a ‘context.SaveChanges’ before it’ll accept the tag mapping?

Last edited 4 months ago by Luke
Luke
Luke
4 months ago
Reply to  Jon P Smith

Got it, all working – thanks Jon!

Michael
Michael
4 months ago

Indirect many-to-many usage – add a new linkTypo: instead
book.AuthorsLink.Add(…
it should be
existingBook.AuthorsLink…

Nice article!

Berk
Berk
4 months ago

Love that content. This is the besth explaining i have seen ever !!!

Berk
Berk
4 months ago
Reply to  Jon P Smith

it was so hard to get the related data on many to many configuration. its much simple with ur article,thanks again.

Kyle
Kyle
4 months ago

This has been so helpful, thank you! Regarding this statement:

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.

Does this mean we have to identify and manage potential duplicates manually? Is there a best practice for this? Say for example if I am adding another book and some of the tags already exist.

Kyle
Kyle
4 months ago
Reply to  Jon P Smith

Ok that makes sense. So we still have to do some work to check for duplicates by finding existing entities that would match the proposed tags before saving changes to the DB. That’s still easier than managing the join table itself within the code.

I was wondering about this as I’m building this exact scenario of a database of books along with tags related to each book. I am working on migrating everything to code first and EF Core 5. Thanks.

Guido Van Hoecke
5 months ago

Would it be possible to add an example of inserting a new book record with links to one or more existing authors and tags. The example s in this article show updating/inserting/removing authors/tags. But I would like to see how to add a book which is written by two existing authors and has one or more existing tags.
TIA, Guido

Garnett Clarke
Garnett Clarke
6 months ago

Any examples with many to many with same table? Eg. a product can have many related products