First Experiences With VS2K8 Part 6 (Disabling Optimistic Concurrency Checks)

It's been a while since I posted and that's because I've fallen a bit behind schedule on my first production ASP.NET LINQ project. In my previous posts I explained how easy it is to bind controls to data with LINQ to SQL. My next step was to be the creation of the data entry portion of the application. And that's when the problems started. I started getting errors from LINQ on updates. Stuff like 'Row not found or changed' and 'An entity can only be attached or modified without original state if it declares a version member or does not have an update check policy.' 'What the hell do those mean?' I thought. So I turned to Google. Turns out that these error messages are the result of a feature of LINQ that is failing. This feature is called optimistic concurrency checking and it works like this: in an ideal situation when an object that has been retrieved from the database by LINQ is updated, LINQ generates an update statement that takes into account both the original and new states of the object by only updating the row in the database to the new state of the object if it still matches the original state of the object. Perhaps an example is in order. Let's say for simplicity's sake that I have a customer table with three columns: CustID (int), Name(varchar) and ModifiedDate(datetime with default of getdate()). Now let's say I ask LINQ for the Customer object with CustID = 1. Let's say I get back the following: { CustID = 1, Name = 'skain', ModifiedDate = '10/2/2007 8:35' } Now let's say I change the Name property to 'skainsez' and then call the SubmitChanges() method on my data context. If I have optimistic concurrency checking enabled on all of my fields then I get an update statement similar to the following: UPDATE customers SET Name='skainsez' WHERE CustID=1 AND Name='skain' AND ModifiedDate='10/2/2007 8:35' Notice the extra constraints in the where clause. In order to get the update done it would have been sufficient to just have said 'WHERE CustID=1' but LINQ added the extra two constraints. In doing so it's effectively telling the database to only update the record if it hasn't changed since we last requested it. This is the heart of optimistic concurrency checking. It helps to deal with concurrency issues that arise when many users are all writing to the same database at the same time. LINQ detects whether the row was successfully updated and can let you know if it wasn't. If it wasn't then you probably need to alert the user and ask them whether they want to overwrite the new data or not. And this is all great. It's built in. The designer turns it all on by default and it just works. It just works that is unless you happen to be working in ASP.NET or any other essentially stateless environment where DataContexts are only instantiated for the length of a request. And since DataContexts must be disposed, if you're in an ASP.NET environment I can't think of any way to handle them other than to instantiate them at the beginning of the request and dispose them at the end. So why is this a problem? Well the problem arises when in one request we get an object for LINQ and populate a data entry form and then on the next we rebuild the object from the form data and then try to update it with a new data context. For instance our data binding method might look like: private void bindData() { using (CustomerDataContext db = new CustomerDataContext()) { Customer c = Customer.GetByID(Convert.ToInt32(Request["CustID"])); bindCustomerToForm(c); } } Our update method (exectued when the user clicks the update button) might look like this: private void updateCustomerFromForm() { Customer c = getCustomerObjectFromFormElements(); using (CustomerDataContext db = new CustomerDataContext()) { c.Update(db); } } I believe that many current ASP.NET apps follow this basic pattern. This assumes that the primary key field (CustID) was written out as a read-only form value and is the same on postback as it was when it was written. Now you'll notice that I'm calling an instance method on my customer object called 'Update'. Let's look at how this method might be implemented: public void Update(CustomerDataContext db) { db.SubmitChanges(); } Now if we were in a stateful application where the datacontext and our objects were not disappearing into the ether this would work fine. But we're in a stateless environment and if we call this version of the Update method no error will be thrown but the database won't be updated either. That's because we created a brand new data context for the purpose of executing the update. This data context doesn't even know about the customer object we created from the form values. Luckily LINQ has a way for us to alert a data context of the existence of a LINQ object: the Attach method. The attach method allows us to pass an object to a data context so that the data context knows to update it when SubmitChanges is called. So it should be pretty easy to modify our Update method to call it: public void Update(CustomerDataContext db) { db.Customers.Attach(this); db.SubmitChanges(); } This compiles and runs just fine. But again no update in the database occurs. What gives? Well I did some digging and I came to the conclusion that no updates were occurring because no changes were made to the object after it was attached so maybe I needed to attach and then set my properties. This meant some kind of ugly code but I figured if it worked I could try and pretty it up later. So I tried: public static void Update(CustomerDataContext db, Customer newCustData) { Customer newCust = new Customer(); db.Customers.Attach(newCust); newCust.CustID = newCustData.CustID; newCust.Name = newCustData.Name; newCust.ModifiedDate = newCustData.ModifiedDate; db.SubmitChanges(); } But when I tried this I got an InvalidOperationException: 'Value of member 'CustomerID' of an object of type 'Customer' changed. A member defining the identity of the object cannot be changed. Consider adding a new object with new identity and deleting the existing one instead.' Adding a new one and deleting the existing one? That sounds a little crazy so I figured this must not be the right way. Did some more digging and noticed that the Attach method has an overload that accepts a bool called 'asModified'. Looking this up on MSDN (which is really scanty for LINQ currently) provided the following definition and nothing more: 'True if the entity is to be attached as modified.' Hmm. 'Well,' I reasoned, 'I do want the entity to be attached as it's been modified.' So I went back to my simpler version of Update and added a 'true' like so: public void Update(CustomerDataContext db) { db.Customers.Attach(this, true); db.SubmitChanges(); } I have to admit I thought this would work. But it didn't. I got a different InvalidOperationException: 'An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.' Hmm. 'if it declares a version member or does not have an update check policy.' What could that mean? Well, first I tried playing around with timestamps thinking that maybe that's what 'version member' means. But I won't waste your time with that because I just couldn't get them to work at all. Next I started looking into that whole 'update check policy' thing. I did some more digging and found that this is most likely referring to optimistic concurrency checking as I described at the beginning of this article. But how could I make it so that my entity wouldn't have an update check policy and would that even solve my problem? Turns out that every field of a LINQ to SQL created entity has an attribute on it called UpdateCheck and by default it is set to a value of Always. For every field on every table. What I found out is that if you click on every field (including your primary key field) and in its properties window set the Update Check property value to Never then the last version of the Update method works just fine. No errors and the update happens in the database like you'd expect it to. But there's also no concurrency checking going on so the last person to hit update is going to be the one whose changes actually show up in the database. Not a horrible situation but wouldn't it be nice if there was a solution here that allowed for optimistic concurrency checking in stateless environment? I can't say for sure right now whether it's a problem in the beta version, whether I'm just doing it wrong or whether optimistic concurrency checking just doesn't make sense in a stateless environment. What I do know is that by turning it off I was able to get my project back on schedule. Which at this point is pretty important seeing as I went out on a bit of limb here using LINQ for a project with a real deadline. But that's me. I love to live a life of danger!