Linq To Sql and identity_insert

Question

I am trying to do record inserts on a table where the Primary Key is an `Identity` field. I have tried calling `mycontext.ExecuteCommand("SET identity_insert myTable ON")` but this doesn't do any good. I get an error saying `IDENTITY_INSERT` is `OFF` when I submit changes. How can I turn it `ON` from the C# code before I submit changes? EDIT I have read that this is because ExecuteCommand's code gets executed in a different session. EDIT 2 Is there any way I can execute some DDL to remove the Identity Specification from my C# code, do the inserts, and then turn Identity Specification back on?

Accepted Answer

You need to do all the steps in a single T-SQL code block - which is going to be really hard if not impossible if you want to turn it on, then execute your LINQ-to-SQL query, and then turn it back off :(

The only real solution I see is to package up the entire SQL into a SQL statement and execute that:

```
SET IDENTITY_INSERT MyTable ON

(do your update here)

SET IDENTITY_INSERT MyTable OFF
```

and execute that as a single code block using `.ExecuteContext()`

Marc

PS: for your EDIT#2 : no, unfortunately, there's no (easy) way to remove the identity from a column, and turn it back on. Basicall you'd have to create a new column without the IDENTITY, copy the values over, drop the IDENTITY column and then do the same backwards when you're done - sorry! :-(

PS #2: this really begs the question: what on earth to do need to do an "identity insert" for? On a regular basis, from an app? Granted - you might run into this need once in a while, but I'd always do this separately, in SQL Mgmt Studio - certainly not in my app..... (just curious what your use case / motivation is).

Linq To Sql and identity_insert

Solution

Problem