How to update a single column in LINQ without loading the entire row?

Question

In LinqToSql, it is very easy to load a row, change a column, and make changes to the database:

using (MyDataContext wdc = new MyDataContext()) { Article article = wdc.Article.First(p => p.ID == id); article.ItemsInStock = itemsinstock; wdc.SubmitChanges(); } 

The only drawback: the article is huge. To download the entire article, just refresh one column - this is the overkill path and will significantly slow down my application.

Is there a way to update a single column using LINQ without loading the whole row?

Now I'm going back to using ExecuteCommand, where speed makes sense, but it's ugly and error prone:

 wdc.ExecuteCommand("UPDATE Article SET ItemsInStock = @1 WHERE ID = @2", itemsinstock,id); 

Answer 1

ligget78 gave me another idea on how to update a single column:

Create a new DataContext only for this type of update and include only the columns you need in this DataContext.

Thus, unnecessary columns will not even be loaded and, of course, will not be sent back to the database.

Answer 2

You need to set UpdateCheck for all properties of the Article class, except the primary key (click on the class property in the LINQ2SQL constructor and switch to the "Tool Properties" window) to "Never" (not sure if WhenChanged may work too) and experiment with it!).

This will force LINQ2SQL to use

 UPDATE ... SET ... WHERE ID = @2 

instead of the long version with all the columns in the WHERE clause:

  UPDATE ... SET ... WHERE ID = @2 AND ItemsInStock = @1 AND SomeOtherColumn = @3 AND... 

Now you can use code like

 context.Articles.Attach(article /* article with updated values */, new Article { ID = articleID, ItemsInStock = -1 } /* pretend that this is the original article */); context.SubmitChanges(); 

Basically, you indicate that only the ItemsInStock property has changed - other details should have the same default value, of course, the article identifier.

NOTE. You do not need to retrieve the article before this.

Answer 3

it works well ExecuteCommand ("UPDATE tIdx_TicketActivity SET Archive = {0} WHERE ExpiryTimeStamp