EF核心是否比较值以在更新语句中包含列？ [英] Does EF core compares value to include columns in update statement?

问题描述

我有以下代码

var dbContext = Setup.ConfigureDBContext();
var wo = await dbContext.WorkOrders.Where(x => x.WorkOrderID == 88).SingleOrDefaultAsync();
var t = wo.Confidence;
wo.ModifiedDateTime = DateTime.UtcNow;
wo.Confidence = t;
await dbContext.SaveChangesAsync();

在上面的查询中，我分配了相同的信心但是更改 ModifiedDateTime
EF会生成以下SQL

in the above query i am assigning the same Confidence but changing the ModifiedDateTime EF generates the following SQL

exec sp_executesql N'SET NOCOUNT ON;
UPDATE [WorkOrders] SET [ModifiedDateTime] = @p0
WHERE [WorkOrderID] = @p1 AND [VersionStamp] = @p2;
SELECT [VersionStamp]
FROM [WorkOrders]
WHERE @@ROWCOUNT = 1 AND [WorkOrderID] = @p1;

',N'@p1 int,@p0 datetime,@p2 varbinary(8)',@p1=88,@p0='2019-10-09 15:33:06.343',@p2=0x0000000000582A52

请注意，EF不包括信心更新语句中的列。我假设EF必须将原始值与新值进行比较，如果有更改，则仅在更新语句中包括这些列。

Note that EF is not including Confidence column in the update statement. I am assuming, EF has to compare the original value with new value and if there is a change then only include those columns in the update statement.

那是正确的假设吗？

我问这个问题是因为 WorkOrder 表还具有4个nvarchar（max）列。这些列中的数据是长字符串。如果我的假设是正确的，那么EF还必须比较这4列以确定这些列是否需要包含在更新查询中。这样的比较会比较慢，并且可能会导致性能下降。然后，我可以仅为这4列创建单独的附属表。

I am asking this question because the WorkOrder table also has 4 nvarchar(max) columns. The data in these columns is long string. If my assumption is correct then EF also has to compare these 4 columns to decide to whether those column needs to include in the update query or not. And that comparison will be slower and may cause performance. Then I may create separate satellite table just for these 4 columns.

推荐答案

您的第一个实体是通过上下文获取的，因此默认情况下上下文会立即开始对其进行跟踪，这意味着您检索到的任何实体都将连同其原始值的副本一起存储在DbContext中。当您更改被跟踪实体的属性值时，上下文会将实体的 EntityState 更改为 Modified ，并且ChangeTracker记录旧属性值和新属性值。调用 SaveChanges 时，数据库将生成并执行UPDATE语句，因为ChangeTracker会跟踪已修改的属性，因此上下文将发出一条SQL语句，该语句仅更新那些更改的属性，它将与它们的原始值进行比较以确定更新后的属性。

Your first entity is obtained by the context, so by default the context begins tracking it immediately, which means that any entity you retrieve will be stored in the DbContext along with a copy of its original values. When you alter property values on a tracked entity, the context changes the EntityState for the entity to Modified and the ChangeTracker records the old property values and the new property values. When SaveChanges is called, an UPDATE statement is generated and executed by the database , since the ChangeTracker tracks which properties have been modified, the context will issue a SQL statement that updates only those properties that were changed , it will compare them with their original values to determine the updated properties .

如果您担心特定的列，则可以在查询时禁用跟踪：

If you concern about the specific columns , you can disable the Tracking while query :

var item = _context.Employees.Where(x=>x.ID==1).AsNoTracking().FirstOrDefault();

然后告诉context您要修改的属性，context将发出一条SQL语句，而不进行比较：

Then tell context which property you want to modify , context will issue an SQL statement updating without compare :

item.Name = "213213";
_context.Attach(item).Property(x => x.Name).IsModified = true;
_context.SaveChanges();

这篇关于EF核心是否比较值以在更新语句中包含列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！