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.

Answer 2

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

Answer 3

It should be enough to open the connection manually before executing commands. This makes the commands run in the same session:

context.Connection.Open();
context.ExecuteCommand("SET IDENTITY_INSERT MyTable ON");
// make changes
// ...
context.SubmitChanges();

Answer 4

What I did is something like this(Nbuider is used to create entities). I create all rows normally except the identity insert row; which is done in the end. This is test data creation hence transaction was not needed.

using (var entitiesEfContext = new ContextABC())
{

    var platforms = Builder<Platform>
                                .CreateListOfSize(4)
                                .TheFirst(1)
                                .With(x => x.Description = "Desc1")
                                .With(x => x.IsDeleted = false)
                                .TheNext(1)
                                .With(x => x.Description = "Desc2")
                                .With(x => x.IsDeleted = false)
                                .TheNext(1)
                                .With(x => x.Description = "Desc3")
                                .With(x => x.IsDeleted = false)
                                .TheNext(1)
                                .With(x => x.Description = "Desc4")
                                .With(x => x.IsDeleted = false)
                                .Build();
                foreach (var platform in platforms)
                {
                    entitiesEfContext.Platform.AddObject(platform);
                }
                entitiesEfContext.SaveChanges();

              // the identity insert row (o as id in my case)

               entitiesEfContext.ExecuteStoreCommand("SET IDENTITY_INSERT Platform ON; INSERT INTO [Platform](Platformid,[Description],[IsDeleted],[Created],[Updated]) VALUES (0,'Desc0' ,0 ,getutcdate(),getutcdate());SET IDENTITY_INSERT Platform Off");


              }

Answer 5

I had the same error message. And I resolve it by changing the properties of the Primary key in the table.

MyTable.MyId int IDENTITY(1,1) NOT NULL

The property settings MyId (in the dbml)

• Auto Generated Value: True;
• Auto-Sync: OnInsert;
• Primary Key: True;
• Server Data Type: int NOT NULL IDENTITY;
• Type: int;

Answer 6

just simple reseed identity key (with custom id) every time you add new record, eg:

    using (var desDb = new DesDbContext())
    {
        // del-table-all --------------------------------------------------------
        desDb.Database.ExecuteSqlCommand("DELETE FROM [Products]");

        foreach (var desItem in desList) //desList is List of Product
        {
            // reseed identity key
            desDb.Database.ExecuteSqlCommand("DBCC CHECKIDENT('Products', RESEED," 
                + (desItem.ProductID - 1) + ");");

            // and record
            desDb.Products.Add(desItem);                        

            // save-db                    
            desDb.SaveChanges();

        }
    }