EF Core Part 4: Keys
So far we’ve worked through installing the required libraries and setting up the connection to our backend through both Code-First and Database-First. In the next few posts, we’ll explore how to better configure our database when using the Code-First approach. There are two ways to handle this; using the Fluent API in the DbContext and through attributes on properties in our models. We’ll touch on creating keys, defining relationships, transformations, and seeding tables.
I’ll be working on the Store/Product models that I used in the part 3, although there’s absolutely no other relation. That was Database-First, after all! Store and Product are just a good intuitive relationship.
Even though Fluent API and attributes can be used in the same project, for the sake of the person who maintains the code try to be consistent.
Declaring a Key
EF Core needs a key to do anything with a database. Without one, it’s going to complain about not having one. Let’s assume that we can’t name our primary key field in such a way that EF Core can use its convention-based assumptions. No
Id names here. So we’re going with
If we try to add our first migration at this point, we’ll get an error message and a very angry Package Manager Console.
Yipes, let’s not do that again. I don’t know if command line interfaces can hold a grudge, but I don’t want to find out.
Adding a key in the Fluent API is quite easy. All we need to do is open up the context and add bit of code to the
Just a quick:
.HasKey(entity => entity.StKey)
What this does is tell EF Core that we want to specify
StKey as the primary key for
Store and when the database is created name the Primary Key constraint
"PK_StKey". The constraint name is arbitrary. I could have named it
"Bob" if I had been so inclined, but that wouldn’t have been very helpful when I look at the database again in six months. Future me would have been very annoyed by that decision.
Adding a key via property attributes is even easier! Let’s add a primary key to the
Product model. We simply need to add a
[Key] attribute to the
ProdKey and add a using for
System.ComponentModel.DataAnnotations. That’s it.
Now that we have keys on both tables, let’s run
Add-Migration and see what we get.
First up is the
Product table. Simply by adding the
[Key] attribute the migration creator added the
Annotation function and its associated configuration to the column definition as well as created a primary key constraint named
"PK_Product". Using the attribute approach, we don’t get to name our constraint
"Bob" or any other silly name, we get EF Core’s standard naming convention. If we really wanted to, we could rename the constraint here. However, EF Core’s name convention is good, so we’ll leave it alone.
Next we have the
Store table where we added the key with the Fluent API. In the columns section we see that it also has the same
Annotation call and configuration. Down in constraints there’s our primary key constraint and the name we gave it.
Before we continue, let’s run
Update-Database to actually create the database. That way we have something to run future migrations against.
Sometimes we need to a key that is comprised of more than one column in the database, these are composite keys. They are helpful when we’re unable to truly define a unique ID in each row. There are differing opinions about where to use composite keys versus generating a truly unique ID for each row. That’s more a discussion to have with your DBA when working out the database structure. I’m going to cover it regardless, it’s a handy tool to have in toolbox. It might be the Torx screwdriver in the toolbox, which is to say you may not need it a lot of the time, but you’ll be happy you have if when the need arises.
Composite Keys can only be created using the Fluent API, attributes just don’t know what to do when multiple
[Key] fields are found. Setting these up in Fluent API is only marginally more complicated than declaring a single column key. Let’s add the
Name field in
Store to the key building out the key to include both the
Name columns in the database. In practice this will let us be able to have duplicate
Names as long as each combination of those columns is unique. Maybe not the best design decision, but this is an example project, not a production one.
Let’s go back into our
DbContext and the
OnModelCreating function and change the
Here I’ve changed the
HasKey definition to include two
Name. This is the only change that EF Core needs to tell the database there’s a composite key. Now let’s create the new migration and see what we get….
Uh oh, what is this? Haven’t seen a yellow message out of PMC yet…
An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.
This terrifying sounding message is not as dire as it sounds. Let’s take its advice and look at the migration and see what’s going on with this.
The first thing the migration does is drop the old primary key. Anytime I see the word “drop” in generated code, I always take a closer look at what it’s doing. I think the PMC has the same paranoia. In this case,
DropPrimaryKey does just that. It removes the
PK_StKey constraint and nothing more.
The next two blocks are
AlterColumn calls. We can get an idea of what’s going by looking at the properties that start with “old”. We have
OldAnnotation, so we know something in association with these properties is changing. On
Name, we can see that the
nullable properties are different. That makes sense,
Name is part of the key now, it can’t be null. Next, we see
oldAnnotation, but no
Annotation to replace it. That’s because we’re not replacing it, we’re removing it.
StKey is not the identity field anymore, so the
Annotation has got to go.
AddPrimaryKey. It’s got the name we gave it (which happens to be the same name as before because I like that name), the table it’s applying the key to, and the columns involved in the key.
All that makes sense, right? Let’s do an
Well, that’s unfortunate… What happened? We can see enough on the first line to figure out what went on:
System.InvalidOperationException: To change the IDENTITY property of a column, the column needs to be dropped and recreated.
It turns out that this is a current bug as of EF Core 1.1. Fret not! For I have… a workaround. Since we can make edits to the migration file, I’ve put together a way around this bug.
Where is a step-by-step of what I did with this:
- Removed the offending
AlterColumnfunction applied to
- Added a new column
- Copied the contents of
- Altered the new
StKeycolumn to not allow nulls
So what was that warning about? Well, any time we make any sort of change to the key on a table, it is possible that we could lose data or have other errors due to change in keys and unique of the columns involved. The migration builder does not look into the database to let us know that we can’t change a constraint because the existing data doesn’t fit the new constraint. It just gives a warning that it could potentially happen. In which case, the migration would fail and we’d have to fix the data before trying again.
That covers us for creating primary keys. No matter what your approach, it’s all just variations on these methods. In the Code-First world, there are only these two ways of doing it. If composite keys are a requirement, then there’s only one.