LINQ's pathetic handling of composite keys

Question

I have a table that has a composite key that consists of two int fields and one varchar(50) field. I made the composite key my primary key, which I don't usually do. Yes, yes, yes, I'm more than familiar with the logical key vs. surrogate key debate, but I must have done some heavy drugs (not really) the day that I made the table since I went with the logical key approach instead of using a surrogate (which I almost always do).

My problem: LINQ won't let me do an update on the varchar(50) column that is part of the composite key. It throws the exception "The property 'Value' is part of the object's key information and cannot be modified." 'Value' is the name of the field (and please don't lecture me on using reserved words for column names... like I already said, I was on serious drugs the day of).

So where does this leave me, besides between a rock and a hard place? The table in question actually does have a unique, identity column (even though it's not the primary key), and this column is being used in a foreign key to another table. So, duh, I can't very easily do some hack like delete the record in question and re-add it, because that would force me to keep track of the fk's in other tables and then re-add them as well. What a nightmare...

Anyone know an easy way to get around this issue without forcing me to make extensive changes to my table structure?

Answer 1

If you already have a unique IDENTITY column on that table, then it should be your primary key. Primary keys should always be a choice only between the minimum sized candidate (unique) keys in a table, and should almost always be immutable.

You don't even have to make your IDENTITY column the PRIMARY KEY on the database, you can just make it the key column in your Linq to SQL model, and everything should work just fine.

Answer 2

The "primary key" that you define on your Linq to SQL model does not actually have to be the primary key in the database, it is just used by the DataContext to equate object instances. Linq to Sql knows nothing about the underlying database structure other than what you tell it. So if you edit your DBML file in the Visual Studio designer, you can change your "primary key" (for the purpose of the model) to be the unique identity key that you already have on your table. This should allow you to update your varchar column with Linq to Sql.

Answer 3

If you're trying to update a column that forms a composite key, I'd say your table is broken anyway, and it's not LINQ that is broken. Tell LINQ that your identity column is your key, or use a SPROC to do the update that is required.