IDENTITY_INSERT ON object structure not working

Question

I have this code that should insert a record with the insert ID 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 click tables if IDENTITY_INSERT is set to OFF.

Answer 1

It should not work. It only works if the identity insert is included in the same connection as the real insert. In your case, you can use two different compounds. For it to work, you must maintain your own database connection and pass it to the ObjectContext constructor.

Answer 2

According to the previous Question, you need to start a transaction of your context. After saving the change, you should also copy the Identity Insert column, and finally you must 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(); }