.NET，实体框架:对象删除 - 更快/更好

Question

有时我不得不面对一次删除许多行。它花费大量时间（例如，删除 3000 个元素 + ~21000 个相关元素大约需要 4 分钟）。

我的做法：

// removes settlement elements
var settlementElements = context.SettlementElements.Where(x => x.SettlementElementSettlementId == id);                     context.SettlementElements.RemoveRange(settlementElements);

我想知道是否只获取这些元素的 ID，使用这些 ID 创建空对象，附加它们并删除会比上面使用的删除范围更快？我的意思是这样的：

var settlementElementsIds = context.SettlementElements.Where(x => x.SettlementElementSettlementId == id).Select(x => x.SettlementElementId);
foreach (var seID in settlementElementsIds)
{
    var obj = new SettlementElements() { SettlementElementId = seID };
    context.SettlementElements.Attach(obj);
    context.SettlementElements.Remove(obj);
}

您在优化元素去除方面有一些经验吗？

Answer 1

TL;DR

如果需要正确的批量删除功能，请使用 EFPlus 包：http://entityframework-plus.net/

详

正如@Tom Lawrence所写，使用RemoveRange将提高内存循环的性能。但是，上面的代码将导致将记录检索到内存中，然后 EF 将为要删除的每条记录生成单独的 DELETE 语句，如以下示例所示：

context.Database.Log = Console.Write;
var id = 111;
var settlementElements = context.SettlementElements
    .Where(x => x.SettlementElementSettlementId == id);
context.SettlementElements.RemoveRange(settlementElements);
context.SaveChanges();

将生成此输出，假设有 3 条记录要从结算 ID=111 中删除：

Opened connection at 10/25/2017 2:08:33 PM +03:00
SELECT
    [Extent1].[SettlementElementId] AS [SettlementElementId],
    [Extent1].[SettlementElementSettlementId] AS [SettlementElementSettlementId],
    [Extent1].[Description] AS [Description]
    FROM [dbo].[SettlementElements] AS [Extent1]
    WHERE [Extent1].[SettlementElementSettlementId] = @p__linq__0
-- p__linq__0: '111' (Type = Int32, IsNullable = false)
-- Executing at 10/25/2017 2:08:33 PM +03:00
-- Completed in 5 ms with result: SqlDataReader
Closed connection at 10/25/2017 2:08:33 PM +03:00
Opened connection at 10/25/2017 2:08:33 PM +03:00
Started transaction at 10/25/2017 2:08:33 PM +03:00
DELETE [dbo].[SettlementElements]
WHERE ([SettlementElementId] = @0)
-- @0: '1111' (Type = Int32)
-- Executing at 10/25/2017 2:08:33 PM +03:00
-- Completed in 1 ms with result: 1
DELETE [dbo].[SettlementElements]
WHERE ([SettlementElementId] = @0)
-- @0: '1112' (Type = Int32)
-- Executing at 10/25/2017 2:08:33 PM +03:00
-- Completed in 0 ms with result: 1
DELETE [dbo].[SettlementElements]
WHERE ([SettlementElementId] = @0)
-- @0: '1113' (Type = Int32)
-- Executing at 10/25/2017 2:08:33 PM +03:00
-- Completed in 0 ms with result: 1
Committed transaction at 10/25/2017 2:08:33 PM +03:00
Closed connection at 10/25/2017 2:08:33 PM +03:00

如果我们有数百万条记录要删除，这绝对不是一个选择！

使用 EFPlus 时，以下代码：

using Z.EntityFramework.Plus;
// ...
context.Database.Log = Console.Write;
var id = 111;
var settlementElements = context.SettlementElements
    .Where(x => x.SettlementElementSettlementId == id);
settlementElements.Delete(); // <-- proper bulk delete method
context.SaveChanges();

将为相同的示例数据生成此输出：

Opened connection at 10/25/2017 2:20:29 PM +03:00
DECLARE @rowAffected INT
DECLARE @totalRowAffected INT
SET @totalRowAffected = 0
WHILE @rowAffected IS NULL
    OR @rowAffected > 0
    BEGIN
        DELETE TOP (4000)
        FROM    A
        FROM    [dbo].[SettlementElements] AS A
                INNER JOIN ( SELECT
    [Extent1].[SettlementElementId] AS [SettlementElementId]
    FROM [dbo].[SettlementElements] AS [Extent1]
    WHERE [Extent1].[SettlementElementSettlementId] = @p__linq__0
                           ) AS B ON A.[SettlementElementId] = B.[SettlementElementId]
        SET @rowAffected = @@ROWCOUNT
        SET @totalRowAffected = @totalRowAffected + @rowAffected
    END
SELECT  @totalRowAffected
-- p__linq__0: '111' (Type = Int32, IsNullable = false)
-- Executing at 10/25/2017 2:20:29 PM +03:00
-- Completed in 2 ms with result: 3
Closed connection at 10/25/2017 2:20:29 PM +03:00

我们可以看到：

• SELECT语句消失了;没有提取到内存中
• 只有单个DELETE语句，而不是每条记录一个

Answer 2

每当我不得不进行任何形式的批量处理时，我都会发现通过在存储过程中进行处理可以提高速度。您可以通过创建删除和比较来运行快速测试，如果存储过程是您的选项，我可以提供帮助。

您也可以尝试使用 RemoveRange 而不是存储过程（如果在您的版本中可用） （ef6）

db.SettleElements.RemoveRange(itemstodelete);
db.SaveChanges();

文档说RemoveRange的性能可能比多次调用Move要好得多。

Source https://msdn.microsoft.com/en-us/library/system.data.entity.dbset.removerange（v=vs.113）.aspx

Answer 3

正如@felix-b回答的那样，

如果你能使用它，最快的方法是使用我的免费库实体框架加+BatchDelete。

如果您已经拥有要删除的所有实体，则另一种技术是使用 BulkDelete .

免责声明：我是项目实体框架扩展的所有者

此库不是免费的，但允许您执行所有批量操作，包括应用程序所需的批量删除：

• 批量保存更改
• 批量插入
• 批量更新
• 批量删除
• 批量合并
• 批量同步

例：

// Easy to use
context.BulkSaveChanges();
// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);
// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);
// Customize Primary Key
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});