ChangeConflictException in Linq to Sql update

Question

I'm in a world of pain with this one, and I'd very much appreciate it if someone could help out.

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]

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

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

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.

Thanks in advance for any help.

Answer 1

I finally figured out what was happening with this. Apparently, the "no count" option was turned on for this server.

In Microsoft SQL Server Management Studio 2005:

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.

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.

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.

Answer 2

Is it possible that any of the data for the row has changed between when it was retrieved and the update was attempted? Because LINQ->SQL has automatic concurrency checking that will validate the contents of the object against the currently stored values (like you see in the generated query). If it is possible that any of the fields have changed for the row in the DB vs the object LINQ is tracking then the update will fail. If this is occurring and for good reason and you know what fields, you can update the object in the DBML designer; select the field at cause and change the "Update Check" property to "Never".

Answer 3

I had the same issue with SQL Server 2008 and the connection option no count already turned of.

Instead of changing the Update Check property to Never (as Quintin suggests), I set it to WhenChanged and the issue was solved.