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?

Answer 1

Another option is to wrap all your Linq2Sql calls in a TransactionScope(). This should force them all to run in the same connection.

using System.Transactions; // Be sure to add a reference to System.Transactions.dll to your project.

       // ... in a method somewhere ...
       using (System.Transaction.TransactionScope trans = new TransactionScope())
       {
          using(YourDataContext context = new YourDataContext())
          {
             context.ExecuteCommand("SET IDENTITY_INSERT MyTable ON");

             context.ExecuteCommand("yourInsertCommand");

             context.ExecuteCommand("SET IDENTITY_INSERT MyTable OFF");
          }
          trans.Complete();
       }
       // ...

Although, if you are trying to do something like:

context.ExecuteCommand("SET IDENTITY_INSERT MyTable ON");
context.MyTable.InsertOnSubmit(myTableObject)
context.SubmitChanges()
context.ExecuteCommand("SET IDENTITY_INSERT MyTable OFF");

you will probably run into other issues, especially if the identity column has the IsDbGenerated attribute set to true. The SQL command generated by Linq2Sql will not know to include the identity column and value.