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

Question

When executing SubmitChanges to the DataContext after updating a couple properties with a LINQ to SQL connection (against SQL Server Compact Edition) I get a "Row not found or changed." ChangeConflictException.

var ctx = new Data.MobileServerDataDataContext(Common.DatabasePath);
var deviceSessionRecord = ctx.Sessions.First(sess => sess.SessionRecId == args.DeviceSessionId);

deviceSessionRecord.IsActive = false;
deviceSessionRecord.Disconnected = DateTime.Now;

ctx.SubmitChanges();

The query generates the following SQL:

UPDATE [Sessions]
SET [Is_Active] = @p0, [Disconnected] = @p1
WHERE 0 = 1
-- @p0: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False]
-- @p1: Input DateTime (Size = 0; Prec = 0; Scale = 0) [9/4/2008 5:12:02 PM]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

The obvious problem is the WHERE 0=1, After the record was loaded, I've confirmed that all the properties in the "deviceSessionRecord" are correct to include the primary key. Also when catching the "ChangeConflictException" there is no additional information about why this failed. I've also confirmed that this exception get's thrown with exactly one record in the database (the record I'm attempting to update)

What's strange is that I have a very similar update statement in a different section of code and it generates the following SQL and does indeed update my SQL Server Compact Edition database.

UPDATE [Sessions]
SET [Is_Active] = @p4, [Disconnected] = @p5
WHERE ([Session_RecId] = @p0) AND ([App_RecId] = @p1) AND ([Is_Active] = 1) AND ([Established] = @p2) AND ([Disconnected] IS NULL) AND ([Member_Id] IS NULL) AND ([Company_Id] IS NULL) AND ([Site] IS NULL) AND (NOT ([Is_Device] = 1)) AND ([Machine_Name] = @p3)
-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [0fbbee53-cf4c-4643-9045-e0a284ad131b]
-- @p1: Input Guid (Size = 0; Prec = 0; Scale = 0) [7a174954-dd18-406e-833d-8da650207d3d]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [9/4/2008 5:20:50 PM]
-- @p3: Input String (Size = 0; Prec = 0; Scale = 0) [CWMOBILEDEV]
-- @p4: Input Boolean (Size = 0; Prec = 0; Scale = 0) [False]
-- @p5: Input DateTime (Size = 0; Prec = 0; Scale = 0) [9/4/2008 5:20:52 PM]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.21022.8

I have confirmed that the proper primary fields values have been identified in both the Database Schema and the DBML that generates the LINQ classes.

I guess this is almost a two part question:

1. Why is the exception being thrown?
2. After reviewing the second set of generated SQL, it seems like for detecting conflicts it would be nice to check all the fields, but I imagine this would be fairly inefficient. Is this the way this always works? Is there a setting to just check the primary key?

I've been fighting with this for the past two hours so any help would be appreciated.

Hartshorn

FWIW: I was getting this error when unintentionally calling the method twice. It would occur on the second call. – Verboten

Excellent background info to be found at c-sharpcorner.com/article/… – Sonar

Answer 1

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.

Samuels

I had a similar - albeit slightly different - problem, and your advice to double check for nullable saved my day! I was bald already, but this issue would have surely cost me another head of hair if I had one.. thanks! – Sinew

Make sure you set the 'Nullable' property in the properties window to True. I was editing the 'Server Data Type' property, changing it from VARCHAR(MAX) NOT NULL to VARCHAR(MAX) NULL and expecting it to work. Very simple mistake. – Obstacle

Had to upvote this. It saved me a ton of time. Was looking at my isolation levels because I had thought it was a concurrency issue – Move

I had a NUMERIC(12,8) column mapped to a Decimal property. I had to precise the DbType in the Column attribute [Column(DbType="numeric(12,8)")] public decimal? MyProperty ... – Desireah

The approved answer just tell why the problem occur, but dont tell how to just validate the primary key. – Cooe

No, probably since checking the nullable fields against the db solved his problem. His question only to check the primary key probably just was a shot to solve the underlying problem with a sledgehammer. – Samuels

One way of identifying the problem fields/columns is to save your current Linq-to-SQL entity classes, located in the .dbml file, to a separate file. Then, delete your current model and regenerate it from the database (using VS), which will generate a new .dbml file. Then, simply run a comparator like WinMerge or WinDiff on the two .dbml files to locate the problem differences. – Wooten

Found this today, tried to like the solution, only to find I'd liked it before! – Sinistral

Answer 2

First lets find root cause and 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
}

Flavour

