EF Core Part 5: Relationships

EF Core Part 1: Installation

EF Core Part 2: Dealing with the Database

EF Core Part 3: Working with Database-First

EF Core Part 4: Keys

EF Core Part 5: Relationships

EF Core Part 6: Transformations

Defining a Relationship

What would a relational database be without relationships? Well, it would be a misnomer, I suppose. As mentioned in earlier posts in this series, EF Core will create relationships for us if we play by its convention-based naming rules. So, because we’re going through this and setting things up manually, we’re intentionally not following those conventions.

We’ll set up two relationships. First, a one-to-many that will represent the products available at a store. Then, we’ll expand upon that relationship and be able to see what stores carry what products by making a many-to-many relationship.

Both the Fluent API and attributes can be used to create relationships, so I’ll show both methods for each type of relationship.

One-to-Many

Since we’re doing Code-First, we’ll need to get things prepped for both Fluent API and attributes by adding a navigation property to the Store model.

Fluent API

Like everything done with the Fluent API, let’s head back into the DbContext‘s OnModelCreating function. I’ve added this code to OnModelCreating this code:

modelBuilder.Entity()
.HasMany(s => s.Products);

That’s all there is to it. EF Core will use this little bit of code to create the required foreign key in Product to refer back to the containing store.

For readability, I split the creation of the relationship apart from the primary key distinction. I could have omitted the second modelBuilder.Entity line and set up the whole configuration of the table in a single use of the modelBuilder.Entity call.

Let’s examine the migration that was generated for us.

The first two blocks are adding both pieces of the Store‘s primary key (remember we made this a composite key in the last section). Then it’s adding an index and a new foreign key constraint. That’s all there is to it.

Attribute

Let’s do that once more. This time, with attributes. To do this, we need to add the [ForeignKey] attribute to the Products list in the Store model and supply it with the properties in Product that will be the foreign key fields.

Next, we need to add those foreign key properties to the Product model.

Normally, I would try to preserve the name across both models, I had to change the Name property for the store because Product.Name already exists. Now if we run Add-Migration, we’ll get a migration that looks awfully familiar.

I’m not going to run and Update-Database with this migration due to how many-to-many relationships need to be added. I’m going to Remove-Migration so that it will simply be gone and I won’t have to worry about it getting run when I’m ready to roll out my many-to-many migration.

Many-to-Many

Many-to-many relationships are not yet supported by EF Core. The functionality can be simulated by adding a join table between the entities you want to create the relationship between. The join table will then have one-to-many relationship in each direction to create the many-to-many relationship.

Let’s call this join table and model StoreProduct because it maps the Store and Product tables together. Many-to-many relationships can be handle in both Fluent API and through attributes. There are some significant differences between each implementation, so we’ll do them one at a time.

Here is what this join table will do, graphically.

You’ll have to pardon my line work. One of many reasons I’m a developer and not a draftsperson. You can see that Store has many StoreProduct records, and conversely Product has many StoreProduct records. So, by StoreProduct being the origin the one in the one-to-many relationship pointing to the other tables, we functionally get a many-to-many relationship.

Fluent API

First we need to make a join table. All it will contain are foreign keys to Store and Product. Since we’re using Fluent API we can leverage composite keys the combination of keys will serve as the StoreProduct key. Remember, we still need something declared as a key to keep EF Core happy.

We’ve got the keys for Store and the Product represented here in our join table. Recall that the key for the Store table is already a composite key, so we need both properties here in the join table.

Next we need to tweak our other two models to give them properties for their side of the relationship.

Conveniently the property is the same for both. Instead of one model containing a list of the other like we saw in one-to-many relationships, we now have a reference to many instances of the join table.

Now like everything else we’ve done with Fluent API, to the DbContext! Here is our starting point with the DbContext.

We have two DbSets one for Store and one for Product. Just to make everything all Fluent API-y, I’ve move the declaration of the Product key to the DbContext. Also for brevity’s sake I removed the HasName function, we’ll roll with EF Core default naming scheme.

Even though there are no functional changes yet, I’ve added a migration and updated the database because the model snapshot will think that I’ve made changes because I’ve effectively changed the name of the primary key constraint on Store. This will keep the migration that we generate later clear of that little bit of housekeeping.

