Why doesn't SET IDENTITY_INSERT OFF work for me in EF 4.1?

Question

I'm using Entity Framework 4.1 Code First and I have a table that has an IDENTITY key, because all new entries in this table should have an auto-generated ID (the ID column is called AccountNumber). I need to import some data from the previous incarnation of this system - these account numbers need to be preserved.

In a previous question, I learned I have to SET IDENTITY_INSERT ON in order to preserve the old account numbers. The idea is that when importing old customers, I'm going to turn IDENTITY_INSERT ON, run a raw SQL insert statement, then turn it off and proceed normally using EF entities.

So, I have the following code:

    public const string InsertQuery = "INSERT INTO dbo.Businesses (AccountNumber, Name, Active, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6})";

...

            dbContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Businesses ON");
            dbContext.Database.ExecuteSqlCommand(InsertQuery, customerData.AccountNumber, customerData.Name, customerData.Active,
                                                 m_userContextManager.GetCurrentUserName(), Now,
                                                 m_userContextManager.GetCurrentUserName(), Now);
            dbContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Businesses OFF");

            // load the entity and map the rest of the attributes
            dbContext.SaveChanges();

When I get to executing the second statement, I get the following infuriating error (because I've just set it to OFF or so I think):

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

The return value from the statement is -1, but because the documentation on MSDN for ExecuteSqlCommand is really inadequate, I have no idea what that means. I would expect an exception to be thrown if the statement failed for some reason. Does anyone know what's going on here?

Answer 1

There is no need to use plain old ADO.NET; the trick is packing everything into a single command:

public const string InsertQuery = @"
    SET IDENTITY_INSERT dbo.Businesses ON;
    INSERT INTO dbo.Businesses (AccountNumber, Name, Active, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6});
    SET IDENTITY_INSERT dbo.Businesses OFF;
";

dbContext.Database.ExecuteSqlCommand(InsertQuery, customerData.AccountNumber, customerData.Name, customerData.Active,
                                     m_userContextManager.GetCurrentUserName(), Now,
                                     m_userContextManager.GetCurrentUserName(), Now);

// load the entity and map the rest of the attributes
dbContext.SaveChanges();

Additionally, you can drop SET IDENTITY_INSERT dbo.Businesses OFF (since IDENTITY_INSERT is turned off at the end of the command anyway), and dbContext.SaveChanges(), as ExecuteSqlCommand executes the command immediately; it doesn't wait for SaveChanges().