Linq中的ChangeConflictException至SQL更新

问题描述

我对此感到非常痛苦，如果有人可以提供帮助，我将非常感激.

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:

1. 右键单击服务器，然后单击属性
2. 在服务器属性"窗口的左侧，选择连接页面
3. 在默认连接"选项下，确保未选择无计数".

1. Right click on the server and click Properties
2. On the left hand of the Server Properties window, select the Connections page
3. Under Default connection options, ensure that "no count" is not selected.

显然，LINQ to SQL在更新后使用@@ ROWCOUNT发出自动乐观并发检查.当然，如果整个服务器都打开"no count"，则@@ 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更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！