Now that we have a model for the join table, how do we wire it up? It’s pretty simple, actually! Here’s what we’re going to do. We’re going to make two one-to-many relationships, one from Store to StoreProducts, and another from Product to StoreProducts. This will give us the ability to see what products a Store has, and what stores carry a particular Product. We’ll do one relationship at a time.

First, let’s set up our key for the StoreProduct model. This one is a doozy with three properties making up the composite key. Let’s add the following code to OnModelCreating:

modelBuilder.Entity()
.HasKey(key => new { key.StoreKey, key.StoreName, key.ProductKey });

Next we’ll create the Store -> Product relationship. This will be the same relationship we set up in the one-to-many section above, only now we’re working through the StoreProduct model. All we need to do is tack on the now familiar relationship creating functions of:

modelBuilder.Entity()
.HasOne(store => store.SpStore)
.WithMany(sp => sp.Products)
.HasForeignKey(fk => new { fk.StoreKey, fk.StoreName });

Here we’re telling the system that this relationship in StoreProduct will have one Store with many StoreProducts. Since StoreProducts has a navigation property of type Product, we have put all the products that belong to a Store into that Store‘s list of StoreProducts.

Now, we’ll do the other side of the relationship, Product -> Store.

modelBuilder.Entity()
.HasOne(product => product.SpProduct)
.WithMany(sp => sp.Stores)
.HasForeignKey(fk => fk.ProductKey);

This does the same thing as the Store -> Product relationship. It populates Product‘s Stores list with the relevant entities out of the StoreProducts table. Since the StoreProducts model has a navigation property of type Store, Product has a list of all stores that carry it.

Our DbContext now looks like this:

Now we could run Add-Migration and Update-Database and EF Core will take care of creating our join table and its associated relationship.

If you were paying attention, you might have seen that we didn’t add a DbSet for StoreProducts. That is because nothing should ever directly reference StoreProduct as a database entity outside these relationships. StoreProduct just serves as a bridge between our two business models. Store can get to its products by using Store.Products[x].Product and Product can reference its stores through Products.Stores[y].Store. Is it a little wordy? Sure. Does it feel like there’s an extra dot in the path? Absolutely. However, this is how we have to implement a many-to-many relationship until the EF Core crew get to building a native version of it. I’ve got my fingers crossed that it’ll be in EF Core 2.0 at the latest.

Attributes

In much the same way that I level set the Fluent API example by moving the ProdKey to the DbContext, I’m going to start of here by removing the composite from Store. Since we’re using only attributes we can’t use composite keys, I’m also going to remove the key setup from DbContext and into the Store model. So, here are the models we’ve been working with as a starting point.

Now we add the StoreProduct model.

In StoreProduct you’ll see that it has its own primary key property. This is only to make EF Core happy and give it a key, we’ll never actually make use of this key directly. You’ll see there are no properties for foreign keys, just our model types.

So now comes the hard part: Add-Migration. Wait…what? That’s right, the hardest part of doing a many-to-many relationship with attributes is choosing the name of your migration. Me? I went with JustDoIt, because it seemed to sum up the process.

So what really happened? Here’s the short version: Since we have [Key] declared on all three of our models and references to the other model types, EF Core was able to work out the key relationships for us and set them up automatically.

The migration it created for us worked all the magic that we needed.

Let’s take a step by step look at what the migration is doing here.

1. These first two blocks are the moving of the Store primary key to the model from the DbContext in our initial implementation. Not much of any concern, just housekeeping.
2. Here we’re creating the StoreProduct table. See how there are two int columns being created and those aren’t our model property names? This is because EF Core is going to keep the foreign keys in the StoreProducts table since it was able to work out what the foreign keys were.
3. In the constraints block, the migration is applying the primary key to StoreProducts, and creating the foreign key relationships to Store and Product.
4. The following two blocks are creating indices on StoreProduct.

That’s it, we can run Update-Database and we’re off and running.

That covers one-to-many and many-to-many from both Fluent API and attribute approaches. It should do for most cases where we need to create relationships between tables. The choice is up to you and your team on which tactic you want to use.

Next up is Transformations and Seeding. Stay Tuned!