ChangeConflictException in updating Linq to Sql

Question

I am in the world of pain with this, and I am very grateful if anyone can help.

I have a DataContext associated with one test pattern in 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 the row - for UPDATE I always get a ChangeConflictException exception with an empty ObjectChangeConflict.MemberConflicts collection. Here is the code:

 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 result of the log for updates running 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 run this query on a clustered SQL Server 2000 (8.0.2039). I can not, for life, understand what is happening here. Making a similar UPDATE query to the database seems to work fine.

Thanks in advance for your help.

Answer 1

Finally, I realized what was happening with this. Apparently, the "no count" option was enabled for this server.

In Microsoft SQL Server Management Studio 2005:

• Right click on the server and click Properties
• In the left part of the Server Properties window, select the Connections page
• In the "Default Connection Settings" section, make sure that "no count" is not selected.

LINQ to SQL seems to be using @@ ROWCOUNT after updates to release automatic optimized concurrency checking. Of course, if "no count" is enabled for the entire server, @@ ROWCOUNT always returns zero, and LINQ to SQL throws a ConcurrencyException after releasing database updates.

This is not the only behavior that LINQ to SQL uses. LINQ to SQL does not perform automated concurrency optimizations with @@ ROWCOUNT if you have a TIMESTAMP column in your table.

Answer 2

Is it possible that any of the data for the row changed between when it was restored and an update was attempted? Since LINQ-> SQL has an automatic concurrency check, which will check the contents of the object against the current stored values ​​(as you see in the generated query). If it is possible that any of the fields has changed for a row in the database and the object that is tracking LINQ, the update will not be performed. If this happens for a good reason, and you know which fields, you can update the object in the DBML designer; select the field for the reason and change the property "Update scan" to "Never".

Answer 3

I had the same problem with SQL Server 2008, and the no count connect option has already been processed .

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

Answer 4

First, write down information about the problem, which row and which field are conflicting and which values ​​are in conflict.

To implement such a detailed log, see my solution here:

What can I do to resolve the "String not found or changed"? An exception in LINQ to SQL in a SQL Server Compact Edition database?