How to make UPDATE queries in LINQ to SQL?

Question

I like using LINQ to SQL. The only problem is that I don't like the default way of updating tables.

Let's say I have the following table with the following columns:

ID (primary key), value1, value2, value3, value4, value5

When I need to update something I call

UPDATE ... WHERE ID=@id

LINQ to SQL calls

UPDATE ... WHERE ID=@id and value1=@value1 and value2=@value2 and value3=@value3 and value4=@value4 and value5=@value5

I can override this behavior by adding

UpdateCheck=UpdateCheck.Never

to every column, but with every update of the DataContext class with the GUI, this will be erased. Is there any way to tell LINQ to use this way of updating data?

Answer 1

I'm confused by this statement:

but with every update of the DataContext class with the GUI, this will be erased. Is there any way to tell LINQ to use this way of updating data?

By "the GUI", do you mean the Linq to SQL designer? Because the property sheet for each member has an "Update Check" property that you can set to "Never". If you are manually editing the .designer.cs file, don't do that, instead change the Update Check setting in the actual designer.

Please note: The "default way" of updating used by Linq to SQL is called optimistic concurrency, and is a way of preventing conflicting updates from multiple users. If you turn this off by using the method above, you have to be prepared to deal with the fact that if two users have the same record open at the same time, the second user's changes will overwrite the first user's changes without any warning or confirmation. Be sure that this is the behaviour you really want.

Answer 2

Unfortunately, no, there's not. You have to edit the DBML manually after it is generated (or updated) - which is a pain (or use the Designer as already mentioned in the other answer).

When I last used L2S on a project, I wrote a quick utility which ran post-generation and fixed it up, but it's an unnecessary pain which (c)shouldn't be required IMHO.

Answer 3

Ran into this one myself. The trick is to change the way one generates the DBML--such as using l2st4. Then you can set that pesky UpdateCheck property to always be never by modifying the template.

Answer 4

That is how Linq works. Why don't you like this update behavior?

Read about optimistic concurrency

http://msdn.microsoft.com/en-us/library/bb399373.aspx