I see a lot of questions and articles about doing CRUD operations with LINQ to SQL (DLinq), especially about Updates, DAL design, etc., and think a little guidance could help a few people…
This article only focuses on update scenarios, typically encountered with web/async apps.
Let’s start with a simple scenario without Transactions or multiple Data Contexts.
Say we want to update a Person’s name in our Employee database, from our app.
First retrieve the Person row, then Update it
dbPerson p = (from p in myContext.Persons where p.Id = myId select p).First() p.Name = “foo”; myContext.SubmitChanges();
Note: Do not use Attach unless your database object, in this case p, comes from another Data Context instance. If it is in a single unit of work, there is no need to use Attach, and it can get pretty confusing if used incorrectly.
In the update above, LINQ takes an optimistic concurrency approach under the covers, and generates a SQL with a complex Where clause, that essentially compares all the fields from your passed in object to the ones in the database.
This provides a concurrency check, whereby if another user snuck in and updated the Person record, the update will fail because the old value checks will detect that the underlying SQL data has changed after the fetch.
This is a good thing, but there is an overhead of the Where clause, which is proportional to the number of columns. So, if you had 30 columns in the Person table, the Where clause would include 30 comparisons.
Although this might be jarring, if the Update is not used that often a little overhead is fine, and that’s the price you pay for the convenience of LINQ.
However, at the other extreme end, if the Update operation is on a critical path and needs to be perform like a dragster with clean pipes, use a stored procedure instead.
As an Architect, I recommend that you first understand your app’s profile because software design is always subjective.
As shown, select it in the OR/M designer and View Properties.
Set the Timestamp property to True, as shown.
Now, DLinq will use this column for concurrency checks instead of the in-efficient Where clause
Remember to set the default value of the timestamp column in your database to getUtcDate() or something, and don’t update this column with LINQ.
Note the Property values that are there. They can be set to improve the efficiency for different scenarios. For e.g. I set Auto-sync to Never because I don’t update an Entity multiple times, so I don’t need it to be updated in LINQ every time.
In the future, I will explore Transactions and multiple Data Contexts when it comes to DAL design.