Many-To-Many Relationships in Entity Framework Core

Many-to-many relationship is one of the most common relationships in the real world. However, there are many software developers and even database administrators with years of experience that don't know or use this type of relationship in their database designs. Some of them use it, but they often do it wrong, so this article is about setting up the many-to-many relationship using Entity Framework the correct way.

What is Many-To-Many Relationship

Many-to-many is a relationship between 2 database tables that allows each table to have many references of the other table rows. For example, let's consider a food recipes blog that has Blogposts and Categories tables. Each Blogpost can have multiple categories, and each Category can have multiple blogposts.

The Blogposts table has the following articles:

1. How to Bake a Pie.
2. How to Grill a Salmon Steak.

The Categories table has the following items:

1. Desserts
2. Fish
3. Easy Recipes

The "How to Bake a Pie" blogpost has the following categories:

• Desserts
• Easy Recipes

The "How to Grill a Salmon Steak" blogpost has the following categories:

• Fish
• Easy Recipes

Notice that both blogposts have more than one categories assigned to them. One has the "Desserts" and "Easy Recipes", the other one has the "Fish" and "Easy Recipes" categories. Also, please note that the "Easy Recipes" category is used for both the "How to Bake a Pie" and "How to Grill a Salmon Steak" blogposts. That is what many-to-many relationship is: each blogpost can have multiple categories, and each category can be assigned to multiple blogposts.

Many-to-many relationships are actually very common. A doctor can have multiple patients, and a patient can go to multiple doctors. A college student can sign up for multiple classes, and each class can have multiple students. A YouTube video can have many viewers, and each viewer can watch multiple videos. A book can have multiple authors, and each person can be the author of multiple books. The list goes on and on.

Technical Implementation of Many-To-Many Relationships

So how do we implement a many-to-many relationship between 2 tables? We do that by creating another table that will connect the Blogposts and Categories tables in a many-to-many relationship. We can name that table BlogpostCategories. This type of tables are often called "join", "link", or "middle" tables because they are used for creating a link between 2 other tables. The BlogpostCategories table will have the following columns:

• BlogpostId - integer, not null.
• CategoryId - integer, not null.

As you can see from the above bullet points, the BlogpostCategories table will have only 2 columns. The BlogpostId column will be a foreign key referencing the Blogposts table. The CategoryId column will be a foreign key referencing the Categories table.

What about the Primary Key of the BlogpostCategories table? The primary key of this table will be a composite key of BlogpostId and CategoryId which means that the primary key will be consisted of 2 columns. Making the primary key a composite key consisted of BlogpostId and CategoryId columns will ensure that there can be no duplicate rows in that table consisted of the same BlogpostId and CategoryId combinations. For example, the "How to Bake a Pie" blogpost CANNOT list the "Desserts" category twice this way. If our primary key was a totally different column, for instance, another column called Id, each blogpost could have duplicate categories. The "How to Bake a Pie" blogpost could have "Desserts" category listed more than once which obviously would be wrong. This is a common mistake that many developers make when they create many-to-many relationships. If you decide to use an auto-incremented Id column as a primary key, then you have to at least add a unique index consisted of BlogpostId and CategoryId columns which is usually an overkill for most scenarios. In some cases, it may make sense to have duplicate records, but those would be very unique cases. The correct primary key selection for most cases is a composite key like we did in our example.

This is what the database diagram will look like for the many-to-many relationship between the Blogposts and Categories tables. As you can see, the BlogpostCategories table in the middle connects the Blogposts and Categories tables. By adding rows to the BlogpostCategories table, we can link each blogpost to many categories and each category to many blogposts:

Creating the EF Models

Let's start by creating a DbContext object and some models for Entity Framework. We will create model classes for the Blogposts, Categories, and BlogpostCategories tables.

This is what the Blogpost model looks like. It's a pretty simple model that specifies that the Id property should be used as the primary key:

Here is what the Category model looks like. Just like the Blogpost model, this one is pretty basic too. Its primary key is the Id column:

And this is the BlogpostCategories table that we talked about so much. As you can see from the image below, we specify that the BlogpostId property is going to be used as a foreign key for the Blogposts table, and the CategoryId property is going to be the foreign key of the Categories table:

Next, we have to add those models to our DbContext class, so that when we create a migration, Entity Framework knows how to generate the migration code for creating those tables in the database:

You might have noticed from the BlogpostCategory model image, that we didn't specify the primary key on the model (there were no "Key" attributes on any properties). Unfortunately, Entity Framework Core doesn't allow setting composite primary keys (keys consisted of multiple columns) using data annotations (the "key" attribute). Therefore, we have to configure the primary key of the BlogpostCategories table in the OnModelCreating method of our ApplicationDbContext class. We have to override the OnModelCreating method and add the configuration code there:

The HasKey method specifies that the primary key of the BlogpostCategory model is a composite key consisted of the BlogpostId and CategoryId columns.

Adding the EF Core Migration

We created the EF models. We added them to the ApplicationDbContext class. Now, we have to add a migration. Open a command prompt window and navigate to the project directory where your application is and run the following command:

dotnet ef migrations add BlogpostCategories

Next, I always recommend taking a look at the generated migration code to make sure that everything is correct. You may miss some things when configuring the models, so it is always a good idea to check the generated migration code before updating the database with it. You should make sure that the primary key of the BlogpostCategories table is consisted of BlogpostId and CategoryId columns in the generated migration code, the BlogpostId column is a foreign key referencing the Blogposts table, and the CategoryId column is a foreign key referencing the Categories table:

If everything looks good, then you can update the database with that migration code by running the following command:

dotnet ef database update

Conclusion

We created a many-to-many relationship between the Blogposts and Categories tables using a middle (or link or join) table called BlogpostCategories. That allowed us to link each blogpost to multiple categories, and each category to multiple blogposts. Please note that we only discussed how to create the relationship using Entity Framework Core. If you are interested in learning how to create, read, update, and delete (CRUD) data in a many-to-many relationship setup, please consider subscribing to our newsletter for being notified when we publish an article about how to build CRUD operations with many-to-many relationships using Entity Framework Core.