Entity Framework Core: Using Junction or Associative Tables

One-to-many and one-to-one is easy, but what about many-to-many? Today, I explain how to create a many-to-many relationship in Entity Framework Core.

Last Updated: • Develop •

Two men shaking hands

When I first encountered Entity Framework, I always gravitated towards a database-first option. Back when Entity Framework appeared, there wasn't a code-first option.

My thinking towards this approach was your database holds the data. If your database wasn't designed properly from the start and you generate your entities based on the database design, your code (entities) may suffer.

With EF, we now have four options for generating your entities:

  • EF Designer from Database
  • Empty EF Designer Model
  • Empty Code First Model
  • Code-First from Database

I like to get a jump on things when writing code, so I design the database first with all of the relationships to other tables and use the "Code-First from Database" option for three reasons:

  1. I like to see how EF translates the database into Code-First results (so yes, I still love code generation with T4).
  2. I don't want to write ALL of the initial Code-First code when I have a database already defined.
  3. Using Code-First From Database confirms that I won't fat-finger a table name or field/property causing an issue.

With that said, you need to define your database structure properly.

While I understand you should build your systems with business objects as to how you want the system to work instead of basing it off of a database schema, there were times when I felt the database schema was enough.

The reason for this particular post today is because I've been asked by a number of developers how to implement many-to-many relationships in Entity Framework.

So today, I decided to follow up on this.

Creating a Database Relationship

To get the most from Entity Framework, your database should have relationships so it can create and translate the tables into navigational properties.

The process for creating a relationship in SQL Server Management Studio is simple. We'll use the Chinook database as an example.

  1. In SQL Server Management Studio, right-click a table and select Design.

    Screenshot of table design in SQL Server Management Studio

  2. On the left of a field, right-click to open the context menu and select "Relationships..."

    Screenshot of table context menu with relationships in SQL Server Management Studio

  3. Create a new relationship by clicking the Add button.

    Screenshot of table relationships in SQL Server Management Studio

  4. Click the arrow to dropdown the category "Tables and Columns Specification" and click the ellipsis on the right.

    Screenshot of dropdown table relations in SQL Server Management Studio

  5. Define your relationship by filling out the foreign and primary key table and associating your fields with one another. Make sure your data types match.

    Screenshot of field relationships between two tables: Primary and Foreign Key tables in SQL Server Management Studio

  6. Click OK twice to save your relationships.

Once you have these relationships saved, you can generate your entities using the Code-First from Database option.

Many-To-Many Database Relationship

While the one-to-one and one-to-many relationships are fairly simple, the many-to-many relationship is where developers are unsure of how to proceed with such a change in the database.

I'm getting bored with the Students <-> Teachers relationship discussion. Let's spice things up a bit and continue using the Chinook database with the Artist <-> Album relationship. 

In the many-to-many relationship, multiple artists could appear on multiple albums and multiple albums could have multiple artists (think of "Now, that's what I call music" series). How would you represent this in a table?

This is where junction tables come into play. Junction (or associative) tables are non-prime tables whose primary key columns are foreign keys.

In our example above, we would have an ArtistAlbum table containing an ArtistId and an AlbumId. If there was a relationship between the two, there would be a record in the table.

Now, back to our database...

Let's create the junction table and the relationship between the two tables.

  1. Create the junction table and call it ArtistAlbum with the following fields.

    Screenshot of ArtistAlbum table definition

  2. Hold down the Ctrl Key and Left-Click to the left of each field. All fields should be selected. (Alternatively, you can click Ctrl-A to select all fields).

    Screenshot of hilighting two fields in ArtistAlbum table

  3. Right-click to bring up the context menu and Select "Set Primary Key". Both fields will become primary keys for the junction table.

    Screenshot of setting the primary keys in ArtistAlbum table

  4. Right-click again and select Relationships

    Screenshot of selecting Relationships for ArtistAlbum table

  5. Add two relationships: One pointing to the Artist table and another pointing to the Album table.

    Album

    Screenshot of ArtistAlbum relationship to Album

    Artist

    Screenshot of ArtistAlbum relationship to Artist

  6. Click Ok

You now have a junction table ready for Entity Framework to define.

So if there is EVER an album where multiple artists (like AC/DC and Aerosmith) sing together, your database will be able to save many-to-many relationships.

Results of the Relationship

When you generate your Code-First from Database, you'll notice some new navigational properties in your album and artist entities as well as the AlbumArtist relationship.

modelBuilder.Entity<AlbumArtist>(entity =>
{
    entity.HasKey(e => new { e.AlbumId, e.ArtistId });

    entity.HasOne(d => d.Album)         .WithMany(p => p.AlbumArtist)         .HasForeignKey(d => d.AlbumId)         .OnDelete(DeleteBehavior.ClientSetNull)         .HasConstraintName("FK_AlbumArtist_Album");
    entity.HasOne(d => d.Artist)         .WithMany(p => p.AlbumArtist)         .HasForeignKey(d => d.ArtistId)         .OnDelete(DeleteBehavior.ClientSetNull)         .HasConstraintName("FK_AlbumArtist_Artist"); });

When generated, your new ChinookContext has a new relationship. At any point, you can load an artist and find out the number of albums they are on as well as load an album and find out how many artists are on it.

Conclusion

Entity Framework Core is a little different than Entity Framework 6. In Visual Studio 2017, you could create Entity Framework DbContext and Entities through a Code-First from Database using the ADO.NET Data Model Entities option on a new item.

For Entity Framework Core, you need the Package Manager Console to generate your DbContext and entities.

For more information on database relationships with EF Core, check out Microsoft Docs.

How do you handle many-to-many relationships? Do you read the tables and use LINQ to match them? Post your comments and let's discuss.

Did you like this content? Show your support by buying me a coffee.

Buy me a coffee  Buy me a coffee
Picture of Jonathan Danylko

Jonathan Danylko is a freelance web architect and avid programmer who has been programming for over 20 years. He has developed various systems in numerous industries including e-commerce, biotechnology, real estate, health, insurance, and utility companies.

When asked what he likes to do in his spare time, he replies, "Programming."

comments powered by Disqus