Superb solution! I have a table that has around 80 fields, and there are numerous triggers on the table that are updating various fields during inserts and updates. I was getting this error from when updating the datacontext using L2S, but was pretty sure that it was being caused by one of the triggers updating a field, thus causing the data context to be different from the data in the table. Your code helped me to see exactly which field was causing the data context to be out of sync with the table. Thanks a ton!! – Unveil

This is a great solution for large tables. To handle nulls, change 'col.XValue.ToString()' to 'col.XValue == null ? "null" : col.XValue.ToString()' for each of the three value fields. – Intermission

Ditto on guarding against null references when stringifying OriginalValue, CurrentValue, and DatabaseValue. – Galoshes

I added nullable checks – Flavour

Answer 3

There is a method on DataContext called Refresh which may help here. It allows you to reload the database record before changes are submitted, and offers different modes to determine which values to keep. "KeepChanges" seems the smartest for my purposes, it is intended to merge my changes with any non-conflicting change that happened in the database in the meantime.

If I understand it correctly. :)

Industrialism

This answer fixed the problem in my case: dc.Refresh(RefreshMode.KeepChanges,changedObject); before dc.SubmitChanges – Besought

I had this issue when applying the ReadOnlyAttribute to properties in a Dynamic Data website. Updates stopped working and I was getting the error "Row not found or changed" (inserts were fine though). The above fix saved loads of effort and time! – Outdate

Could you please explain the RefreshMode values e.g. what does KeepCurrentValues mean? what does it do? Many thanks. I could create a question... – Outdate

I had problems with concurrent transactions not completing in time for another transaction to begin on the same rows. KeepChanges helped me here, so perhaps it just aborts the current transaction (while keeping the values it saved) and starting the new one (honestly I have no idea) – Gott

Answer 4

This can also be caused by using more than one DbContext.

So for example:

protected async Task loginUser(string username)
{
    using(var db = new Db())
    {
        var user = await db.Users
            .SingleAsync(u => u.Username == username);
        user.LastLogin = DateTime.UtcNow;
        await db.SaveChangesAsync();
    }
}

protected async Task doSomething(object obj)
{
    string username = "joe";
    using(var db = new Db())
    {
        var user = await db.Users
            .SingleAsync(u => u.Username == username);

        if (DateTime.UtcNow - user.LastLogin >
            new TimeSpan(0, 30, 0)
        )
            loginUser(username);

        user.Something = obj;
        await db.SaveChangesAsync();
    }
}

This code will fail from time to time, in ways that seem unpredictable, because the user is used in both contexts, changed and saved in one, then saved in the other. The in-memory representation of the user who owns "Something" doesn't match what's in the database, and so you get this lurking bug.

One way to prevent this is to write any code that might ever be called as a library method in such a way that it takes an optional DbContext:

protected async Task loginUser(string username, Db _db = null)
{
    await EFHelper.Using(_db, async db =>
    {
        var user = await db.Users...
        ... // Rest of loginUser code goes here
    });
}

public class EFHelper
{
    public static async Task Using<T>(T db, Func<T, Task> action)
        where T : DbContext, new()
    {
        if (db == null)
        {
            using (db = new T())
            {
                await action(db);
            }
        }
        else
        {
            await action(db);
        }
    }
}

So now your method takes an optional database, and if there isn't one, goes and makes one itself. If there is it just reuses what was passed in. The helper method makes it easy to reuse this pattern across your app.

Federal

Answer 5

I solved this error by redragging over a table from the server explorer to the designer and re-building.

Roberge

Redragging the offending table from the Server Explorer to the designer and rebuilding fixed this for me as well. – Ozone

Answer 6

I fixed this by adding (UpdateCheck = UpdateCheck.Never) to all [Column] definitions.

Does not feel like an appropriate solution, though. In my case it seems to be related to the fact that this table has an association to another table from where a row is deleted.

This is on Windows Phone 7.5.

Imparadise

this was my issue - the linq query produced contains EVERY single field in the WHERE clause during the UPDATE statement. so by the time we .SubmitChanges() the UPDATE wouldn't happen based on the now outdated WHERE - so it threw this exception. We removed the update checks on these fields to solve.. – Pyrrolidine

Answer 7

This is what you need to override this error on C# code:

            try
            {
                _db.SubmitChanges(ConflictMode.ContinueOnConflict);
            }
            catch (ChangeConflictException e)
            {
                foreach (ObjectChangeConflict occ in _db.ChangeConflicts)
                {
                    occ.Resolve(RefreshMode.KeepChanges);
                }
            }

Marylinmarylinda

I have scheduled items submitted by an application front-end to the database. These trigger execution in a service, each on different threads. The user can hit a 'cancel' button which changes all the outstanding command's status. The service finishes each one but finds that 'Pending' was changed to 'Cancelled' and cannot change it to 'Completed'. This fixed the problem for me. – Semple

