Updating entities using any object-relational mapper can be difficult for at least two reasons: (1) the number of update options that these ORM frameworks provide and (2) the complexity of requirements in today’s business scenarios. With this post I want to discuss a few of the ways that you can update entities in LINQ to SQL to fit your particular scenario.

What Do I Need?

Before we jump into code, you have to consider how your product requirements influence your update decisions. There are two questions you can ask to help:

1. Can multiple clients update the same database record at the same time?
2. If this can happen, what data is saved when multiple simultaneous updates occur?

Question 1′s answer is usually yes, but there are some environments where it is no, such as single-user applications and applications where users own specific data that nobody else can update.

Question 2 is a bit more complicated. Consider a web application that allows customers to choose their desired seat(s) on a commercial flight. The typical use case is that the customer chooses his seats and successfully submits his choice, but what is the expected behavior when two people choose the same seat and click submit at the same time? Clearly, the customer whose seat choice was saved first should win, and the application should ask the "losing" customer to make another choice.

Although less common, it’s also possible for the last update to overwrite all previous changes. This can be very dangerous, especially if the users whose updates were overwritten aren’t notified. There is also a balance, where the last update wins only if a particular column or set of columns were updated.

I have targeted the discussion of these questions so far toward optimistic concurrency since that is the only concurrency control that LINQ to SQL supports. It is interesting to contrast it with pessimistic concurrency, but it is out of scope for this post.

What Can LINQ to SQL Offer?

Once you’ve established what you need, it’s easier to match your requirements with what LINQ to SQL offers. But what does LINQ to SQL offer? Here we’ll look at a few simple use cases for updates.

Retrieve Existing Entity, Change Properties, Update

This is a popular pattern in many object-relational mappers to update entities. The code below shows an example of how to update a Product in the Northwind database using this technique.

 

When the application calls the SubmitChanges method, LINQ to SQL knows what specific properties it needs to update in its SQL statement. This is because the DataContext is aware of the Product entity since it was retrieved from the database in the first place.

Note that this approach requires two separate calls to the database—one to select the Product entity and one to update it. In highly contentious situations, it is possible that another client can change that Product entity in between these two database calls. In that case, LINQ to SQL will either allow the last update or will throw a ChangeConflictException with the message "Row not found or changed." Which happens when? The answer is "it depends."

If you drag tables from the Server Explorer to your DBML designer and start using LINQ to SQL without changing the designer, then your update from the code above will look like this. (To get this information for your queries, set the DataContext.Log property to Console.Out. Alternatively, you can try a few other listeners, courtesy of Damien Guard.)

 

In most cases, this doesn’t match what you expect. However, it does give you a clue why the ChangeConflictException’s message is "Row not found or changed." If the WHERE clause of the SQL UPDATE statement filters out the row you’re looking for, then no update occurs. And the only case when the WHERE clause would filter it out is when another client has updated one of the properties on the Product between the time it was retrieved and the time it was updated.

So what if you don’t want this behavior, and you just want to compare the values of the primary keys? This is also a simple change. Navigate to your entity type (Product) in the DBML designer and select all the columns that you do not want to participate in optimistic concurrency. Then go to the Properties window and change the "Update Check" property to Never. See below for details.

Re-running the LINQ to SQL code shown above will now yield much smaller SQL, at the cost of eliminating concurrency checking.

 

You may notice another property in the property grid called "Time Stamp." If you have a timestamp column in your table, and its Time Stamp property is set to "True," then Update Check on other properties will be ignored.

A quick summary of this information:

1. If you have a column whose "Time Stamp" property is true, then only that column will be used for optimistic concurrency checking.
2. Otherwise, only columns whose "Update Check" property is set to "Always" (or "WhenChanged" and they are changed) will be used for optimistic concurrency checking.
3. Primary keys are always used as part of the WHERE clause to identify the target tuple in the database.

Attach an Entity, Change Properties, Update

