LINQ中的更新查询包含WHERE子句中的所有列，而不仅仅是主键列 [英] Update query in LINQ contains all columns in WHERE clause instead of just the primary key column

问题描述

我正在使用Linq更新表中的单个列，请使用下面的虚拟表.

MyTable(PKID，ColumnToUpdate，SomeRandomColumn)

  var row =(从DataContext.MyTable中的x其中b.PKID == 5选择x).FirstOrDefault();row.ColumnToUpdate = 20;DataContext.SubmitChanges(); 

这将列更新为预期的，这里没有任何惊喜.但是，当我检查生成的SQL命令时，它会执行以下操作:

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

这是在执行更新，但前提是所有列均与Entity期望的值匹配，而不是自己引用主键列.

如果数据库列被另一个过程更改，则在此特定项目中这是非常可行的；例如.在获取要操作的行、计算要设置值的更改以及将更新命令作为一批行发出之间有一个窗口.在这种情况下，查询将导致异常，导致部分更新，除非我捕获，重新加载数据并重新发送单个查询.缺点还在于行信息可能非常大(例如，包含HTML标记)，并且当处理较大的批处理时，整个信息将传递给SQL并降低系统速度.

有没有一种方法可以使Linq/Entity仅根据Where子句中的PK列发出更新命令?

解决方案

默认情况下，我从未在生产项目中使用LINQ-to-SQL，也从未意识到使用乐观并发 ¹ ./p>

这是默认行为:

1. 如果表中没有 Timestamp/Rowversion 列 ² ，则所有列均具有"Update Check"(更新检查)；设置为始终"在DBML中(主键列和计算列(即所有可更新列)除外).
2. 如果表中确实有 Timestamp/Rowversion 列，则此列具有"Time Stamp"(时间戳)；设置为"True"在DBML中，所有列都具有"Update Check"(更新检查).=从不".

更新检查"或时间戳"将一列标记为并发令牌.这就是为什么在更新语句中您看到这些附加谓词的原因(不是这样)，即随机"列.显然，模型中的表没有 Timestamp/Rowversion 列，因此更新会检查表中所有可更新列的值.

---

¹ 乐观并发:更新记录时未设置排他锁，但更新时将检查所有列或选定列的现有值.如果在获取数据和保存数据之间另一用户更改了这些列值之一，则会发生更新异常.

² 当记录更新时，数据类型为 Timestamp 或 Rowversion 的列会自动递增，因此会检测到该记录的所有并发更改

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?

解决方案

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

This is the default behavior:

1. If a table doesn't have a Timestamp/Rowversion column², 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.

---

¹ 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.

² 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.

这篇关于LINQ中的更新查询包含WHERE子句中的所有列，而不仅仅是主键列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！