Entity Framework IDENTITY_INSERT ON doesn't work

Question

I have this code that is supposed to insert the record with identity insert on

using (MCT_DB_ArchiveEntities ent = new MCT_DB_ArchiveEntities())
{
  ent.ExecuteStoreCommand("SET IDENTITY_INSERT [clicks] ON");
  ent.clicks.Attach(ck);
  ent.clicks.Context.ObjectStateManager.ChangeObjectState(ck, System.Data.EntityState.Added);
  ent.SaveChanges();
}

I get this error.

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

score 4 · Accepted Answer · answered Apr 25 '13 at 12:50

4

It is not supposed to work. It works only if the identity insert is turned on on the same connection as the real insert. In your case two different connections can be used. To make it work you have to maintain your own DB connection and pass it to ObjectContext's constructor.

answered Apr 25 '13 at 12:50

Ladislav Mrnka

360,892
59
660
670

Thanks! I was able to get it working using the TransactionScope – user2312219 Apr 25 '13 at 13:19

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

According to this previous Question you need to begin a transaction of your context. After saving the change you have to restate the Identity Insert column too and finally you must have to commit the transaction.

using (MCT_DB_ArchiveEntities ent = new MCT_DB_ArchiveEntities())
using (var transaction = ent.Database.BeginTransaction())
{
    var item = new User {Id = 418, Name = "Abrahadabra" };
    ent.IdentityItems.Add(item);
    ent.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Test.Items ON;");
    ent.SaveChanges();
    ent.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");
    transaction.Commit();
}

Entity Framework IDENTITY_INSERT ON doesn't work

2 Answers2

Linked