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
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
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

Chris Moschini Chris Moschini · Accepted Answer · 2013-03-19T22:20:26

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.

Entity Framework Code First Migrations: Set Primary Key Value

3 Answers