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.

This question has been asked and answered before http://stackoverflow.com/questions/13086006/how-can-i-force-entity-framework-to-insert-identity-columns http://stackoverflow.com/questions/4998948/identity-insert-on-not-being-respected-for-entity-framework-dbset-add-method — Viet Nguyen, Sep 10 '15 at 11:11

Denis Bubnov · Answer 1 · 2015-09-04T06:01:10.263

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 did try that, "says connection was closed" – tam tam Sep 04 '15 at 05:43 — tam tam, Sep 04 '15 at 05:43

Hakan Fıstık · Answer 2 · 2018-03-08T19:43:31.383

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 article which 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.

Evk · Answer 3 · 2015-09-10T07:54:02.523

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

score 0 · Answer 4 · answered Sep 11 '15 at 04:29

0

Add another column to manage the hierarchy. Identity column should not be used for this purpose.

Sorry for not answering the question but I don't think your solution is right.

answered Sep 11 '15 at 04:29

Ppp

1,015
9
14

Entity Framework 4.2, Unable to set Identity Insert ON

4 Answers4