When updating a data table in SQL Server using LINQ to SQL and the SubmitChanges() method you may encounter the following exception:
System.Data.Linq.ChangeConflictException: Row not found or changed.
at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
The error in itself is not too hard to understand, you are simply trying to update by using an UPDATE statement that doesn’t update anything.
The most common reason is that someone (or something) has changed the data in the database between you retrieving the data and you updating the data.
What happens in the background when you call SubmitChanges is that there is an inspection of the rows (objects) in the data context data table.
If it detects any changes, it builds an SQL statement based on the values that it have and then it executes this statement.
This statement is basically taking the values that were retrieved from the database and then uses them in the WHERE clause of the UPDATE, however, this UPDATE will fail
of course if values have changed before you execute the UPDATE, it will be a bit clearer below, this approach is called optimistic concurrency and this is what LINQ to SQL uses.
Now, the problem is that the exception is not very verbose. You may have several tables in your dbml with many columns and rows.
So what column value has changed (and in what table) and is causing us trouble. As usual, let’s do this with a demo.
First create a new table in your SQL Server and insert some rows:
create table Employee (Id int primary key, FirstName nvarchar(20), LastName nvarchar(20), Location nvarchar(20))
insert into Employee values (1, 'Mike', 'Spike', 'Stockholm')
insert into Employee values (2, 'John', 'Johnsson', 'London')
insert into Employee values (3, 'Peter', 'Peterson', 'Paris')
insert into Employee values (4, 'Lars', 'Larsson', 'New York')
Then create a new C# console application. Then Add->New Item->LINQ to SQL classes, call it Employee.dbml
Drag the table you just created from your server onto the designer and Save.
Our objective is to change the location for employees in London to have their location set to Paris.
Our problem is that between we have retrieved the data and made the change and submitted it, a quicker colleague has already done this.
So when we load the data, the London location still exists in the database, when we save it, it does not.
Then the code for the application should be:
static void Main(string[] args)
{
EmployeeDataContext edc = new EmployeeDataContext();
try
{
// Get all Employees that work in London.
var empInLondonQuery = from e in edc.Employees where e.Location.Equals("London") select e;
// Write out employess were are changing location for:
foreach (var Employee in empInLondonQuery)
{
Console.WriteLine("Changing location for {0}, {1}:", Employee.LastName, Employee.FirstName);
// Change location
Employee.Location = "Paris";
}
// Now emulate the other user by changing the Location before we submit our change.
Console.WriteLine("Now update the Employee table by running this is SQL Server Management Studio:");
Console.WriteLine("\t update Employee set Location = 'Paris' where Location = 'London';");
Console.WriteLine("And hit any key...");
Console.ReadKey();
// Finally submit the changes
edc.SubmitChanges();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
Run it, update the database when prompted, and you should get the “Row not found or changed” exception.
Not very descriptive. So in order to see what is going on, we can first enable the Log for the data context so that we can see what SQL is being used,
secondly we can catch the ChangeConflictException and then query the ObjectChangeConflict and MemberChangeConflict classes.
So, change the code above to look as follows:
static void Main(string[] args)
{
EmployeeDataContext edc = new EmployeeDataContext();
edc.Log = Console.Out;
try
{
// Get all Employees that work in London.
var empInLondonQuery = from e in edc.Employees where e.Location.Equals("London") select e;
// Write out employess were are changing location for:
foreach (var Employee in empInLondonQuery)
{
Console.WriteLine("Changing location for {0}, {1}:", Employee.LastName, Employee.FirstName);
// Change location
Employee.Location = "Paris";
}
// Now emulate the other user by changing the Location before we submit our change.
Console.WriteLine("Now update the Employee table by running this is SQL Server Management Studio:");
Console.WriteLine("\t update Employee set Location = 'Paris' where Location = 'London';");
Console.WriteLine("And hit any key...");
Console.ReadKey();
// Finally submit the changes
edc.SubmitChanges();
}
catch (ChangeConflictException cce)
{
Console.WriteLine(cce);
foreach (ObjectChangeConflict occ in edc.ChangeConflicts)
{
MetaTable metatable = edc.Mapping.GetTable(occ.Object.GetType());
Console.WriteLine("\nTable name: {0}\n", metatable.TableName);
foreach (MemberChangeConflict mcc in occ.MemberConflicts)
{
Console.WriteLine("Member: {0}", mcc.Member);
Console.WriteLine("\tCurrent value: {0}", mcc.CurrentValue);
Console.WriteLine("\tOriginal value: {0}", mcc.OriginalValue);
Console.WriteLine("\tDatabase value: {0}", mcc.DatabaseValue);
Console.ReadLine();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
and rerun it (you have to change the Location back to London, otherwise there will be no rows selected).
Now the output should be as follows:
SELECT [t0].[Id], [t0].[FirstName], [t0].[LastName], [t0].[Location]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[Location] = @p0
-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926
Changing location for Johnsson, John:
Now update the Employee table by running this is SQL Server Management Studio:
update Employee set Location = 'Paris' where Location = 'London';
And hit any key...
UPDATE [dbo].[Employee]
SET [Location] = @p4
WHERE ([Id] = @p0) AND ([FirstName] = @p1) AND ([LastName] = @p2) AND ([Location] = @p3)
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p1: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [John]
-- @p2: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Johnsson]
-- @p3: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]
-- @p4: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Paris]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926
System.Data.Linq.ChangeConflictException: Row not found or changed.
at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
at SubmitChangesError.Program.Main(String[] args) in C:\_Blog\Projects\SubmitChangesE
Table name: dbo.Employee
SELECT [t0].[Id], [t0].[FirstName], [t0].[LastName], [t0].[Location]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[Id] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926
Member: System.String Location
Current value: Paris
Original value: London
Database value: Paris
This is a bit more verbose, first we can see how the data is selected from the database (all rows where Location is London).
Then we can see the actual update statement, here you will see that the update is built based on all columns using the values that they got when it was retrieved.
Then we get the exception which we then query.
Here we can see that the data table that we are interacting with is Employee.
Then we query the database to get the current value using the primary key.
Finally we display the current value (what we have changed it to), the original value (what it was when we retrieved it) and the database value (what it is in database).
Following the steps above should help you in figuring out what has changed in your database, causing the ChangeConflict exception.
Maybe this is ok for you and you think that whatever is in the database should stay there. You can then call the SubmitChanges method with the ConflictMode set to ContinueOnConflict.
Or you can, after the exception has been caught, call the ObjectChangeConflict.Resolve method. Links related to this below.
"LINQ to SQL - Optimistic Concurrency Overview (LINQ to SQL)"
http://msdn.microsoft.com/en-us/library/bb399373.aspx
"LINQ to SQL - How to: Retrieve Member Conflict Information (LINQ to SQL)"
http://msdn.microsoft.com/en-us/library/bb386985.aspx
"LINQ to SQL - How to: Manage Change Conflicts (LINQ to SQL)"
http://msdn.microsoft.com/en-us/library/bb399389.aspx
"ObjectChangeConflict.Resolve Method"
http://msdn.microsoft.com/en-us/library/system.data.linq.objectchangeconflict.resolve.aspx
"DataContext.SubmitChanges Method (ConflictMode)"
http://msdn.microsoft.com/en-us/library/bb345081.aspx
