C＃实体框架：批量扩展输入内存问题 [英] C# Entity Framework: Bulk Extensions Input Memory Issue

问题描述

我目前正在使用EF扩展程序。我不明白的一件事是，它应该有助于提高性能

I am currently using EF Extensions. One thing I don't understand, "its supposed to help with performance"

但是，将一百万条以上的记录放入List变量中是内存问题本身。
因此，如果要更新百万条记录，而又不将所有内容都保存在内存中，该如何高效地完成呢？

however placing a million+ records into List variable, is a Memory Issue itself. So If wanting to update million records, without holding everything in memory, how can this be done efficiently?

我们应该使用 for循环，并批量更新说10,000？ EFExtensions BulkUpdate是否具有支持此功能的任何本机功能？

Should we use a for loop, and update in batches say 10,000? Does EFExtensions BulkUpdate have any native functionality to support this?

示例：

var productUpdate = _dbContext.Set<Product>()
    .Where(x => x.ProductType == 'Electronics');  // this creates IQueryable

await productUpdate.ForEachAsync(c => c.ProductBrand = 'ABC Company');

_dbContext.BulkUpdateAsync(productUpdate.ToList());

资源：

https://entityframework-extensions.net/bulk-update

推荐答案

我找到了正确的 EF扩展方式以类似查询的条件进行批量更新：

I found the "proper" EF Extensions way to do a bulk update with a query-like condition:

var productUpdate = _dbContext.Set<Product>()
    .Where(x => x.ProductType == 'Electronics')
    .UpdateFromQuery( x => new Product { ProductBrand = "ABC Company" });

这应该导致正确的SQL UPDATE ... SET ... WHERE ，而无需首先加载实体，具体方法如下：文档：

This should result in a proper SQL UPDATE ... SET ... WHERE, without the need to load entities first, as per the documentation:

为什么 UpdateFromQuery 比 SaveChanges 快， BulkSaveChanges 和 BulkUpdate ？

UpdateFromQuery 直接在SQL中执行一条语句，例如 UPDATE [TableName] SET [SetColumnsAndValues] WHERE [Key] 。

UpdateFromQuery executes a statement directly in SQL such as UPDATE [TableName] SET [SetColumnsAndValues] WHERE [Key].

其他操作通常需要一个或多个数据库往返，这会使性能变慢。

Other operations normally require one or multiple database round-trips which makes the performance slower.

您可以在此 dotnet小提琴示例，改编自其 BulkUpdate 的示例。

You can check the working syntax on this dotnet fiddle example, adapted from their example of BulkUpdate.

• 没有我

• No mention of batch operations for this, unfortunately.

在进行此类大型更新之前，可能值得考虑停用您可能拥有的索引此列，然后再重建它们。

Before doing a big update like this, it might be worth considering deactivating indexes you may have on this column, and rebuild them afterward. This is especially useful if you have many of them.

请注意位置，如果它不能被EF转换为SQL，则它将在客户端完成，这意味着通常可怕的往返加载-更改内存-更新

Careful about the condition in the Where, if it can't be translated as SQL by EF, then it will be done client side, meaning the "usual" terrible roundtrip "Load - change in memory - update"

这篇关于C＃实体框架：批量扩展输入内存问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！