CODEWITHSUNDEEP
c#.netsql-serverlinq
Richard Ev
Richard Ev

Reputation: 54137

Edit composite key value using LINQ

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.)

Upvotes: 2

Views: 5532

Answers (5)

Ken
Ken

Reputation: 11

I worked around this by using a SQL stored proc to update one of the primary keys and calling it from LINQ.

Upvotes: 1

Richard Ev
Richard Ev

Reputation: 54137

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

Upvotes: 3

HLGEM
HLGEM

Reputation: 96640

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.

Upvotes: 0

bruno conde
bruno conde

Reputation: 48255

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 ...

Upvotes: 0

Otávio Décio
Otávio Décio

Reputation: 74300

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

Upvotes: 4

Related Questions