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

Question

In LinqToSql, it is lovely easy to load a row, change a column, and submit the 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: Article is huge. To load the entire article, just to update one column is way overkill and slows down my app significantly.

Is there a way to update a single column using LINQ, without having to load the entire row?

Right now I revert to using ExecuteCommand where speed is of essence, but this is ugly and error prone:

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

Answer 1

You need to set UpdateCheck on all properties of the Article class except the primary key (click on the class property in LINQ2SQL designer and switch to Properties Tool Window) to Never (not sure about WhenChanged, maybe that works too - go ahead and experiment with it!).

This will force LINQ2SQL to use

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

instead of the long version with all 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 ItemsInStock property has changed - other props should have the same default value, articleID of course being the same.

NOTE: you don't need to fetch the article prior to that.

Answer 2

ligget78 gave me another idea how to make an update of a single column:

Create a new DataContext just for this kind of update, and only include the needed columns into this DataContext.

This way the unneeded columns will not even be loaded, and of course not sent back to the database.