Delete a big list of items with Entity Framework

Question

I want to delete a big list of items with EF, so I tried to remove them one by one but it take long time.

And I tried to use .RemoveAll() method with my list but it don't update the database [only remove from loaded entity]

So I use a SqlCommand to remove them from the database and I use .RemoveAll() to prevent EF unexpected number of rows update (0) exception.

Code:

dbContext.Database.ExecuteSqlCommand("DELETE FROM xxx WHERE xxx");
loadedEntity.subItems.ToList().RemoveAll(r => true);
dbContext.SaveChanges();

My question: is there a better way to do this?

Check this out: http://stackoverflow.com/questions/15220411/entity-framework-delete-all-rows-in-table — Paweł Reszka, Nov 13 '14 at 09:24
I update the question, I don't want to delete all Items in table I just want to delete all sub Items. IE: delete all products inside market. — user1150331, Nov 13 '14 at 10:26

score 24 · Accepted Answer · answered Nov 13 '14 at 09:34

24

try this

var all = dbContext.XXX.Where(x => x.xxx == "xxx");
dbContext.XXX.RemoveRange(all);
dbContext.SaveChanges();

answered Nov 13 '14 at 09:34

1

I don't have this method .RemoveRange() in my dbContect – user1150331 Nov 13 '14 at 10:29
@user1150331 update your ef to 6.0 and you will get . – Nov 13 '14 at 12:12
Note - this is still a good response for EF/.Net Core, circa 2020. – paulsm4 Aug 16 '20 at 05:56
Still works great 23'! – Sean Haddy Mar 01 '23 at 18:55

Vladimirs · Answer 2 · 2014-11-13T10:43:01.310

1

Have a look at batch extensions. (one or two)

Deletion will be as simply as dbContext.XXX.Delete(x => x.xxx == "xxx").

edited Nov 13 '14 at 10:43

answered Nov 13 '14 at 09:23

Vladimirs

8,232
4
43
79

score 0 · Answer 3 · answered Jan 07 '20 at 08:03

Entity Framework Core

3.1 3.0 2.2 2.1 2.0 1.1 1.0

using (YourContext db = new YourContext())
{
    var foundList=db.YourDbSet.Where(c=>c.YourProp=='someVal').ToList();
    db.YourDbSet.RemoveRange(foundList);
    db.SaveChanges();
}

Summary:

Removes the given collection of entities from the context underlying the set with each entity being put into the Deleted state such that it will be deleted from the database when SaveChanges is called.

Remarks:

Note that if System.Data.Entity.Infrastructure.DbContextConfiguration.AutoDetectChangesEnabled is set to true (which is the default), then DetectChanges will be called once before delete any entities and will not be called again. This means that in some situations RemoveRange may perform significantly better than calling Remove multiple times would do. Note that if any entity exists in the context in the Added state, then this method will cause it to be detached from the context. This is because an Added entity is assumed not to exist in the database such that trying to delete it does not make sense.

You're correct. But... 1) that's basically what user4093832 already said [above](https://stackoverflow.com/a/26905494/421195) (six years ago), and 2) More often than not you'll probably already have a dbContext - there's seldom a need for "using/new Context()" in most "real world" code. — paulsm4, Aug 16 '20 at 05:54
Sorry for making you confusing. 1. I learned it from [Microsoft](https://learn.microsoft.com/en-us/dotnet/api/system.collections.generic.list-1.removerange?view=netcore-3.1). 2. It is really for the OP 's example. Thank you. — Nguyen Van Thanh, Aug 16 '20 at 08:04

Delete a big list of items with Entity Framework

3 Answers3

Linked

Related