Entity Framework Core Many-To-Many

How to handle Many-To-Many in Entity Framework Core

Many-To-Many relationship in not as straightforward as other relationships. In this post I’ll show you how to create many-to-many relationships and how to use them in EF Core.

The model

Simple and practical example of many-to-many could be some sort of digital ecommerce store. Users can put items into shopping cart (one shopping cart can have many items), whereas items belong to multiple carts. Lets start off by creating 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 unfortunately it won’t work. At the day of publishing this article EF Core cannot handle this scenario. It simple don’t know how to handle this relationship. This is what you get when you’ll try to add migration:

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

First thing we need to do is to manually create another “in-between” class (table) which will hold many-to-many relations. Lets we proceed we new class:

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

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

Since we have a new class that maps Cart to Item we also need to change 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-migration right now then you’ll get another error:

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

Right, CartItem doesn’t have primary key. Since it’s a many-to-many relationship it should have composite key. Composite key is like a regular primary key but it is composed by two properties (columns) instead of one. At the moment, the only way to create composite key is to define it inside 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 EntityFramework. We can proceed with migration.

Inserting into Many-To-Many

Suppose that we already have Cart and Item in our database. Now we want to add specific item to specific cart. In order to do this we need to create new CartItem and save it.

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

// you can either associate it using ids
var cartItem = new CartItem
{
    CartId = cart.Id,
    ItemId = item.Id
};

// or by using entities
var cartItem = new CartItem
{
    Cart = cart,
    Item = item
};

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

Retrieving related data in Many-To-Many

Fetching data from database is rather simple. You need to pay attention to includes in case of retrieving entities with related data. Just remember that there are a total of 3 tables involved: Cart, Item, and CartItem which links items with carts.

// get specific cart including all items
var cartIncludingItems = db.Carts.Include(cart => cart.Items).ThenInclude(row => row.Item).First(cart => cart.Id == 1);
// get all items belonging to cart
var cartItems = cartIncludingItems.Items.Select(row => row.Item);

In addition, you don’t have to do it using a relation. For instance, if you have cart id you can fetch all items at once using following Linq:

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

Same principle applies to opposite use case, which means that you can apply above pattern to get all carts which have specific item.

Deleting from Many-To-Many

By deleting we mean removing association between cart and item. We won’t remove the cart or the item in the following examples, just the thing which holds cart and item association.

Lets start by deleting single item from 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 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();