Linq 中的 ChangeConflictException 到 Sql 更新 [英] ChangeConflictException in Linq to Sql update
问题描述
我对这个世界感到痛苦,如果有人能提供帮助,我将不胜感激.
I'm in a world of pain with this one, and I'd very much appreciate it if someone could help out.
我有一个 DataContext 附加到数据库上的单个测试表.测试表如下:
I have a DataContext attached to a single test table on a database. The test table is as follows:
CREATE TABLE [dbo].[LinqTests](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[StringValue] [varchar](255) NOT NULL,
[DateTimeValue] [datetime] NOT NULL,
[BooleanValue] [bit] NOT NULL,
CONSTRAINT [PK_LinqTests] PRIMARY KEY CLUSTERED ([ID] ASC))
ON [PRIMARY]
使用 Linq,我可以在测试表中添加、检索和删除行,但我无法更新行——对于 UPDATE,我总是得到一个 ChangeConflictException,其中包含一个空的 ObjectChangeConflict.MemberConflicts 集合.这是使用的代码:
Using Linq, I can add, retrieve and delete rows from the test table, but I cannot update a row -- for an UPDATE, I always get a ChangeConflictException with an empty ObjectChangeConflict.MemberConflicts collection. Here is the code used:
var dataContext = new UniversityDataContext();
dataContext.Log = Console.Out;
for (int i = 1; i <= 1; i++) {
var linqTest = dataContext.LinqTests.Where(l => (l.ID == i)).FirstOrDefault();
if (null != linqTest) {
linqTest.StringValue += " I've been updated.";
}
else {
linqTest = new LinqTest {
BooleanValue = false,
DateTimeValue = DateTime.UtcNow,
StringValue = "I am in loop " + i + "."
};
dataContext.LinqTests.InsertOnSubmit(linqTest);
}
}
try {
dataContext.SubmitChanges(ConflictMode.ContinueOnConflict);
}
catch (ChangeConflictException exception) {
Console.WriteLine("Optimistic concurrency error.");
Console.WriteLine(exception.Message);
Console.ReadLine();
}
Console.ReadLine();
这是通过 DataContext 执行的更新的日志输出.
Here is the log output for an update performed through the DataContext.
UPDATE [dbo].[LinqTests]
SET [StringValue] = @p3
WHERE ([ID] = @p0) AND ([StringValue] = @p1) AND ([DateTimeValue] = @p2) AND (NOT ([BooleanValue] = 1))
-- @p0: Input BigInt (Size = 0; Prec = 0; Scale = 0) [1]
-- @p1: Input VarChar (Size = 15; Prec = 0; Scale = 0) [I am in loop 1.]
-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [3/19/2009 7:54:26 PM]
-- @p3: Input VarChar (Size = 34; Prec = 0; Scale = 0) [I am in loop 1. I've been updated.]
-- Context: SqlProvider(Sql2000) Model: AttributedMetaModel Build: 3.5.30729.1
我在群集 SQL Server 2000 (8.0.2039) 上运行此查询.对于我的一生,我无法弄清楚这里发生了什么.对数据库运行类似的 UPDATE 查询似乎工作正常.
I'm running this query on a clustered SQL Server 2000 (8.0.2039). I cannot, for the life of me, figure out what's going on here. Running a similar UPDATE query against the DB seems to work fine.
在此先感谢您的帮助.
推荐答案
我终于弄清楚这是怎么回事了.显然,此服务器的无计数"选项已打开.
I finally figured out what was happening with this. Apparently, the "no count" option was turned on for this server.
在 Microsoft SQL Server Management Studio 2005 中:
In Microsoft SQL Server Management Studio 2005:
- 右键单击服务器,然后单击属性
- 在服务器属性"窗口的左侧,选择连接页面
- 在默认连接选项下,确保未选择无计数".
显然,LINQ to SQL 在更新后使用@@ROWCOUNT 来发出自动乐观并发检查.当然,如果对整个服务器开启不计数",@@ROWCOUNT 总是返回零,LINQ to SQL 在向数据库发出更新后抛出 ConcurrencyException.
Apparently, LINQ to SQL uses @@ROWCOUNT after updates to issue an automated optimistic concurrency check. Of course, if "no count" is turned on for the entire server, @@ROWCOUNT always returns zero, and LINQ to SQL throws a ConcurrencyException after issuing updates to the database.
这不是 LINQ to SQL 使用的唯一更新行为.如果表中有 TIMESTAMP 列,LINQ to SQL 不会使用 @@ROWCOUNT 执行自动乐观并发检查.
This isn't the only update behavior LINQ to SQL uses. LINQ to SQL doesn't perform an automated optimistic concurrency check with @@ROWCOUNT if you have a TIMESTAMP column on your table.
这篇关于Linq 中的 ChangeConflictException 到 Sql 更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!