Edit composite key value using LINQ

Question

I have a table which uses three columns as a composite key.

One of these column values is used as a sequence tracker for ordered related records. When I insert a new record I have to increment the sequence numbers for the related records that come after the new record.

I can do this directly in SQL Server Management Studio, but when I attempt this in LINQ I get the following error:

Value of member 'Sequence' of an object of type 'TableName' changed.
A member defining the identity of the object cannot be changed.
Consider adding a new object with new identity and deleting the existing one instead.

Can anyone suggest a way around this limitation?

(Adding a new record (as suggested by the error message) isn't really an option as the table with the composite key has a relationship with another table.)

Answer 1

Changing primary keys is a "code smell" in my book.

Answer 2

Hi Richard.

I think the compiler is right. The only way of doing this is creating a new record and deleting the old one.

(Adding a new record (as suggested by the error message) isn't really an option as the table with the composite key has a relationship with another table.)

I think there's no problem with this. Just copy all the fields of your entity, set the new sequence, and set also any relation by just assigning the old EntitySet reference to the new one. I tried this and it updates correctly.

Besides of this, couldn't you just create a new ID column with auto-increment? I agree with @ocdecio. I think changing primary keys is poor design ...

Answer 3

I don't know LINQ, but would this work if you have cascading update defined on the SQL Server for the FK relationships?

Mind, I think using a composite key is a bad idea and changing one is a worse idea. The primary key should not change. Too many things can get broken if the primary key changes. And what do you do when the primary key changes and it is now not unique? If you do this, you will need a way to handle that as well because it will happen.

Answer 4

The fix we implemented was as follows

• Deleted the relationship that used the composite key
• Added autoincrement ID field, set that as primary key
• Added Unique contstraint to the three fields that we were previously using as our
• Re-created the relationship using the three fields that were previously our primary key

Answer 5

I have a solution for that (even though I have to admit it is quite a last chance pratice), you can have a look here: http://www.raibaud.com/wordpress/workaround_linq_to_sql_annoying_limitations_part_1 and here also have a look at part 2.

It works around and tweak the LINQ engine but does work perfectly. Benjamin