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
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 StoreId
, ProductId
, or Id
names here. So we’re going with StKey
and ProdKey
.
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.
Single-Column Key
Fluent API:
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 OnModelCreating
function.
Just a quick:
modelBuilder.Entity()
.HasKey(entity => entity.StKey)
.HasName("PK_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.
Attribute:
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.
Composite Keys
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 StKey
and Name
columns in the database. In practice this will let us be able to have duplicate StKeys
and 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 HasKey
definition.
Here I’ve changed the HasKey
definition to include two Store
properties, StKey
and 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 oldClrType
, oldNullable
, and OldAnnotation
, so we know something in association with these properties is changing. On Name
, we can see that the oldNullable
and 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.
Lastly, there’s 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 Update-Database
.
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
AlterColumn
function applied toStKey
- Added a new column
StKey2
- Copied the contents of
StKey
intoStKey2
using theSql
function - Dropped
StKey
- Renamed
StKey2
toStKey
- Altered the new
StKey
column 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.
4 thoughts on “EF Core Part 4: Keys”