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();
}
Advertisements

19 thoughts on “Linq to SQL: ChangeConflictException With "WHERE 0 = 1"

  1. Just another word of thanks. Ran into this problem with some properties I’d added to dropthings widgets and your code helped pinpoint where I’d gone wrong. Cheers!

  2. I am having issues with:
    sw.WriteLine(e.SerializeForLog());
    Is this code dependent on another method perhaps?

  3. It’s possible I may have missed that.
    It’s a method that serializes an exception to a string (simply dumps everything in it into a string). Should be either easy enough to write or you could simply delete the line.

  4. i have seen this post at least 100 times and thought it doesnt help me…
    …now i have discovered that one column which doesnt have ANYTHING to do with my update was nullable in the db but not in the dbml – that was the reason!
    double or triplecheck your dbml – everything will be fine.

  5. Thanks Man!!!
    I want to add that it works, no matter of whether your real table in db has more columns then in your model.
    It fails if some of that columns has invalid null/not null state in comparing with real db

  6. Thanks a lot, I spent almost a day seeking for the root cause, and actually it was a NULL/Not NULL difference…
    Cheers

  7. Dude,
    I really appreciate your posting this issue, I spent the entire day trying to figure out the issue, problem was it does not error out on my Development machine but it does when I publish the code to our test site.
    Thanks a lot!

  8. Sorry I jumped the gun.
    I still have the issue on my Web server, but works fine on my development machine. What else should I be checking!

  9. In the db we have a NULL string, but in the C# code strings are nullable objects so its not marked as NULL. For example my dbml code behind has:
    private string _ProdDesc;
    Would this create the 0=1 issue?

  10. You’re a genius! Thanks so much, this was bugging me for quite some time now. It was great to see the ChangeConflictException in such a readable manner.

  11. I’ve got one word for you: triggers.
    I spent half a day with this until I realized that I’m actually updating two tables and the other one contains a trigger that alters the other table’s data.
    It’s sort of a cache type of thing, so my problem was easily solved by changing the fields UpdateCheck to Never.
    The funny (I’m not laughing though) thing is that it didn’t report any member conflicts at all.

  12. Your great discovery saved us from a misery and brain damage. Lots of thanks.
    BTW, don’t you think “WHERE 0 = 1” is a piece of very true .NET Zen? We were very close to satori this night.

  13. Oh man, this one was boggling me so much. This solved a lot of errors I was getting randomly. You the man!

  14. thanks a lot…Specially for the two sentence ” 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”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s