only update 1 column with linq

Question

Hi,

I'm using linq to retrieve a row from a table in the database. Now I only update 1 column. Then I update it back into the database.

this goes well, except for the times any of the other fields is changed by another process/thread/user In that case I get an exception (optimistic concurrency), telling me to look out, values have been changed since I last got the object with linq.

Since I'm only interested in this 1 column, can I tell linq to just update this column and ignore the others? (and warn me this one column has indeed been changed)

R

Answer 1

It sounds like you may be using the DataContext in a singleton pattern, or at least sharing it across multiple threads.

When you call SubmitChanges on your DataContext is will do all the database changes which the DataContext knows about.

The DataContext is meant to be used only as a unit-of-work object and disposed of as soon as you've completed that piece of work.

Answer 2

I don't know this too well, but looks like you could use the UpdateCheck.Never enum value on the ColumnAttribute to avoid the concurrency check completely.

MSDN

Answer 3

I found this. Is that what you looked for?

If that doesn't solve the problem, you can always build an sql update statement and execute it.

Answer 4

There is a good article in MSDN:

Simultaneous Changes

The following example (a little further in the article) may be of special interest:

public partial class Northwind : DataContext
{
   ...

   public void UpdateProduct(Product original, Product current) {
      // Execute the stored procedure for UnitsInStock update
      if (original.UnitsInStock != current.UnitsInStock) {
         int rowCount = this.ExecuteCommand(
            "exec UpdateProductStock " +
            "@id={0}, @originalUnits={1}, @decrement={2}",
            original.ProductID,
            original.UnitsInStock,
            (original.UnitsInStock - current.UnitsInStock)
         );
         if (rowCount < 1)
            throw new Exception("Error updating");
      }
      ...
   }
}

with your stored procedure being:

create proc UpdateProductStock
   @id               int,
   @originalUnits    int,
   @decrement         int
as
UPDATE Product 
SET originalUnits=@originalUnits,
    decrement=@decrement
WHERE id=@id

In my experience, the best way to go is making your own stored procedure. Linq doesn't offer an easy way to do simultaneous changes, unless you set "UpdateCheck" properties from (almost) all your columns to "Never".

Answer 5

You can do this, but it requires that you structure your code in a certain way.

For example you have a description and an amount that needs to be updated individually. You would then create 2 methodes:

• updateAmount which takes key and amount.
• udateDesription which takes key and description.

Then within for example the updated amount methode:

• create a serializable transaction
• read the row from the database
• updated the amount in memory
• save the record to the database
• commit the transaction

When you do it like this, you only change what you intended to change.

The times when we have used this pattern has been due to auditing requirements. You are able to say that this user changed the amount and this user changed the description. Not that each user changed the record and you need to look at the data to see what they did.

Answer 6

I read an article talking about the possibility of changing the UpdateCheck attribute at runtime. (Filed it away under the 'I might need that some day' category, but so far I haven't needed it.)

See

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/12/20/10038.aspx

Answer 7

If you have access to change the DB schema, add a timestamp column to the data table in question, then ensure that no other column is set for concurrency updating (it should automatically pick up on the TimeStamp column, but may need to regenerate the table in your dbml if not). When you have a TimeStamp column, LINQ to SQL will only use that field when doing concurrency checks, thus allowing for a more discrete update.

Answer 8

You can detect and resolve your concurrency issues by catching a ChangeConflictException:

using (var db = new MyDataContext())
{
    var row = db.MyTable.Single(x => x.Id == tableId);  // Getting the row

    row.Column = columnNewValue;  // Assign the new value

    try
    {
        db.SubmitChanges();
    }
    catch (ChangeConflictException)
    {
        db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
        db.SubmitChanges();
    }
}

With RefreshMode.KeepChanges all the changes made to your client objects will persist, and the changes from other users/processes/threads, on other columns will be merged.

In your case, only your column will be changed.

Recommended articles:

• Optimistic Concurrency Overview
• LINQ To SQL Samples - Optimistic Concurrency
• Resolve Concurrency Conflicts by Merging with Database Values (LINQ to SQL)

Answer 9

using the PLINQO framework at http://plinqo.com, you can update one column if you like using the batch operations that are a part of PLINQO.

context.Task.Update(t => t.Id == 1, t2 => new Task {StatusId = 2});

This will perform a Update Task Set StatusId = 2 Where Id = 1