如何使用 EF6 删除 1,000 行? [英] How can I delete 1,000 rows with EF6?

问题描述

我使用的是实体框架 6.

I am using Entity Framework 6.

我有一个表，其中包含名为 Tests 的测试信息.我正在删除通过首先获取测试列表，执行此表中的行每个删除，然后提交.

I have a table with test information called Tests. I am deleting rows from this table by first getting a list of the tests, doing a delete for each and then a commit.

   var testList = _testService.GetTests(1, userId).ToList();
   testList.ForEach(_obj => _uow.Tests.Delete(_obj));
   _uow.Commit();

我有另一个表，其中包含名为 Questions 的问题信息.我想做同样的事情，但这里有超过 1000 行桌子.如果我将它们全部列出然后删除 1,000 次效率不高.

I have another table with question information called Questions. I would like to do the same but there are over 1000 rows in this table. If I list them all and then do 1,000 deletes will this not be very efficient.

这种问题的删除并不经常发生.做任何人都对我如何做到这一点有建议.我应该做1,000 次删除.用EF做这种事情正常吗?

This deletion of questions does not happen very often. Does anyone have a suggestion as to how I could do this. Should I do 1,000 deletes. Is it normal to do this kind of thing using EF?

推荐答案

据我所知，EF 6 在 DbContext 上引入了 .RemoveRange() 选项.简而言之，您可以执行以下操作:

EF 6 as far as I know introduced the .RemoveRange() option on your DbContext. So in short, you can do something like the following:

var db = new MyDbContext();
var itemsToDelete = db.MyTable.Where(x=>!x.active);
db.MyTable.RemoveRange(itemsToDelete);
db.SaveChanges();

因此，您无需执行任何类型的 foreach，而是可以利用这种新的扩展方法.使用您的工作单元上下文，您可以重载您的 Delete 方法，该方法采用 IEnumerable (?*) 而不是像当前方法一样的单个 Test 对象.这个新的重载应该调用 DbContext 上的 RemoveRange() 函数.

So instead of having to do any type of foreach, you can utilize this new extension method. With your Unit Of Work context, you could have an overload of your Delete method that takes an IEnumerable (?*) instead of a single Test object like your current method. This new overload should invoke the RemoveRange() function on the DbContext.

?* - 这取决于 GetTests() 返回的内容，但我认为 IEnumerable<> 涵盖了 IList<>和一个 IQueryable<>

?* - It depends on what GetTests() returns, but I think IEnumerable<> covers both an IList<> and an IQueryable<>

编辑

几点意见.首先，我不会在发出 RemoveRange 之前调用 .ToList()，因为您不想实际将项目提取到您的服务.这应该有助于减少一些性能时间.其次，您是对的，您仍然会发出 1000 条删除语句.但是，性能提升来自于不对您从 DbSet 中删除的每个单独项目调用 EF 中的 ChangeTracker.来自 MSDN 杂志:

A couple of comments. First, I would not call .ToList() before issuing the RemoveRange as you do not want to actually fetch the items to your service. This should help cut down on some performance times. Second, you are right, kind of, that you will still issue 1000 delete statements. However, the performance gains come from not calling the ChangeTracker in EF for each individual item you are removing from the DbSet. From MSDN magazine:

AddRange 和 RemoveRange 如前所述，AddRange 和RemoveRange 是社区成员 Zorrilla 的贡献.每个方法将单个实体类型的可枚举作为其参数.在共享 DbTransactions 部分的第一个代码示例中，我使用了AddRange 当我传入一个 Casino 实例数组时:

AddRange and RemoveRange As mentioned earlier, AddRange and RemoveRange are contributions from community member Zorrilla. Each method takes as its parameter an enumerable of a single entity type. In the first code sample in the sharing DbTransactions section, I used AddRange when I passed in an array of Casino instances:

context.Casinos.AddRange(new[] { Casino1, Casino2 });这些方法执行速度比一次添加或删除单个对象快得多因为，默认情况下，实体框架在每个 Add 中调用 DetectChanges和删除方法.使用 Range 方法，您可以处理多个DetectChanges 只调用一次，从而提高性能戏剧性地.我已经使用 5、50、500、5,000 甚至50,000 个对象，至少在我的场景中，没有限制数组的大小——而且速度非常快！请记住，这改进只与让上下文对对象，并且与 SaveChanges 无关.仍然调用 SaveChanges一次只执行一个数据库命令.所以虽然你可以很快将 50,000 个对象添加到上下文中，您仍然会得到 50,000 个插入调用 SaveChanges 时单独执行的命令 - 可能不是你想在真实系统中做的事情.

context.Casinos.AddRange(new[] { casino1, casino2 }); These methods execute much faster than adding or removing a single object at a time because, by default, Entity Framework calls DetectChanges in each Add and Remove method. With the Range methods, you can handle multiple objects while DetectChanges is called only once, improving performance dramatically. I’ve tested this using five, 50, 500, 5,000 and even 50,000 objects and, at least in my scenario, there’s no limit to the size of the array—and it’s impressively fast! Keep in mind that this improvement is only relevant in getting the context to act on the objects, and has no bearing on SaveChanges. Calling SaveChanges still executes just one database command at a time. So while you can quickly add 50,000 objects into a context, you’ll still get 50,000 insert commands executed individually when you call SaveChanges—probably not something you want to do in a real system.

另一方面，关于在不需要对象的情况下实现对批量操作的支持由 EF (bit.ly/16tMHw4) 跟踪，并用于启用批处理操作在对数据库的一次调用中一起发送多个命令(bit.ly/PegT17).这两个功能都没有包含在最初的 EF6 版本中，但两者都很重要，预计会在未来发布.

On the flip side of this, there were long discussions about implementing support for bulk operations without requiring objects to be tracked by EF (bit.ly/16tMHw4), and for batch operations to enable sending multiple commands together in a single call to the database (bit.ly/PegT17). Neither feature made it into the initial EF6 release, but both are important and slated for a future release.

如果您真的只想发出单个数据库命令，那么使用原始 SQL 语句的存储过程都可以，因为 EntityFramework 不支持批量事务.但是，与调用 Remove()<相比，使用 RemoveRange 和 AddRange 项(特别是如果，如您所说，不经常使用)将为您节省大量时间/code> 在 foreach 循环中.

If you truly want to only issue a single database command, either a stored procedure of using raw SQL statements would be the way to go since EntityFramework does not support bulk transactions. However, using the RemoveRange and AddRange items (especially if, as you said, are infrequent) will save you a lot of time compared to calling Remove() in a foreach loop.

这篇关于如何使用 EF6 删除 1,000 行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！