我该怎么做才能解决“找不到或未更改行"的问题?SQL Server Compact Edition 数据库上的

问题描述

在使用 LINQ to SQL 连接更新几个属性后对 DataContext 执行 SubmitChanges 时(针对 SQL Server Compact Edition)，我收到未找到或更改行"的消息.ChangeConflictException.

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

查询生成以下 SQL:

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

明显的问题是WHERE 0=1，加载记录后，我确认deviceSessionRecord"中的所有属性都正确，包括主键.此外，在捕获ChangeConflictException"时，没有关于失败原因的其他信息.我还确认这个异常被抛出，数据库中只有一条记录(我试图更新的记录)

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)

奇怪的是，我在不同的代码部分有一个非常相似的更新语句，它生成以下 SQL 并且确实更新了我的 SQL Server Compact Edition 数据库.

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

我已确认已在数据库架构和生成 LINQ 类的 DBML 中确定了正确的主要字段值.

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. 为什么会抛出异常?
2. 在查看第二组生成的 SQL 之后，似乎为了检测冲突，检查所有字段会很好，但我认为这会相当低效.这是总是这样吗?有没有只检查主键的设置?

过去两个小时我一直在努力解决这个问题，因此我们将不胜感激.

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

推荐答案

这很讨厌，但很简单:

检查 O/R-Designer 中所有字段的数据类型是否与 SQL 表中的数据类型匹配.双重检查是否可以为空！一个列应该在 O/R-Designer 和 SQL 中都可以为空，或者在两者中都不能为空.

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.

例如，NVARCHAR 列title"在您的数据库中被标记为 NULLable，并且包含值 NULL.即使该列在您的 O/R-Mapping 中被标记为 NOT NULLable，LINQ 也会成功加载它并将 column-String 设置为 null.

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.

• 现在你改变一些东西并调用SubmitChanges().
• LINQ 将生成一个 SQL 查询包含WHERE [title] IS NULL"，以确保标题未被其他人更改.
• LINQ 查找的属性[title] 在映射中.
• LINQ 会发现 [title] NOT NULLable.
• 由于 [title] 不可为空，因此逻辑它永远不可能为 NULL！
• 所以，优化查询，LINQ用where 0 = 1"替换它，相当于从不"的 SQL.

当字段的数据类型与 SQL 中的数据类型不匹配，或者字段丢失时，也会出现相同的症状，因为 LINQ 将无法确保 SQL 数据在读取数据后没有发生变化.

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 Server Compact Edition 数据库上的 LINQ to SQL 中的异常?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！