Entity Framework and Identity columns - Part 1 - The Hack

The Problem...

I had some issues with EF 6.5 recently that to be honest took me more than a few minutes to solve.

I was modifying the migration of an existing schema, which updated correctly and all was good. When I ran a query to the table to insert new data, I stumbled upon this lovely exception:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=472540 for information on understanding and handling optimistic concurrency exceptions.

Now I know I had not set the database to use optimistic concurrency so this is the part that took a while to understand.

On the table I have a primary key, an integer type, which is not an identity column, however, EF things that it is. So when the new entity is inserted the pk is not set correctly. Nor is it when you insert the model with a value for the PK field.

The underlying issue is that EF assumes that PKs are identity columns by default so it believes you are causing a concurrency issue when you try to insert this model because it thinks you should not be setting a value for an identity column, something that database should do.

The Solution :)

You have two options to fix this:

1. Make the column an identity column in the database. This requires a table recreated with identity turned on this time and a data migrate step, to move the existing data from that table into the new table.

2. Add the appropriate configuration to EF to identify the PK column as a non-identity column. Then in your code you must the value of the field yourself.

Given I had other constraints at play here, I went with option 2. for now, but when time allows I'll be going back to option 1.

With option 2. you need to tell EF to stop assuming the PK column is also the identity column via this data attribute:

Data Attribute Fix

Old Attribute:

[Key]
public int Id {get;set;}

New Attribute

[Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
public int Id {get;set;}

Fluent API Fix

Using fluent this looks like:

modelBuilder.Entity<TEtity>().Property(t => t.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

After you create and run a new migration to update EF's model of your domain, the id of the model should be persisted without error. That is provided the id you set in code, is unique in the database table.