How can I delete 1,000 rows with EF6?

Question

I am using Entity Framework 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();

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.

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?

Answer 1

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.

Answer 2

Built in Entity Framework .RemoveRange() method, still Fetches the Entries on memory , and issues X deletes looping though all of them.

If you don't want to write Any SQL for Deletion especially when selecting which entities to delete is complex

Entity Framework Plus Library offers batch delete-update methods issuing only one single command.

// Deleting
context.Users
  .Where(u => u.FirstName == "firstname")
  .Delete();

A current limitations of the Entity Framework is that in order to update or delete an entity you have to first retrieve it into memory. Now in most scenarios this is just fine. There are however some senerios where performance would suffer. Also, for single deletes, the object must be retrieved before it can be deleted requiring two calls to the database. Batch update and delete eliminates the need to retrieve and load an entity before modifying it.

Answer 3

I have made some test using EF6 and Sql Server Profiler

Using .RemoveRange()

It first fetch all record to delete from database

exec sp_executesql N'SELECT [Extent1].[Id] AS [Id], [Extent1].[IdOrder] AS [IdOrder], [Extent1].[Name] AS [Name], [Extent1].[Partita] AS [Partita], FROM [dbo].[MyTable] AS [Extent1] WHERE [Extent1].[IdOrder] = @p__linq__0',N'@p__linq__0 varchar(8000)',@p__linq__0='0cb41f32-7ccb-426a-a159-b85a4ff64c29'

Then it fire N delete command to database

exec sp_executesql N'DELETE [dbo].[MyTable] WHERE ([Id] = @0)',N'@0 varchar(50)',@0='ffea29aa-8ba5-4ac9-871b-3f5979180006'

X 1000 times

This happends also using and IQueriable

Using Entity Framework Extended Library

It fire only one command to database

exec sp_executesql N'DELETE [dbo].[MyTable] FROM [dbo].[MyTable] AS j0 INNER JOIN ( SELECT 1 AS [C1], [Extent1].[Id] AS [Id] FROM [dbo].[MyTable] AS [Extent1] WHERE [Extent1].[IdOrder] = @p__linq__0) AS j1 ON (j0.[Id] = j1.[Id])',N'@p__linq__0 nvarchar(36)',@p__linq__0=N'0cb41f32-7ccb-426a-a159-b85a4ff64c29'

Answer 4

Update to EF 7

Now EF supports batch update and delete:

Bulk Delete

context.Books
           .Where(b => b.Name == "C#")
           .ExecuteDelete();

Bulk Update

context.Books
           .Where(b => b.Name == "C#")
           .ExecuteUpdate(s => s
                        .SetProperty(b => b.Name, b => b.Name + "updated");

Answer 5

    db.TBLName.RemoveRange(db.TBLName.Where(s => s.ID > 0));
    await db.SaveChangesAsync();