We can now use this overview of concurrency checking in LINQ to SQL to demonstrate the next technique, which does not require two round trips to the database. It is important, however, that you have all the information that LINQ to SQL needs for concurrency checking before using it.

 

It’s the same basic pattern as before, but instead of allowing the DataContext to retrieve the Product entity from the database, we put it there ourselves with the Attach method. You have to be very careful with Attach and default values. If I changed the code to set product.UnitsInStock to 0 instead of 14, the update wouldn’t occur because there would be no property change. Before the Attach, product.UnitsInStock is 0 (the default value for an Int32); before SubmitChanges, product.UnitsInStock is still 0. There was no change to the value, and thus there will be no update of the UnitsInStock column when SubmitChanges is called. To get around this you can try using one of the two other overloads for Attach. The first overload is useful for when you have both the old entity and the new entity. The code below shows how this works.

 

When Attach(entity, original) executes, LINQ to SQL sees what properties have different values between the original entity and the current entity and uses them to update the tuple in the database when SubmitChanges is called. Again, it’s critical that the original entity (oldProduct, in this example) has all of its properties that participate in optimistic concurrency set to the values that were originally retrieved from the database.

You can use the third overload of the Attach when no properties participate in optimistic concurrency or the only property that does represents a timestamp column. If the latter is true, then the timestamp property’s value must match the value in the database for the update to be successful. This Attach overload also tells LINQ to SQL that every property has changed on the entity. This gets around the problems posed by the previous two Attach overloads, but you must now make sure that you set every property; otherwise, the unset properties will be set to NULL or the appropriate default value for the type as part of the update.

 

The generated SQL for the code above reveals the following:

 

In this case it’s actually more difficult to apply only the changes we want, and we might as well use one of the other Attach overloads. Also, you should note that there is functionally no difference between Attach(entity) and Attach(entity, false).

Bringing It All Together

Now you know what your requirements dictate for updates as well as the factors involved in updating records with LINQ to SQL, we need to bring all this information together. Remember those questions I asked in the beginning? Here they are again just in case.

1. Can multiple clients update the same database record at the same time?
2. If this can happen, what data is saved when multiple simultaneous updates occur?

If multiple clients can’t update the record simultaneously, then you can use any of the options that I discussed in the previous section and not worry about optimistic concurrency. The "best" choice depends on your scenario. For example, if you have a smart client with a DataContext that is long-lived, then you may not even need to worry about which scenario you use. The DataContext will track the changes to the object on the UI and then you can call SubmitChanges immediately the code reaches your data layer. But if your DataContext is short-lived, then Attach(entity, true) may be useful if you retrieve the entity from one context and update it with another. In stateless environments (like ASP.NET), Attach(entity) and retrieving the entity from the database to update are probably the easier options. But beware the caveat with Attach!

If multiple clients can update the same record simultaneously, you will need to carefully consider which data can be safely overwritten and which cannot. You can then configure LINQ to SQL to recognize your choices with the "Update Check" property in the DBML designer. Attach is then probably the better option, because if your optimistic concurrency checks fail, then a ChangeConflictException is thrown to alert you. If you retrieve the entity from the database and then update its properties, you must explicitly check for concurrency violations. Otherwise, the risk is always present that you overwrite data that you don’t want to.

In this post I haven’t discussed how to resolve conflicts when LINQ to SQL throws a ChangeConflictException. There are a lot of useful links located here that do just that, in particular the three last how-tos: how to resolve concurrency conflicts by retaining database values, by overwriting database values, and by merging with database values.

I hope this has been a useful source of information on updates in LINQ to SQL. Of course, this covers only the APIs themselves and doesn’t discuss how you would integrate this into, for example, an n-tier ASP.NET application. This is something I’m definitely interested in doing for a future post.

EDIT: I made a few changes to this post to make a possible pitfall with Attach more explicit. You should definitely consider this before choosing an update strategy. Thanks to Damien Guard for pointing this out.