What can I do to resolve a "Row not found or changed" Exception in LINQ to SQL on a SQL Server Compact Edition Database?

Thats nasty, but simple:

Check if the data types for all fields in the O/R-Designer match the data types in your SQL table. Double check for nullable! A column should be either nullable in both the O/R-Designer and SQL, or not nullable in both.

For example, a NVARCHAR column "title" is marked as NULLable in your database, and contains the value NULL. Even though the column is marked as NOT NULLable in your O/R-Mapping, LINQ will load it successfully and set the column-String to null.

• Now you change something and call SubmitChanges().
• LINQ will generate a SQL query containing "WHERE [title] IS NULL", to make sure the title has not been changed by someone else.
• LINQ looks up the properties of [title] in the mapping.
• LINQ will find [title] NOT NULLable.
• Since [title] is NOT NULLable, by logic it never could be NULL!
• So, optimizing the query, LINQ replaces it with "where 0 = 1", the SQL equivalent of "never".

The same symptom will appear when the data types of a field does not match the data type in SQL, or if fields are missing, since LINQ will not be able to make sure the SQL data has not changed since reading the data.

First, it useful to know, what is causing the problem. Googling solution should help, you can log the details (table, column, old value, new value) about the conflict to find better solution for solving the conflict later:

public class ChangeConflictExceptionWithDetails : ChangeConflictException
{
    public ChangeConflictExceptionWithDetails(ChangeConflictException inner, DataContext context)
        : base(inner.Message + " " + GetChangeConflictExceptionDetailString(context))
    {
    }

    /// <summary>
    /// Code from following link
    /// https://ittecture.wordpress.com/2008/10/17/tip-of-the-day-3/
    /// </summary>
    /// <param name="context"></param>
    /// <returns></returns>
    static string GetChangeConflictExceptionDetailString(DataContext context)
    {
        StringBuilder sb = new StringBuilder();

        foreach (ObjectChangeConflict changeConflict in context.ChangeConflicts)
        {
            System.Data.Linq.Mapping.MetaTable metatable = context.Mapping.GetTable(changeConflict.Object.GetType());

            sb.AppendFormat("Table name: {0}", metatable.TableName);
            sb.AppendLine();

            foreach (MemberChangeConflict col in changeConflict.MemberConflicts)
            {
                sb.AppendFormat("Column name : {0}", col.Member.Name);
                sb.AppendLine();
                sb.AppendFormat("Original value : {0}", col.OriginalValue.ToString());
                sb.AppendLine();
                sb.AppendFormat("Current value : {0}", col.CurrentValue.ToString());
                sb.AppendLine();
                sb.AppendFormat("Database value : {0}", col.DatabaseValue.ToString());
                sb.AppendLine();
                sb.AppendLine();
            }
        }

        return sb.ToString();
    }
}

Create helper for wrapping your sumbitChanges:

public static class DataContextExtensions
{
    public static void SubmitChangesWithDetailException(this DataContext dataContext)
    {   
        try
        {         
            dataContext.SubmitChanges();
        }
        catch (ChangeConflictException ex)
        {
            throw new ChangeConflictExceptionWithDetails(ex, dataContext);
        }           
    }
}

And then call submit changes code:

Datamodel.SubmitChangesWithDetailException();

Finally, log the exception in your global exception handler:

protected void Application_Error(object sender, EventArgs e)
{         
    Exception ex = Server.GetLastError();
    //TODO
}