.net 如何解决 SQL Server Compact Edition 数据库上 LINQ to SQL 中的“未找到或更改行”异常？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45045/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What can I do to resolve a "Row not found or changed" Exception in LINQ to SQL on a SQL Server Compact Edition Database?
提问by Kevin
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.
在使用 LINQ to SQL 连接（针对 SQL Server Compact Edition）更新几个属性后对 DataContext 执行 SubmitChanges 时，我收到“未找到或更改行”的消息。变更冲突异常。
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:
该查询生成以下 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)
明显的问题是WHERE 0=1，加载记录后，我已经确认“deviceSessionRecord”中的所有属性都正确，包括主键。此外，在捕获“ChangeConflictException”时，没有关于失败原因的其他信息。我还确认在数据库中只有一条记录（我正在尝试更新的记录）中抛出了这个异常
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.
奇怪的是，我在代码的不同部分有一个非常相似的更新语句，它生成以下 SQL 并且确实更新了我的 SQL Server Compact Edition 数据库。
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.
我已经确认在数据库架构和生成 LINQ 类的 DBML 中都确定了正确的主字段值。
I guess this is almost a two part question:
我想这几乎是一个两部分的问题：
- Why is the exception being thrown?
- 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?
- 为什么会抛出异常？
- 在查看第二组生成的 SQL 之后，似乎为了检测冲突，检查所有字段会很好，但我认为这会相当低效。这是总是这样吗？有没有只检查主键的设置？
I've been fighting with this for the past two hours so any help would be appreciated.
过去两个小时我一直在与这个斗争，所以任何帮助将不胜感激。
回答by Sam
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.
检查 O/R-Designer 中所有字段的数据类型是否与 SQL 表中的数据类型匹配。 仔细检查是否可以为空！一个列应该在 O/R-Designer 和 SQL 中都可以为空，或者在两者中都不能为空。
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.
例如，NVARCHAR 列“title”在您的数据库中被标记为 NULLable，并且包含值 NULL。即使该列在您的 O/R-Mapping 中被标记为 NOT NULLable，LINQ 也会成功加载它并将 column-String 设置为 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".
- 现在你改变一些东西并调用 SubmitChanges()。
- LINQ 将生成一个包含“WHERE [title] IS NULL”的 SQL 查询，以确保标题未被其他人更改。
- LINQ 在映射中查找 [title] 的属性。
- LINQ 会发现 [title] NOT NULLable。
- 由于 [title] 是 NOT NULLable，从逻辑上它永远不可能是 NULL！
- 因此，优化查询时，LINQ 将其替换为“where 0 = 1”，SQL 等效于“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.
当字段的数据类型与 SQL 中的数据类型不匹配时，或者如果字段丢失，也会出现相同的症状，因为 LINQ 将无法确保 SQL 数据在读取数据后没有更改。
回答by Tomas Kubes
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:
创建用于包装您的 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
}
回答by Matt Sherman
There is a method on DataContext called Refreshwhich 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.
DataContext 上有一个名为Refresh的方法，它可能在这里有所帮助。它允许您在提交更改之前重新加载数据库记录，并提供不同的模式来确定要保留哪些值。“KeepChanges”对于我的目的来说似乎是最聪明的，它旨在将我的更改与同时发生在数据库中的任何非冲突更改合并。
If I understand it correctly. :)
如果我理解正确的话。:)
回答by Chris Moschini
This can also be caused by using more than one DbContext.
这也可能是由使用多个 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.
这段代码会不时以似乎不可预测的方式失败，因为用户在两种上下文中都被使用，在一个上下文中更改和保存，然后在另一个上下文中保存。拥有“Something”的用户的内存表示与数据库中的内容不匹配，因此您会遇到这个潜在的错误。
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:
防止这种情况的一种方法是编写任何可能被称为库方法的代码，以采用可选的 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.
所以现在你的方法需要一个可选的数据库，如果没有，就自己创建一个。如果有，它只是重用传入的内容。辅助方法可以轻松地在您的应用程序中重用此模式。
回答by Chris Moschini
I solved this error by redragging over a table from the server explorer to the designer and re-building.
我通过将表从服务器资源管理器重新拖动到设计器并重新构建来解决此错误。
回答by Michael Nero
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.
不知道你有没有找到满意的答案，但我贴了一个类似的问题，最后我自己回答了。原来是为数据库开启了 NOCOUNT 默认连接选项，这导致每次使用 Linq to Sql 进行更新时都会引发 ChangeConflictException。你可以在这里参考我的帖子。
回答by Johan Paul
I fixed this by adding (UpdateCheck = UpdateCheck.Never)to all [Column]definitions.
我通过添加(UpdateCheck = UpdateCheck.Never)到所有[Column]定义来解决这个问题。
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.
这是在 Windows Phone 7.5 上。
回答by MarceloBarbosa
This is what you need to override this error on C# code:
这是您在 C# 代码上覆盖此错误所需的内容：
try
{
_db.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException e)
{
foreach (ObjectChangeConflict occ in _db.ChangeConflicts)
{
occ.Resolve(RefreshMode.KeepChanges);
}
}
回答by John Pasquet
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.
在使用 qub1n 的答案后，我发现我的问题是我无意中将数据库列声明为十进制（18,0）。我分配了一个十进制值，但数据库正在改变它，去掉小数部分。这导致了行更改问题。
Just adding this if anyone else runs into a similar issue.
如果其他人遇到类似问题，只需添加此内容。
回答by Wojtek
In my case the problem was with the server-wide user options. Following:
就我而言，问题在于服务器范围的用户选项。下列的：
https://msdn.microsoft.com/en-us/library/ms190763.aspx
https://msdn.microsoft.com/en-us/library/ms190763.aspx
I enabled the NOCOUNT option in hope to get some performance benefits:
我启用了 NOCOUNT 选项，希望能获得一些性能优势：
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
结果证明这会破坏 Linq 对 Affected Rows 的检查（我可以从 .NET 源中了解到），导致ChangeConflictException
Resetting the options to exclude the 512 bit fixed the problem.
重置选项以排除 512 位修复了问题。
