Omer van Kloeten's .NET Zen

Linq to SQL: ChangeConflictException With "WHERE 0 = 1"

I just finished debugging a very annoying error, where I kept getting a ChangeConflictException with the message "Row not found or changed" while trying to update my data. I found that there were no Member Conflicts in the exception, which seemed really weird to me.

When I set the Log property on my context, I found that Linq to SQL was using UPDATE statements that looked like this:

UPDATE [dbo].[MyTable]
SET [Col1] = @p0, [Col2] = @p1
WHERE 0 = 1
-- @p0: Input ...
-- @p1: Input ...

This was apparently some optimization made by the Linq to SQL team, but it gave me a clue as to where to look.

Apparently, when you have a Linq to SQL object model that differs by even one property from your database, you're prone to get this. I found out that two of my fields that were in the SET part of the WHERE clause were defined as NOT NULL in my object model, but were marked as nullable in my database model. Quick fix and all works well.

One has to wonder why there is no "Synchronize" button in the Linq to SQL designer.

Also, here's an interesting piece of code I wrote while debugging, which serializes a ChangeConflictException to string:

public static string SerializeForLog(this ChangeConflictException e, DataContext context)
{
    StringBuilder builder = new StringBuilder();

    using (StringWriter sw = new StringWriter(builder))
    {
        sw.WriteLine("Optimistic concurrency error:");
        sw.WriteLine(e.Message);

        foreach (ObjectChangeConflict occ in context.ChangeConflicts)
        {
            Type objType = occ.Object.GetType();
            MetaTable metatable = context.Mapping.GetTable(objType);
            object entityInConflict = occ.Object;

            sw.WriteLine("Table name: {0}", metatable.TableName);

            var noConflicts =
                from property in objType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                where property.CanRead &&
                      property.CanWrite &&
                      property.GetIndexParameters().Length == 0 &&
                      !occ.MemberConflicts.Any(c => c.Member.Name != property.Name)
                orderby property.Name
                select property;

            foreach (var property in noConflicts)
            {
                sw.WriteLine("\tMember: {0}", property.Name);
                sw.WriteLine("\t\tCurrent value: {0}", 
                    property.GetGetMethod().Invoke(occ.Object, new object[0]));
            }

            sw.WriteLine("\t-- Conflicts Start Here --", metatable.TableName);

            foreach (MemberChangeConflict mcc in occ.MemberConflicts)
            {
                sw.WriteLine("\tMember: {0}", mcc.Member.Name);
                sw.WriteLine("\t\tCurrent value: {0}", mcc.CurrentValue);
                sw.WriteLine("\t\tOriginal value: {0}", mcc.OriginalValue);
                sw.WriteLine("\t\tDatabase value: {0}", mcc.DatabaseValue);
            }
        }

        sw.WriteLine();
        sw.WriteLine("Attempted SQL: ");

        TextWriter tw = context.Log;

        try
        {
            context.Log = sw;
            context.SubmitChanges();
        }
        catch (ChangeConflictException)
        {
            // This is what we wanted.
        }
        catch
        {
            sw.WriteLine("Unable to recreate SQL!");
        }
        finally
        {
            context.Log = tw;
        }

        sw.WriteLine();

        sw.WriteLine(e.SerializeForLog());
    }

    return builder.ToString();
}