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

Question

I have to migrate data from one database to another. Keys should be preserved. I'm using EF with an auto-generated .dbmx file. I've read many answers to similar problem and follow those suggestions: I do turn IDENTITY_INSERT ON exactly before inserting the entry; on the database diagram I changed the StoreGeneratedPattern to None on Id column (also tried to do the same with adding corresponding attribute to Id property).

Here is my code:

var myEntity = new MyTable
{
    Id = 12345,
    Name = "Lorem ipsum",
    BrandId = brandId,      
};

MyContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT MY_TABLE_NAME ON");
MyContext.MyTable.Add(myEntity);
await MyContext.SaveChangesAsync(); //getting error here

When do that in Management Studio, it works just fine. The problem appears only when using Entity Framework.

What is looking weird that I can see in SQL Server Profiler that INSERT_IDENTITY command is executed and insert SQL comes with Id column, but seems is not executed. When I copy paste this SQL query it works just fine.

Answer 1

The problem is that the EDMX is and Designer.cs files are generated before you compile, when you genrate the schema from the DB and do not change. The validation is run against those what is in those files. They expect the identify_insert property to be enabled, so when you programatically disable it in the DB, the EF layer throws the exception you encountered. Basically saying - your DB doesn't match what I have.

Honestly, doing a large table data transfer this way is not what EF is designed for. You should use Bulk Data Copy