LINQ Error: Cannot insert explicit value for identity column in table ” when IDENTITY_INSERT is set to OFF

December 4, 2009

Just a small tip after getting a bit caught up with this error yesterday. I know what this error means – Its a SQL error – It means your code is trying to insert a value into an indentity column, which of course should be left alone – Such a column normally auto-increments when a new line is inserted.

How did I get caught up with this one? Having checked the database table, I was lost – I had one identity column, and my LINQ query was touching this. After some thought, I sussed it – The table definition on the LINQ2SQL surface (.DBML File) was not up to date – I previously had another column set to identity, which I had later changed in the database, but not updated the related LINQ class, so when the code ran, we get the error.

Solution – Delete the table from the LINQ2SQL surface (.DBML File) and re-add it. Beware that you’ll lose any changes you might have made on your DBML, although personally I like to keep the database table and related LINQ class identical, just to save confusion.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: