LINQ to SQL "1 of 2 Updates failed" on "SubmitChanges()"

Question

I am updating an object of type X and its children Y using LINQ to SQL and then submitting changes and getting this error

Example Code

X objX = _context.X.ToList().Where(x => x.DeletedOn == null).First();
objX.DeletedOn = DateTime.Now;

EntitySet<Y> objYs = objX.Ys;
Y objY = objYs[0];
objY.DeletedOn = DateTime.Now;

_context.SubmitChanges();

On SubmitChanges() I get an exception "1 of 2 Updates failed", no other information as to why that happened. Any ideas?

Also the exception type is ChangeConflictException

Answer 1

I'm not sure what the cause of the error may be exactly, but there seem to be a number of problems with the example you've provided.

• Using ToList() before the Where() method would cause your context to read the entire table from the DB into memory, convert it to an array; and then in the same line you immediately call Where which will discard the rows you've loaded, but don't need. Why not just:

  _context.X.Where(...

• The Where method will return multiple items, but the second line in the example doesn't appear to be iterating through each item individually. It appears to be setting the DeletedOn property for the collection itself, but the collection wouldn't have such a property. It should fail right there.

• You are using DateTime.Now twice in the code. Not a problem, except that this will produce ever so slightly different date values each time it is called. You should call DateTime.Now once and assign the result to a variable so that everything you use it on gets identical values.

• At the point where you have "Y objY = objYs[0]" it will fail if there are no items in the Y collection for any given X. You'd get an index out of bounds exception on the array.

So given this example, I'm not sure if anyone could speculate as to why code modeled after this example might be breaking.

Answer 2

Sooo what was the cause of the problem - A trigger

I did a sql profiler and saw that

When ObjY's DeletedOn property got updated a trigger updated ObjX's property (value in table) called CountOfX

which led to an error as the SQL created by LINQ to SQL had the old CountOfX value in it.

Hence the conflict.

If you ever get this error - SQL profiler is the best place to start your investigation

ALSO NOT RELATED TO THE QUESTION I am testing LINQ to SQL and ADO.net Framework, weirdly this error happened in LINQ to SQL but not in ADO.net framework. But I like LINQ to SQL for its Lazy Loading. Waiting for EF to get outta beta