我如何与EF6删除1000行？ [英] How can I delete 1,000 rows with EF6?

问题描述

我使用实体框架6。

我有测试信息称作测试表。我删除
通过首先获得测试列表，在做这个表中的行
一个删除每个然后提交。

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();

我有问题的资料被称为问题另一个表。
我想这样做，但有这1000多行
表。如果我全部列出来，然后做1000会删除此
不是很有效。

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.

问题这个删除并不经常发生。是否
任何人有一个建议，我怎么能做到这一点。我应该怎么办
1000删除。这是正常使用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 ，你可以利用这个新的扩展方法。有了您的单位工作背景，你可以有，而不是一个单一的测试这需要一个IEnumerable（*？）你的删除方法的重载对象像当前的方法。这种新的重载应该调用上的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＆LT;＆GT; 涵盖一个的IList＆LT;＆GT; 和的IQueryable＆LT;＆GT;

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

修改

一对夫妇的意见。首先，我不会把 .ToList（）发行前的 RemoveRange 因为你不希望实际获取项目为你服务。这应该有助于减少一些性能倍。第二，你是对的，那种，你仍然会发出1000 DELETE语句。然而，性能提升来自于不调用EF的ChangeTracker为您从 DbSet 删除个别项目。从 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来自社区成员德索里利亚贡献。每
  方法作为其参数可枚举一个单一的实体类型。
  在共享DbTransactions部分的第一个code样品，我用
  的AddRange，当我在赌场实例的数组传递：

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（新[] {casino1，casino2}）;这些方法
  执行比在时间添加或移除单个对象快得多
  因为默认情况下，实体框架在调用DetectChanges每添加
  和删除方法。随着范围的方法，你可以同时处理多
  而DetectChanges对象只调用一次，从而提高性能
  大幅提升。我已经使用五，50，500，5000，甚至这个测试
  50000对象，至少在我的情况，有没有限制
  在大小排列，而且它的IM pressively快！请记住，这
  改进是仅在获得作用于上下文相关
  对象，并且对调用SaveChanges没有轴承。调用的SaveChanges仍
  在同一时间只执行一个数据库命令。因此，尽管你可以快速
  新增5物体进入一个背景下，你仍然会得到50000插入
  当你调用调用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去不支持大宗交易的方式存储的过程。然而，使用 RemoveRange 和的AddRange 项目（尤其是如果像你说的，是很少）会为你节省很多的时间相比于调用删除（）在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删除1000行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！