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.
- These first two blocks are the moving of the
Store
primary key to the model from theDbContext
in our initial implementation. Not much of any concern, just housekeeping. - 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 theStoreProducts
table since it was able to work out what the foreign keys were. - In the constraints block, the migration is applying the primary key to
StoreProducts
, and creating the foreign key relationships toStore
andProduct
. - 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!
3 thoughts on “EF Core Part 5: Relationships”