Entity Framework 4.2, Unable to set Identity Insert ON

Try this: Entity Framework with Identity Insert

Maybe so:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required)){    using (var db = new Context()) // your Context    {        db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ErrorCode ON");        ErrorCode errorCode = db.ErrorCode.First(); // for example        foreach (ErrorCode ec in errorCodesStep3.errorcodesUsers)        {            errorCode.ID = ec.ID;            errorCode.ParentID = ec.ParentID;            errorCode.ErrorDescription = ec.ErrorDescription;                db.ErrorCode.Add(errorCode);        }        db.SaveChanges();        db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ErrorCode OFF");        scope.Complete();    }}

I faced the same problem, we had a table which it is identity was set.then we needed to switch the identity to false, (I mean make it as the following)

[DatabaseGenerated(DatabaseGeneratedOption.None)]public int ID { get; set; }

but, this does not work after doing the migration and update the database.after googling the problem, I founded this articlewhich indicates that the switching ON/OFF identity is not a straightforward operation, and it is not supported now by the Entity Framework.

so the solution (which worked for me) was the following

in the Visual Studio open the table in the designer View and select the column which represent the primary key, (which in your case "ID") then from the properties panel find the "Identity Specification" property (it must have the value of true) set it to false,and do not forget to update the table, by clicking the Update button to execute the SQL statement on the table. (the update button exists in the table toolbar)

then try to run your code, It should work.

I hope that this is helpful. if you have any question, please let me know.

Here is code which I verified does what you want. The idea is to use single connection for both ExecuteSqlCommand and insert operations, and it seems BeginTransaction() call does the trick:

using (var db = new TestEntities()) {     using (var tran = db.Database.BeginTransaction()) {       db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Error ON");            foreach (var id in Enumerable.Range(1, 20)) {                var ec = new Error();                ec.ErrorID = id;                db.Errors.Add(ec);            }            db.SaveChanges();            db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Error OFF");            tran.Commit();        }    }}