CODEWITHSUNDEEP
c#entity-frameworkout-of-memory
VisualBean
VisualBean

Reputation: 5008

OutOfMemory when removing rows > 500000 EntityFramework 6

What I've got:

I have a large list of addresses(ip addr) > millions

What I'm trying to do:

Remove 500k addresses efficiently through EntityFramework

My Problem:

Right now, I'm splitting into lists of 10000 addresses and using RemoveRange(ListOfaddresses)

if (addresses.Count() > 10000)
{
    var addressChunkList = extension.BreakIntoChunks<Address>(addresses.ToList(), 10000);
    foreach (var chunk in addressChunkList)
    {
        db.Address.RemoveRange(chunk);
    }
}

but I'm getting an OutOfMemoryException which must mean that it's not freeing resources even though I'm splitting my addresses into separate lists.

What can I do to not get the OutOfMemoryException and still remove large quantities of addresses within reasonable time?

Upvotes: 3

Views: 2070

Answers (3)

reckface
reckface

Reputation: 5858

A couple of suggestions.

  1. Use a stored procedure or plain SQL

  2. Move your DbContext to a narrower scope:

    for (int i = 0; i < 500000; i += 1000)
{
  using (var db = new DbContext())
  {
    var chunk = largeListOfAddress.Take(1000).Select(a => new Address { Id = a.Id });
    db.Address.RemoveRange(chunk);
    db.SaveChanges();
  }
}

See Rick Strahl's post on bulk inserts for more details

Upvotes: 1

BenjaminPaul
BenjaminPaul

Reputation: 2931

When I have needed to do something similar I have turned to the following plugin (I am not associated).

https://github.com/loresoft/EntityFramework.Extended

This allows you to do bulk deletes using Entity Framework without having to select and load the entity into the memory first which of course is more efficient.

Example from the website:

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

Upvotes: 4

TomTom
TomTom

Reputation: 62101

So? WHere did you get the idea EF is an ETL / bulk data manipulation tool?

It is not. Doing half a million deletes in one transaction will be dead slow (delete one by one) and EF is just not done for this. As you found out.

Nothing you can do here. Start using EF within design parameters or choose an alternative approach for this bulk operations. There are cases an ORM makes little sense.

Upvotes: 4

Related Questions