如何在一个SQL语句中更新多个实体模型？ [英] How do I update multiple Entity models in one SQL statement?

问题描述

我有以下内容：

List<Message> unreadMessages = this.context.Messages
            .Where( x =>
                x.AncestorMessage.MessageID == ancestorMessageID &&
                x.Read == false &&
                x.SentTo.Id == userID ).ToList();

foreach(var unreadMessage in unreadMessages)
{
    unreadMessage.Read = true;
}

this.context.SaveChanges();

但是必须有一种方法，而不必执行2个SQL查询，一个用于选择项，以及用于更新列表的项。

But there must be a way of doing this without having to do 2 SQL queries, one for selecting the items, and one for updating the list.

我该怎么做？

推荐答案

EF中的惯用支持

据我所知，尚不直接支持批量更新 还（尽管有关批量操作支持的讨论一直持续了一段时间，但很可能会在某个时候包括在内）。

As far as I know, there is no direct support for "bulk updates" yet in Entity Framework (there has been an ongoing discussion for bulk operation support for a while though, and it is likely it will be included at some point).

（为什么）要这样做？

很明显这是在本机SQL中可以在单个语句中完成的操作，与您在问题中采用的方法相比，它具有一些明显的优势。使用单个SQL语句，客户端和数据库服务器之间只需要非常少量的I / O，并且该语句本身可以由DB服务器完全执行和优化。无需转移到可能有较大结果集的客户端并在其中进行迭代，只需更新一个或两个字段并以其他方式发送回去。

It is clear that this is an operation that, in native SQL, can be achieved in a single statement, and provides some significant advantages over the approach followed in your question. Using the single SQL statement, only a very small amount of I/O is required between client and DB server, and the statement itself can be completely executed and optimized by the DB server. No need to transfer to and iterate through a potentially large result set client side, just to update one or two fields and send this back the other way.

如何

因此，尽管EF没有直接支持，但仍然可以使用两种方法之一来做到这一点。

So although not directly supported by EF, it is still possible to do this, using one of two approaches.

选项A.手工编写SQL更新语句

这是一种非常简单的方法，不需要任何其他工具/ packages，也可以执行异步操作：

This is a very simple approach, that does not require any other tools/packages and can be performed Async as well:

var sql = "UPDATE TABLE x SET FIELDA = @fieldA WHERE FIELDB = @fieldb";
var parameters = new SqlParameter[] { ..., ... };
int result = db.Database.ExecuteSqlCommand(sql, parameters);

或

int result = await db.Database.ExecuteSqlCommandAsync(sql, parameters);

明显的缺点是，很好地打破了很好的linqy范式，并且不得不手工编写SQL（可能需要更多代码）

The obvious downside is, well breaking the nice linqy paradigm and having to handcode your SQL (possibly for more than one target SQL dialect).

选项B。使用其中一个EF扩展/实用程序包

一段时间以来，有许多开源的nuget软件包可以为EF提供特定的扩展。它们中的许多确实提供了一种很好的 linqy方式来向服务器发出单个更新SQL语句。两个示例是：

Since a while, a number of open source nuget packages are available that offer specific extensions to EF. A number of them do provide a nice "linqy" way to issue a single update SQL statement to the server. Two examples are:

1. 实体框架扩展库，该库允许使用以下语句执行批量更新：

1. Entity Framework Extended Library that allows performing a bulk update using a statement like:

context.Messages.Update(
     x => x.Read == false && x.SentTo.Id == userID,
     x => new Message { Read = true });

它也可以在 github

EntityFramework.Utilities ，它允许使用如下语句执行批量更新：

EntityFramework.Utilities that allows performing a bulk update using a statement like:

EFBatchOperation
   .For(context, context.Messages)
   .Where(x => x.Read == false && x.SentTo.Id == userID)
   .Update(x => x.Read, x => x.Read = true);

也可以在 github

当然还有其他软件包和库可以提供类似的支持。

And there are definitely other packages and libraries out there that provide similar support.

这篇关于如何在一个SQL语句中更新多个实体模型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！