How to Handle Many-to-Many Relationships in EF Core?

Many-to-many relationships aren't as straightforward as other relationships. In this article, I'll show you how to create many-to-many relationships and how to use them in EF Core.

Models

A simple and practical example of many-to-many could be some kind of digital e-commerce store. Users can add items to a shopping cart (one cart can have multiple items), and items belong to multiple carts. Let's start by creating the Cart and Item classes.

public class Cart
{
    public int Id { get; set; }

    public ICollection<Item> Items { get; set; }
}

public class Item
{
    public int Id { get; set; }

    public string Name { get; set; }

    public int Quantity { get; set; }

    public ICollection<Cart> Carts { get; set; }
}

This looks good, but it doesn't work. At the time of writing, EF Core cannot handle this situation. It seems EF Core doesn't know how to handle this relationship. When you try to add a migration, you get the following:

Unable to determine the relationship represented by navigation property ‘Cart.Items’ of type ‘ICollection’. Either manually configure the relationship, or ignore this property using the ‘[NotMapped]’ attribute or by using ‘EntityTypeBuilder.Ignore’ in ‘OnModelCreating’.

The first thing we need to do is manually create another "intermediate" class (table) that will establish the many-to-many relationship between Cart and Item. Let's create this class:

public class CartItem
{
    public int CartId { get; set; }
    public Cart Cart { get; set; }

    public int ItemId { get; set; }
    public Item Item { get; set; }
}

We've created a new class CartItem that associates Cart and Item. We also need to change their respective navigation properties:

public class Cart
{
    public int Id { get; set; }

    public ICollection<CartItem> Items { get; set; }
}

public class Item
{
    public int Id { get; set; }

    public string Name { get; set; }

    public int Quantity { get; set; }

    public ICollection<CartItem> Carts { get; set; }
}

If you try to add a migration now, another error appears:

The entity type ‘CartItem’ requires a primary key to be defined.

Right, CartItem doesn't have a primary key. Since it's a many-to-many relationship, it should have a composite primary key. Composite primary keys are like regular primary keys, but they consist of two properties (columns) instead of one. Currently, the only way to create a composite key is in OnModelCreating.

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Entity<CartItem>().HasKey(i => new { i.CartId, i.ItemId });
}

Finally, our database structure can be handled by Entity Framework, and we can proceed with migrations.

Inserting Many-to-Many

Suppose we already have Cart and Item in our database. Now we want to add a specific item (Item) to a specific cart (Cart). To do this, we need to create a new CartItem and save it.

var cart = db.Carts.First(i => i.Id == 256);
var item = db.Items.First(i => i.Id == 1024);

// Can use the primary key IDs of both classes to associate
var cartItem = new CartItem
{
    CartId = cart.Id,
    ItemId = item.Id
};

// Can also use both class entity instances to associate
var cartItem = new CartItem
{
    Cart = cart,
    Item = item
};

db.Add(cartItem);
db.SaveChanges();

Retrieving Related Data in Many-to-Many

Fetching data from the database is fairly straightforward. Note the use of Include to retrieve related data. A total of three tables are involved: Cart, Item, and CartItem (which links items to carts).

// Get a specific cart with all its associated items
var cartIncludingItems = db.Carts.Include(cart => cart.Items).ThenInclude(row => row.Item).First(cart => cart.Id == 1);
// Get all items in the specified cart
var cartItems = cartIncludingItems.Items.Select(row => row.Item);

Alternatively, some operations can be performed without using relationships. For example, if you have a cart ID, you can get all items at once using the following LINQ:

var cartId = 1;
var cartItems = db.Items.Where(item => item.Carts.Any(j => j.CartId == cartId));

The same principle applies to the reverse use case, meaning you can apply the above pattern to get all carts that contain a specific item.

Deleting from Many-to-Many

Deleting refers to removing the relationship (CartItem) between a cart (Cart) and an item (Item). In the following examples, we will not delete the cart or the item; we will only delete the relationship between them.

Let's start by removing a single product from a cart.

var cartId = 1;
var itemId = 1;
var cartItem = db.CartsItems.First(row => row.CartId == cartId && row.ItemId == itemId);

db.Remove(cartItem);
db.SaveChanges();

Now, let me show you how to remove all items from a cart.

var cart = db.Carts.Include(c => c.Items).First(i => i.Id == 2);

db.RemoveRange(cart.Items);
db.SaveChanges();

Original author: Zbigniew

Original title: How to handle Many-To-Many in Entity Framework Core

Original link: https://softdevpractice.com/blog/many-to-many-ef-core/

Translation: 沙漠尽头的狼