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?

Daniel Liuzzi · Accepted Answer · 2013-03-18T14:42:22.980

11

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().

edited Mar 18 '13 at 14:42

answered Apr 16 '12 at 15:48

Daniel Liuzzi

16,807
8
52
57

Any way that I can save my entity without listing all of it's properties (again - after filling it) ??? – user2173353 Feb 07 '14 at 11:53
Sorry, I don't follow your question. Do you mean to access a Business POCO right after using the method I described in my answer? – Daniel Liuzzi Feb 07 '14 at 21:10
I mean, inserting the POCO that I have filled in in my code without proving it's properties one-by-one (as above). (Of course I also want to use SET IDENTITY_INSERT) – user2173353 Feb 10 '14 at 09:35
1

Sth like `dbContext.Database.InsertWithIdentity(myPOCO);`. Probably too much to ask... :( – user2173353 Feb 10 '14 at 13:03
Not that I know, sorry. As its name implies, `ExecuteSqlCommand` is not actually inserting anything but executing a command. Although in this case the command is a SQL INSERT, it could be anything. So yes, you need to provide values for all parameters your command will need. – Daniel Liuzzi Feb 10 '14 at 19:32
Without using EF, this isn't much of a solution (in my opinion). However, what worked for me is (1) wrapping the `context.SaveChanges()` in the `ExecuteSqlCommand` statements, then (2) wrapping all of that in a SQL transaction, and (3) making sure that I set the `StoreGeneratedPattern` property in the EF designer for the model's ID to "None". – Ross Brasseaux Apr 13 '16 at 04:08

score 5 · Answer 2 · edited May 23 '17 at 10:29

5

ExecuteSqlCommand will open the connection, execute the sql and then close it. So your next command will execute using a different connection.

What you can do is use the plain old ADO.net classes to insert the data within a single transaction. Or script the data and execute it as shown here

edited May 23 '17 at 10:29

Community

1
1

answered Oct 10 '11 at 15:40

Eranga

32,181
5
97
96

That's so kind of them to close the connection immediately after issuing the command... ;) – Josh Kodroff Oct 12 '11 at 23:24
@JoshKodroff :) that is by design. Usually you delay the opening the connection as possible then use it and close it immediately. – Eranga Oct 12 '11 at 23:46

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

2 Answers2

Linked