Update without first selecting data in LINQ 2 SQL?

Question

How can you go about updating a record without having to select the data first in LINQ?

As you must first perform a linq select (obviously calls a SQL SELECT which is costly), change required properties, then perform SubmitChanges(). I wish to avoid this and just perform a SQL UPDATE, I already know the ID.

Answer 1

You need to use the Attach method to "attach" a disconnected entity to your table. This article on MSDN outlines how to use it:

Data Retrieval and CUD Operations in N-Tier Applications

Answer 2

This is going to be very hard to do unless you set your objects up to use optimistic concurrency (all columns to nocheck, I think). Using any sort of concurrency you'll need both the old values and the new values, at least for the columns that are checked. Alternatively you could create and map a SQL function/stored procedure for the update or execute the SQL directly using ExecuteCommand as @Kyralessa says. I think Detach/Attach works best when paired, i.e, get the object, detach it, use it, and then reattach and save it when you are done.

Answer 3

I haven't tried it, but it looks as if you can do this with Attach:

Table(TEntity).Attach Method (TEntity, Boolean)

You set up the object in its updated state and pass it in, setting the boolean param to true. Of course, then you have to set all the fields, not just the ones you want to change.

If you only want to change a field or two, you can call SQL directly with the ExecuteCommand and ExecuteQuery methods on a DataContext object. You have to pass in the SQL as a string, and one argument for each parameter in the SQL. Note in particular how the SQL string has to be constructed:

The syntax for the command is almost the same as the syntax used to create an ADO.NET DataCommand. The only difference is in how the parameters are specified. Specifically, you specify parameters by enclosing them in braces ({…}) and enumerate them starting from 1. The parameter is associated with the equally numbered object in the parameters array.

Answer 4

PLINQO implements entity Detach and will allow you to detach an entity from one context and then re-attach it onto another context. It also has the ability to directly execute updates and deletes without retrieving the entities first. For more information, you can look at the website http://www.plinqo.com/