Many to many relationships are not as simple as other relationships. In this article, I will show you how to create many to many relationships and how to use them in EF Core.

Model

A simple and practical example of many to many may be some kind of digital e-commerce store. Users can put goods into shopping carts (a shopping Cart can have multiple goods), and goods belong to multiple shopping 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; }
}

It looks good, but it doesn't work. At the time of publication, EF Core was unable to handle this situation. It seems that EF Core does not know how to handle this relationship. When you try to add a migration, you will get the following results:

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 relationship represented by the navigation property "Cart.Items" of type "icollection < item >" cannot be determined. Manually configure the relationship, or use the "[NotMapped]" property or "EntityTypeBuilder.Ignore" in "OnModelCreating" to ignore this property.]

The first thing we need to do is to manually create another "intermediate" class (table), which will establish a 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 have created a new class CartItem associated with 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 will appear:

The entity type 'CartItem' requires a primary key to be defined. [entity type "CartItem" needs to define a primary key.]

Yes, CartItem does not have a primary key. Because it is a many to many relationship, it should have a composite primary key. A composite primary key is similar to a regular primary key, but it consists of two attributes (columns) rather than one attribute. 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 the EntityFramework, and we can continue to migrate.

Insert many to many

Suppose we already have carts and items in our database. Now we want to add a specific Item to a specific shopping Cart. In order 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);

// The primary key ID s of the two classes can be used for association
var cartItem = new CartItem
{
    CartId = cart.Id,
    ItemId = item.Id
};

// You can also associate two class entities
var cartItem = new CartItem
{
    Cart = cart,
    Item = item
};

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

Retrieve relevant data in many to many

Getting data from the database is quite simple. Pay attention to using the Include association to retrieve relevant data. There are three tables involved: Cart, Item and cartitem (associate the commodity Item with the Cart).

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

In addition, some operations can be performed without relationships. For example, if you have a shopping cart ID, you can use the following Linq to get all items at once:

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

The same principle applies to the opposite use case, which means that you can apply the above pattern to get all shopping carts with a specific item.

Remove from many to many

Delete means to delete the relationship between Cart and Item. In the following example, we will not delete the shopping Cart cart or commodity Item, but only the relationship between the shopping Cart cart and commodity Item.

Let's start by deleting a single product Item from the Cart.

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

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

Then, let me show you how to delete all items from the shopping cart.

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

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