Also check the other enumerations of RefreshMode, like KeepCurrentValues. Note that you have to call SubmitChanges again after using this logic. See msdn.microsoft.com/en-us/library/…. – Semple

Answer 8

I don't know if you've found any satisfactory answers to your question, but I posted a similar question and eventually answered it myself. It turned out that the NOCOUNT default connection option was turned on for the database, which caused a ChangeConflictException for every update made with Linq to Sql. You can refer to my post at here.

Rocket

Answer 9

In my case, the error was raised when two users having different LINQ-to-SQL data contexts updated the same entity in the same way. When the second user attempted the update, the copy they had in their data context was stale even though it was read after the first update had completed.

I discovered the explanation and solution in this article by Akshay Phadke: https://www.c-sharpcorner.com/article/overview-of-concurrency-in-linq-to-sql/

Here's the code I mostly lifted:

try
{
    this.DC.SubmitChanges();
}
catch (ChangeConflictException)
{
     this.DC.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues);

     foreach (ObjectChangeConflict objectChangeConflict in this.DC.ChangeConflicts)
     {
         foreach (MemberChangeConflict memberChangeConflict in objectChangeConflict.MemberConflicts)
         {
             Debug.WriteLine("Property Name = " + memberChangeConflict.Member.Name);
             Debug.WriteLine("Current Value = " + memberChangeConflict.CurrentValue.ToString());
             Debug.WriteLine("Original Value = " + memberChangeConflict.OriginalValue.ToString());
             Debug.WriteLine("Database Value = " + memberChangeConflict.DatabaseValue.ToString());
         }
     }
     this.DC.SubmitChanges();
     this.DC.Refresh(RefreshMode.OverwriteCurrentValues, att);
 }

When I looked at my output window while debugging, I could see that the Current Value matched the Database Value. The "Original Value" was always the culprit. That was the value read by the data context before applying the update.

Thanks to MarceloBarbosa for the inspiration.

Sonar

Answer 10

I know this question has long since been answered but here I have spent the last few hours banging my head against a wall and I just wanted to share my solution which turned out not to be related to any of the items in this thread:

Caching!

The select() part of my data object was using caching. When it came to updating the object a Row Not Found Or Changed error was cropping up.

Several of the answers did mention using different DataContext's and in retrospect this is probably what was happening but it didn't instantly lead me to think caching so hopefully this will help somebody!

Starinsky

Answer 11

I recently encountered this error, and found the problem was not with my Data Context, but with an update statement firing inside a trigger after Commit was being called on the Context. The trigger was trying to update a non-nullable field with a null value, and it was causing the context to error out with the message mentioned above.

I'm adding this answer solely to help others dealing with this error and not finding a resolution in the answers above.

Clovah

Answer 12

I have also got this error because of using two different contexts. I resolved this issue by using single data context.

Landrum

Answer 13

In my case the problem was with the server-wide user options. Following:

https://msdn.microsoft.com/en-us/library/ms190763.aspx

I enabled the NOCOUNT option in hope to get some performance benefits:

EXEC sys.sp_configure 'user options', 512;
RECONFIGURE;

and this turns out to break Linq's checks for the Affected Rows (as much as I can figure it out from .NET sources), leading to ChangeConflictException

Resetting the options to exclude the 512 bit fixed the problem.

Judsonjudus

Answer 14

After employing qub1n's answer, I found that the issue for me was that I had inadvertently declared a database column to be decimal(18,0). I was assigning a decimal value, but the database was changing it, stripping the decimal portion. This resulted in the row changed issue.

Just adding this if anyone else runs into a similar issue.

Potency

Answer 15

I know this is an older post, but the issue can still be problematic today. I wanted to share my experience with this; as the solution for me was slightly different than the accepted answer. The accepted answer however did lead me to resolve my issue so thank you!

In my case, I had an update trigger that would auto-insert a row into a status history table anytime the status changed on a row in a table (SQL Server); based on a set of known codes. My history table had a NOT NULL attribute for the status ID column, and my INSERT statement didn't take into account that a previously unknown code might slip through; thereby causing the row to insert to fail.

So the moral of the story is in addition to checking your data models, be sure to review any triggers you have defined as that too will result in a "row not found or changed" error.

Hope this helps someone else down the line; thanks all!

Acrocarpous

Answer 16

I had the same problem when inserting data and then wanting to modify or delete them in the same form, the solution I found to this was the following:

db.Refresh(System.Data.Linq.RefreshMode.KeepChanges, employee);

db = is your connection variable as you might imagine, and employee would be the variable you would be using for your table.

Karney