LINQ to SQL Concurrency - ChangeConflictException Row not found or changed
LINQ to SQL Concurrency - ChangeConflictException Row not found or changed
If we create a small model of the Northwind database, and we try to modify one of the customers (lets take ALFKI for example):
NorthwindDataContext db = new NorthwindDataContext();
Customer alfki = db.Customers.Single(c => c.CustomerID == "ALFKI");
alfki.City = "London";
db.SubmitChanges();
We will see that the update command that is created by the run time is:
UPDATE Customers
SET City = 'London'
WHERE CustomerID = 'ALFKI' AND
CompanyName = 'Alfreds Futterkiste' AND
City = 'Berlin' AND
Region IS NULL
Of course, the update command updates the City column in the Customers table according to the appropriate CustomerID value, but what with all these other conditions in the where clause?
LINQ to SQL uses Optimistic Concurrency strategy. When it selects records, it does not perform any locking on the selected rows. Only when it tries to update records in the database or delete them, it checks that the records were not changed since the last time they were selected. How does it do it? It tries to update the row that matches the original values, as the DataContext got them when it selected the rows. This explains the additional conditions on the update command.
If during the time the logic was done, updates were made from another program (or another DataContext for that matter), an exception will be thrown:
System.Data.Linq.ChangeConflictException was unhandled
Message="Row not found or changed."
Source="System.Data.Linq"
LINQ to SQL tried to look for the record to updated comparing all the columns to their original values, and when no records where found, the exception was thrown. This is the way LINQ to SQL tells us that the row it was looking for was deleted or changed by another program and the update cannot be done.
Enjoy!