How can I delete 1,000 rows with EF6?

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

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.

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

Edit

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 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 }); 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.

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.

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.

Leave a Comment