Monday, December 22, 2008

LINQ-to-SQL: DataContext.SubmitChanges() throws ChangeConflictException if NOCOUNT is ON

DataContext.SubmitChanges() will throw a ChangeConflictException if NOCOUNT is on. Use Reflector to look at the code in System.Data.Linq, you will notice that ChangeProcessor.SubmitChanges() method uses the count of the number of rows affected by the SQL statement to determine if updates succeeded. Updates are setup to fail if the row being updated was deleted or modified concurrently by another user. For example, say your update had modified value for column C in table T in the row with primary key PK = 1 from 5 to 6. The update statement constructed by Linq will look something like:

update T set C=6 where PK=1 and C = 5

The update will return 0 rows affected when either the row was deleted by a different user or if the value of C was updated. In either case, LINQ will raise a ChangeConflictException. However, if NOCOUNT is on, then the update statement will always return 0. Hence LINQ throws the ChangeConflictException even if there is no conflict.