views:

3535

answers:

2

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,

A: 

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

TFD
I appreciate your post, but I have 3 quick points... 1) The "surrogate key" example is putting bad design to fit your bad technology :)... 2) L2S isn't a dead product... 3) EF has this exact same limitation as L2S.
Timothy Khouri
In answer.1. It's a POV. I think manually updating a autogenerated key is bad design. Having an alternate key is a good pragmatic choice depending on the need to 'fudge' the key. Typically matching keys in a legacy system. What is you reason?2. L2S is in cardic arrest, and smells bad already
TFD
3. Yes, EF has same problem, as should any product using SQL Identity. The Identity Insert feature is there to do once off loading of legacy data hence the 'sysadmin' role requirement
TFD
These sorts of lectures are not helpful - feel free to express yourself in a comment but this is not a useful answer. In the real world there are a number of reasons why you would temporarily want to force identity fields to specific values, for example when importing data from another system. That's why IDENTITY INSERT exists.
Herb Caudill
Whatever..... I notice someone bothered to upvote my supporting comments!!!
TFD