Using Junction or Associative Tables in Entity Framework Core

When I first encountered Entity Framework (EF), 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.
   
2. On the left of a field, right-click to open the context menu and select "Relationships..."
   
3. Create a new relationship by clicking the Add button.
   
4. Click the arrow to dropdown the category "Tables and Columns Specification" and click the ellipsis on the right.
   
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.
   
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.
   
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).
   
3. Right-click to bring up the context menu and Select "Set Primary Key". Both fields will become primary keys for the junction table.
   
4. Right-click again and select Relationships
   
5. Add two relationships: One pointing to the Artist table and another pointing to the Album table.
   Album
   
   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.