Geek Answers Handbook

Linq To Sql and identity_insert

I am trying to make entries in a table where the Primary Key is an Identity field.

I tried to call
mycontext.ExecuteCommand("SET identity_insert myTable ON")
but it is not good.

I get an IDENTITY_INSERT is OFF error when submitting changes.

How can I turn it ON from C # code before submitting the changes?

EDIT

I read that this is because the ExecuteCommand code is executing in a different session.

EDIT 2

Is there any way I can do DDL to remove the id spec from my C # code, insert and insert the id again?

+4
source share
6 answers

You need to take all the steps in one block of T-SQL code - it will be very difficult, if not impossible, if you want to enable it, and then run the LINQ-to-SQL query, and then rotate it back off :(

The only real solution I see is to pack all of the SQL into an SQL statement and execute this:

 SET IDENTITY_INSERT MyTable ON (do your update here) SET IDENTITY_INSERT MyTable OFF

and execute it as one block of code using .ExecuteContext()

Mark

PS: for your EDIT # 2: no, unfortunately, there is no (simple) way to remove an identifier from a column and enable it again. Basicall you will need to create a new column without IDENTITY, copy the values, reset the IDENTITY column and then do the same back when you are done - sorry !: - (

PS # 2: this really asks the question: why do I need an “identity insert”? On a regular basis, from the application? Provided - you may encounter this need from time to time, but I always did it separately, in Mgmt Studio SQL - of course, not in my application ..... (just curious what your use case / motivation is).

+4
source

Another option is to wrap all Linq2Sql calls in TransactionScope (). This should make them all work on 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 are likely to encounter other problems, especially if the identifier column has the IsDbGenerated attribute set to true. The SQL command generated by Linq2Sql will not know to include the column and identifier value.

+15
source

Before running the commands should be enough to open the connection manually. This makes the commands work in one session:

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

I did something like this (Nbuider is used to create entities). I usually create all rows except the insert ID row; what is done at the end. This is creating test data, so a transaction is 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"); }
0
source

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

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

MyId property settings (in dbml )

  • Auto Generated Value: True ;
  • Auto Sync : OnInsert ;
  • Primary Key: True ;
  • Server data type: int NOT NULL IDENTITY ;
  • Type: int ;
0
source

just reseed identity key (with user identifier) ​​each time a new record is added, for example:

  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(); } }
0
source

More articles:


All Articles