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:
- 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
- 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.
I am a program manager in the Visual C# Product Unit of Microsoft. I am currently working on the LINQ project with specific responsibility for DLinq. Previously, I have been in a PM in SQL Server working on ObjectSpaces and DataSet.
In pre-MS life, I have worked for companies ranging from startup to IBM on a wide range of software projects.
Before I started working, I did M.S.E.E. and Ph.D. (CSE) from the University of Notre Dame and B.Tech. E.E. from IIT Bombay, India.
