How do I MANUALLY set an Identity field in LINQ-To-SQL (IDENTITY INSERT)

Question

I have a table that normally, upon insert, the auto-key will increment. But, there are some instances when we want to set the ID (as would easily be done with "IDENTITY INSERT" in SQL).

Is there a way to accomplish this with LINQ to SQL?

Thanks,

Answer 1

Take a look here: http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/566e9540-911e-48b4-ac31-f69c0ab9f7fb/ Last reply here: http://forums.asp.net/t/1208607.aspx

Answer 2

Yes you can hack this, but this just smells of bad design. If you want identity then let the RDBMS handle it. If you want custom ID's then you handle it. Mixing the two is a recipe for disaster

Consider using a surrogate key. e.g.

create table MyTable ( Id int primary key identity(1,1), OtherKey int not null, MyData varchar(100), )

You can then set OtherKey to special values when you need them

Also, Linq2SQL is a dead product, consider moving to EF or maybe Mindscapes LightSpeed product which handles also this in a much better way. Check out keytable pattern

http://www.davidhayden.com/blog/dave/archive/2009/01/14/ORMapperIdentityGenerationKeyTableReduceRoundtripsImprovePerformance.aspx