Entity Framework 4.2, Unable to set Identity Insert ON

Question

I would like to add records in bulk to a table with given ID's so I could build a hierarchy for displaying records in a tree view fashion. I can singly add records which works fine and I don't set the ID. I would like to set the Ids only in bulk so I set the DatabaseGenerated Option as None in the id column for my entity as None.

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{

     foreach (ErrorCode ec in errorCodesStep3.errorcodesUsers)
     {

           errorCode.ID = ec.ID;
           errorCode.ParentID = ec.ParentID;
           errorCode.ErrorDescription = ec.ErrorDescription;
           db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ErrorCode ON");
           db.ErrorCode.Add(errorCode);
           db.SaveChanges();    
           scope.Complete();
     }
}

ErrorCode

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

I am getting the error that:

cannot set identity when identity insert is OFF.

Not sure whats wrong, since I have seen examples that identity insert can be switched on. I am using EF Version 4.2.0.0 and Runtime Version v4.0.30319. I can easily add an INSERT statement via executeSQL with parameterized variables but I would like to do it with entity framework. Seems like db.Database.ExecuteSqlCommand is in a separate scope and it closed right away and is not available when db.savechanged is executed.

Answer 1

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();
    }
}