linqlinq-to-sqloptimistic-concurrency

Update query in LINQ contains all columns in WHERE clause instead of just the primary key column

I am updating a single column in a table using Linq, take fictitious table below.

MyTable (PKID, ColumnToUpdate, SomeRandomColumn)

var row = (from x in DataContext.MyTable
           where b.PKID == 5
           select x).FirstOrDefault();

row.ColumnToUpdate = 20;
DataContext.SubmitChanges();

This updates the column to as expected, no surprises here. However when I inspect the SQL commands which are generated, it does this:

UPDATE [dbo].[MyTable ]
SET [ColumnToUpdate ] = @p2
WHERE ([PKID] = @p0) AND ([SomeRandomColumn] = @p1)

This is performing the update, but only if all columns have matched the values of what Entity expects them to be, rather than referencing the Primary Key column on it's own.

If a database column is changed by another process, which is very feasible in this particular project; eg. There is a window between getting the row you want to manipulate, calculating the changes you would like to set the value to, and issuing the update command as a batch of rows. In this situation the query will cause an exception, causing a partial update, unless I trap, reload the data and resend individual queries. It also has a downside that the row information can be quite large (ie, containing HTML mark up for instance), and the whole thing gets passed to SQL and slows the system down when larger batches are processed.

Is there a way of making Linq / Entity to issue update commands based only on the PK column in the Where clause?

Solution

  • I never used LINQ-to-SQL for production projects and I never were aware of it applying optimistic concurrency1 by default.

    This is the default behavior:

    1. If a table doesn't have a Timestamp/Rowversion column2, all columns have "Update Check" set to "Always" in the DBML (except primary key columns and computed columns, i.e. all updateable columns).
    2. If a table does have a Timestamp/Rowversion column, this column has "Time Stamp" set to "True" in the DBML and all columns have "Update Check" = "Never".

    Either "Update Check" or "Time Stamp" mark a column as concurrency token. That's why in update statements you see these additional predicates on (not so) "random" columns. Apparently, the tables in your model didn't have Timestamp/Rowversion columns, hence an update checks the values of all updateable columns in the table.

    1 Optimistic concurrency: no exclusive locks are set when updating records, but existing values of all or selected columns are checked while updating. If one of those column value was changed by another user between getting the data and saving them, an update exception occurs.

    2 A column of data type Timestamp or Rowversion is automatically incremented when a record is updated and therefore detects all concurrent changes to this record.