Entity Framework: Issue with IDENTITY_INSERT - "Cannot insert explicit value for identity column in table"

Question

I am using Entity Framework 4.0 with C#.NET. I am trying to create a "simple" migration tool to convert data from one table to another(the tables are NOT the same)

The database is a SQL Server 2005.

I have a target DB structure similar to the following:

MYID - int, primary key, identity specification (yes)
MYData - varchar(50)

In my migration program, I import the DB structure into the edmx. I then manually turn off the StoreGeneratedPattern.

In my migration program, I turn off the identity column as follows(I have verified it does indeed turn it off):

            using (newDB myDB = new newDB())
            {
                //turn off the identity column 
                myDB.ExecuteStoreCommand("SET IDENTITY_INSERT SR_Info ON");
            }

After the above code, I have the following code:

            using (newDB myDB = new newDB())
            {
                DB_Record myNewRecord = new DB_Record();
                //{do a bunch of processing}
                myNewRecord.MYID = 50;
                myDB.AddToNewTable(myNewRecord);
                myDB.SaveChanges();
            }

When it gets to the myDB.SaveChanges(), it generates an exception: "Cannot insert explicit value for identity column in table".

I know the code works fine when I manually goto the SQL Server table and turn off the Identity Specification off.

My preference is to have the migration tool handle turning the Identity Specification on and off rather than have to manually do it on the database. This migration tool will be used on a sandbox database, a dev database, a QA database, and then a production database so fully automated would be nice.

Any ideas for getting this to work right?

I used the following steps:

Create database table with identity column.
In Visual Studio, add a new EDMX.
On EDMX, I select Update Model from Database (and select add the desired table)
On EDMX, I click MYID and in the properties pane, I set StoreGeneratedProcedure to None.

At this point, when I add a new record with MYID set, the DB overwrites teh value of MYID with the Identity value.

So far, no matter where I've added "myDB.ExecuteStoreCommand("SET IDENTITY_INSERT SR_Info ON");", it behalves the same way.

I have tried adding a stored procedure and it also doesn't work.

I suspect that ultimately, the migration will not be fully automated.

EF does not play well with Identity columns for migration purpose. We left EF & used basic Sql Client library with Sql Command to do migration. Or you can use metadata to Create & execute raw SQL. — Akash Kava, Sep 07 '13 at 05:56

score 7 · Answer 1 · edited Sep 26 '17 at 14:34

7

When column is identity, inserting record from Entity Framework will raise the following error:

Cannot insert explicit value for identity column in table 'MY_ENTITY' when IDENTITY_INSERT is set to OFF. To avoid error set entity property StoreGeneratedPattern="Identity"

<EntityType Name="MY_ENTITY">
    <Key>
        <PropertyRef Name="UserID" />
    </Key>
    <Property Name="RecordID" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
</EntityType>

edited Sep 26 '17 at 14:34

Massimiliano Kraus

3,638
5
27
47

answered Jul 08 '13 at 08:18

tetris

121
1
3

Where localize this file? – Danilo Pádua Sep 19 '17 at 13:17

score 0 · Answer 2 · edited Sep 26 '17 at 14:33

0

protected override void OnModelCreating(DbModelBuilder modelBuilder)       
{
    modelBuilder.Entity<EntityClass>().HasKey(p => p.Id);

    modelBuilder.Entity<EntityClass>()
        .Property(c => c.Id)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

    base.OnModelCreating(modelBuilder);   
}

edited Sep 26 '17 at 14:33

Massimiliano Kraus

3,638
5
27
47

answered Sep 11 '14 at 10:36

Siddhesh

871
10
15

score 0 · Answer 3 · edited May 23 '17 at 11:46

0

This looks promising Using IDENTITY_INSERT with EF4

You basically need to make sure your data model knows about the changes you made to the database, as they are now no longer in sync with the underlying table.

However, I don't recommend using EF as a heavy-weight data load tool. Prefer the SQL or ETL tooling.

edited May 23 '17 at 11:46

Community

1
1

answered Mar 08 '12 at 18:47

Simon Whittemore

166
7

Simon, I'm only migrating a total of 30-40k worth of records... I was hoping to do a quick dirty migration tool in EF since I'm a bit rusty on "raw" SQL. LOL. I did see the above article before asking this question. – John Stone Mar 08 '12 at 20:29

Entity Framework: Issue with IDENTITY_INSERT - "Cannot insert explicit value for identity column in table"

3 Answers3