EF Core Part 4: Keys

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:

1. Removed the offending AlterColumn function applied to StKey
2. Added a new column StKey2
3. Copied the contents of StKey into StKey2 using the Sql function
4. Dropped StKey
5. Renamed StKey2 to StKey
6. 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.