How do I temporarily turn off an IDENTITY column in EF Code First?

Question

I'm creating a rewrite of an existing application in EF Code First, but I need to import some of the data from the existing application's database. Here's the definition of my entity class:

public class Business : EntityBase
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int AccountNumber { get; set; }

Now, the imported records need to retain their AccountNumber value from the old system. However, new values should use the IDENTITY value generated by the DB.

How do I turn off the IDENTITY while I'm importing old records, then turn it back on for the remainder of the lifetime of the application? Executing this before importing the records has no effect:

context.Database.ExecuteSqlCommand("set identity_insert Businesses on");

score 5 · Accepted Answer · answered Aug 22 '11 at 23:47

You can't. Executing a set identity_insert command changes the behavior of SQL Server, but not the behavior of Entity Framework.

You have three choices for importing records:

Use raw ADO.NET commands (not entities) or ExecuteSqlCommand.
Use a different DbContext, in which you define the keys as non-generated.
Use a different technology that's more suited for data conversion, like SSIS

score 2 · Answer 2 · edited May 23 '17 at 12:23

I recently ran into this same issue. In my case, I was using SQL Server CE. Here's a link to my solution:

Inserting all rows of a DataTable into a SQL Server table, including Id values (scroll down to Edit 2)

This is basically Diego's solution #1 -- raw ADO.NET. It's not a great solution because you will need to customize the queries to account for any differences in databases, but hopefully it will help solve your problem for the moment.

How do I temporarily turn off an IDENTITY column in EF Code First?

2 Answers2

Linked