Batch Delete with Entity Framework and Microsoft Azure

Question

I use Entity Framework 6.1, MVC5, ASP.NET 4.5 and Azure Websites (free) + Azure SQL.

When I try to delete 45 000 entities, it takes more than 45 minutes. I have navigation properties in my entities.

I tried this :

context.Contacts.RemoveRange(db.Groups.Find(id).Contacts.ToList());

And this :

foreach(var contact in db.Groups.Find(id).Contacts.ToList())
{
    context.Database.ExecuteSqlCommand("DELETE FROM Contacts WHERE ContactID = {0}", contact.ContactID);
}

I'm sure it's not normal... but what is causing this problem ?

And both ways take 45 minutes? – Rand Random Apr 14 '14 at 09:41 — Rand Random, Apr 14 '14 at 09:41

Thewads · Accepted Answer · 2014-04-14T09:53:30.060

When you are calling .ToList() you are pulling back all of those entities into memory for a start. And then by looping through these you are effectively calling that same piece of SQL 45000 times if I understand your situation.

You shouldn't need the entire object returned to delete it. I suggest only pulling out the IDs of the contacts to delete by just selecting the ID in your query:

var ids = db.Groups.Find(id).Contacts.Select(x => x.ContactId).ToList();

I would then look into implementing either EntityFrameworkExtensions library(https://github.com/loresoft/EntityFramework.Extended) to help handle this (making sure to batch up your deletes), or using a IN query, like DELETE FROM Contacts WHERE ContactID IN (1,2,3,....) but again, making sure to batch this.

The Extensions library lets you do a delete like:

m_context.Contacts.Delete(x => idList.Contains(x.ContactId));

Note however that this will execute immediately, and not when you call SaveChanges()

Overall, the code would be something like this:

public int RemoveContacts(IList<int> _ids)
{
    int index = 0;
    int numDeleted = 0;
    while (index < _ids.Count())
    {
        var batch= _ids.Skip(index).Take(MAX_BATCH_SIZE);

        //Using extensions method
        numDeleted += context.Contacts.Delete(x => batch.Contains(x.ContactId));

        //Using SQL
        context.Database.ExecuteSqlCommand("DELETE FROM Contacts WHERE ContactID IN {0}", batch);
        index += MAX_BATCH_SIZE;
    }
    return numDeleted ;
}

Great thank you. By the way, are they any tools like this for batch insert ? I tried the solution provided here : http://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework/5942176 but inserting the same 45 000 entities still takes 8 minutes... and... don't know why, but after they are all inserted, the page continues to load indefinitely like if it was stucked in savechanges(). — simonauger, Apr 14 '14 at 10:06
There isn't a great way of doing batch inserts no. I would have a look at `SqlBulkCopy` for mass inserts, but still with batching it up. Also worth using the settings on the context configuration to help a bit. `Context.Configuration.AutoDetectChangesEnabled = false;` and `Context.Configuration.ValidateOnSaveEnabled = false;` just remember to turn them back on afterwards — Thewads, Apr 14 '14 at 10:07
Ok and any idea why the page continues to load indefinitely like if it was stucked in savechanges() even if all the entities are inserted ? — simonauger, Apr 14 '14 at 10:39
That's a separate issue which I can't answer from just 'loads indefinitely'. Would suggest raising a new question for it, but I suspect it isn't to do with your SaveChnages() but rather an ajax timeout or something — Thewads, Apr 14 '14 at 10:41

Batch Delete with Entity Framework and Microsoft Azure

1 Answers1