Override IDENTITY_INSERT for use in loading data through LINQ-to-SQL

Goyuix · December 2012

I was hoping to be able to batch load a bunch of records that already have the primary key specified and despite my best efforts in wrapping transactions and other things I can't seem to avoid the following error:

Explicit value must be specified for identity column in table 'Users' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Here is a code sample I have tried - is there a way to actually override the identity insert? I can do this in SQL but I am really trying to take the Joe Albahari LINQPad challenge and do this through LINQPad instead of the management studio in a more traditional script...

using (var t = new TransactionScope(TransactionScopeOption.Required))
{
  Users.Context.ExecuteCommand("SET IDENTITY_INSERT Users ON");
  Users.InsertOnSubmit(new Users { Id=1234, Name="Goyuix"});
  SubmitChanges();
  Users.Context.ExecuteCommand("SET IDENTITY_INSERT Users OFF");
}

JoeAlbahari · December 2012

Right now there's no way to tell LINQPad not to apply the AutoGenerated attribute to the identity column. So you'll have to set the querying language to 'SQL' and do it the old-fashioned way!

Halfbeard · September 2016

I'm trying to do this exact same thing.
Just posting to check if there is a way to do it now, since the reply was from 4 years ago?

(great tool, btw, I use it for pretty much everything)

Override IDENTITY_INSERT for use in loading data through LINQ-to-SQL

Comments