LINQ to SQL Tips 7: Minimal update when you don't want optimistic concurrency check

The common guidance for updates is to keep the original values in view state so that you can recreate the object in its original state, Attach() it and then set the values that the user has modified. This works well for the most part. But at times, a user is allowed to to set only certain values (e.g. Product.UnitsInStock) and is allowed to set them without fear of concurrent change (i.e. optimistic concurrency conflict).

 

There are two parts to the generated SQL update command:

1. SET clause: This is always minimal; i.e. only the changed columns are included by comparing original and current versions of the object. Hence, all properties don’t have to be set
2. WHERE clause: By default, L2S (designer or SqlMetal) assume that all columns participate in opt concurrency and generates a full WHERE clause. What you seem to want in the example below is turning off (or limiting) optimistic concurrency. That option is available but tedious in the designer. For each class member, UpdateCheck can be set to “Never” in the property grid. This is not possible through SqlMetal (unless there is a column of timestamp type in which case, that is the only column used in opt concurrency check)

The code below _simulates_ what you would do with just a few (or one) values available in post-back. Please see this post before using Attach() in a simple 2-tier app. Here, I manually set UpdateCheck to Never for all Product class members one by one in the designer. As a result, the update for UnitsInStock succeeded even though I don’t have the original values.

 

            using (NorthwindDataContext db = new NorthwindDataContext(@"C:\Northwind\Northwnd.mdf"))

            {

                db.Log = Console.Out;

     

    // simulate minimal product information available by creating a new one

                Product prod = new Product();

                // ID is essential and cannot be skipped

                prod.ProductID = 8;

                db.Products.Attach(prod);

                // Change just one property

                prod.UnitsInStock = 11;

                db.SubmitChanges();

            }

 

Of course, you must think through very carefully if you want optimistic concurrency check or not. It depends on the semantics of your application.

As an aside, if the designer only provided a single class level property to turn off UpdateCheck for all members, the above solution would be much more palatable. Right now it is a bit tedious when you use the designer.

Comments

• Anonymous
  June 13, 2008
  J'avais blogué sur les 5 premières astuces de Dinesh Kularni . Voici les 3 suivants : LINQ to SQL Tips

• Anonymous
  June 23, 2008
  You can shift-click multiple properties(columns), including Update Check, in the designer and change them all at once. This is still tedious if you have a bunch of tables, but better than going row by row. I was not sure from your post whether you had found this...