如何解决 SQLServerCompactEdition 数据库上的 LINQtoSQL 中的“ Row not found or change”异常？

小开

最佳答案

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.

小开

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

小开

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.

小开

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. :)

小开

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.

小开

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!

小开

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.

小开

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.

小开

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

小开

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
}

小开

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.

小开

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

小开

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.

小开

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.

小开

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!

小开

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.