Entity Framework Code First Migrations: Set Primary Key Value

Question

I have a table that stores some extra data for some rows of a table like:

public class QuoteExtra
{
    [Key]
    public int QuoteId { get; set; }
    // More fields here
}

I'd like to be able to add rows to this table where I explicitly set the PK.

If I simply leave it as above, setting a value and submitting the row causes the value to be discarded and replaced with the auto-generated value from the database (and the column is defined as an Identity column in the actual schema).

This appears to be the right solution:

public class QuoteExtra
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int QuoteId { get; set; }
    // More fields here
}

However this instead gets me the exception:

Cannot insert explicit value for identity column in table 'EnumTest' when IDENTITY_INSERT is set to OFF.

So, how do I write my class so that I'm able to set the value of a Primary Key in EF?

Edit:

I tried adding the following Code-based Migration to set IDENTITY_INSERT to ON:

public override void Up()
{
    Sql("SET IDENTITY_INSERT QuoteExtra ON");
}

I ran it and tried again, but got the same exception as above. What's strange is the database does reflect this setting, and running SQL against it directly does allow me to insert arbitrary values in for the primary key - so it would appear Entity Framework itself is enforcing this rule, and neglecting to recognize that IDENTITY_INSERT is not in fact set to off. Do I need to set it somewhere in EF itself?

Edit 2:

I misunderstood IDENTITY_INSERT; I assumed setting it once left it on for that table indefinitely. In fact it lives as long as the "Session," meaning that for example setting it in a Migration means it lives... as long as that Migration runs, and has no bearing on future connections like my later .Add() with EF, which explains why I still got that exception - the DB really is the source of the exception, not EF. Since IDENTITY_INSERT is limited to at most one table per session it's a fairly inefficient way to do this - not creating an Identity PK column in the first place seems like a better route.

What do you mean by *What's strange is the database does reflect this setting*? — Ladislav Mrnka, Mar 19 '13 at 21:01
What I meant was that I was able to insert values if I used raw SQL to do so after running the identity_insert on command - but now I see why - it's because the setting is on for the length of a session, and in raw SQL in Sql Server Management Studio I stayed within the span of a session, while my EF migration and following test code are separate sessions. — Chris Moschini, Mar 19 '13 at 21:09
Yes. Identity insert is related to connection. If you ensure that your code is running in within the same connection you should be able to use it. But it is somehow black magic especially when considering migrations. — Ladislav Mrnka, Mar 19 '13 at 21:22

Chris Moschini · Accepted Answer · 2013-03-22T22:03:55.440

This is the proper way of creating a PK without Identity Autoincrement enabled:

public class QuoteExtra
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int QuoteId { get; set; }
    // More fields here
}

However, if you add DatabaseGenerated(DatabaseGeneratedOption.None)] after EF Migrations has already created the table, it quietly does nothing to the table. If this is your scenario you need to add a manual migration to drop the table:

add-migration RecreateQuoteExtra

And in the migration:

public override void Up()
{
    DropTable("QuoteExtra");
}

EF Automatic Migrations will then automatically recreate the table without the Identity constraint, which will then allow you to set the PK value anytime, without having to run any special commands like IDENTITY_INSERT ON.

It sounds like a less destructive way to do this is coming in EF7 ("Data Motion"), or you could write a lot of manual sql yourself in the migration to create temp tables and move data around if you wanted to avoid losing existing data in the table.

EDIT: Depending on your scenario EF Migrations might not recreate the table - if the class already exists and is already added to your DbContext it will just drop it and leave it at that, meaning your manual migration has to not only drop but also create the table. Not a big deal since the scaffolded code EF Migrations generates for you from add-migration will create these statements for you, but it is a bit more code to check over for issues.

score 4 · Answer 2 · answered Mar 19 '13 at 20:25

4

It is right solution but only for a new table. If you change database generated option for an existing table, EF migrations are not able to perform this change and your QuoteId column is still marked as Identity in the database.

answered Mar 19 '13 at 20:25

Ladislav Mrnka

360,892
59
660
670

The docs on IDENTITY_INSERT specify no such limitation, and in fact in raw SQL I can turn this on and off freely long after the table's been created and specify values for the PK. http://msdn.microsoft.com/en-us/library/ms188059.aspx They state there at most one table can have this enabled per session, meaning I actually need to toggle this on not via a Migration but rather right before an INSERT then turn it back off, which is pretty ugly. It seems like finding a way to turn off the Identity functionality would be better... . – Chris Moschini Mar 19 '13 at 20:56
EF doesn't provide a direct way to play with `IDENTITY_INSERT` setting. My original answer targeted your playing with `DatabaseGeneratedOption`. – Ladislav Mrnka Mar 19 '13 at 21:00
I see what you were trying to say now. EF Migrations needs to actually drop the table in order to perform this change - this may be a limitation of SQL Server. – Chris Moschini Mar 19 '13 at 22:14
1

The SQL server actually needs to drop the column but at the end it may be the same. – Ladislav Mrnka Mar 20 '13 at 09:18

score 1 · Answer 3 · answered Mar 20 '14 at 14:19

1

I could'nt solve this Problem with your Hints then i've tried to re-create the whole Database but it wasnt working, too.

To fix this you have to remove the identity: true property on the first(!) creation of the Column (e.g. Initial-Migration).

Maybe it will help someone..

answered Mar 20 '14 at 14:19

RDPP

21
2

Yep, that's what my answer says. – Chris Moschini Mar 20 '14 at 15:30
The scenario is a little bit trickier as I figured. When automatic migrations are turned on, it is enough to have a (probably altered) model with DatabaseGeneratedOption.None set on the primary key column, SET IDENTITY INSERT ON for the table in a transaction. However, when a manual migration is generated using code first approach, the migration is going to have the identity: true attributes for the primary key columns. That setting is not set by the automatic migration, so it should be eliminated from the migration Up() method before creating the database. – Daniel Leiszen Nov 12 '15 at 23:44

Entity Framework Code First Migrations: Set Primary Key Value

3 Answers3

Linked