Efficient way to delete multiple rows with Linq to Entity?

Question

Hi I'm looking for efficient way to delete multiple records at once. I'm deleting 400 records and it takes 8-15 seconds. Here is my code

using (var entities = new Entity())
   {                               
       foreach (Item item in entities.Items.Where(x => x.id == id))
              entities.DeleteObject(item);
       entities.SaveChanges();
   }

possible duplicate of [Bulk-deleting in LINQ to Entities](http://stackoverflow.com/questions/869209/bulk-deleting-in-linq-to-entities) — Craig Stuntz, May 20 '11 at 13:33

score 3 · Answer 1 · answered Dec 24 '12 at 18:23

3

You can do it faster using EntityFramework.Extensions
1) First install EntityFramework.Extensions using NuGet

2) Here is the code similar to Linq2Sql's DeleteAllOnSubmit():

using EntityFramework.Extensions;

....

using (var entities = new Entity())
{                               
    entities.Items.Delete(x => x.id == id);
    entities.SaveChanges();
}

...

answered Dec 24 '12 at 18:23

Rajesh

1,459
10
17

It requires package `EntityFramework.Extended` https://github.com/loresoft/EntityFramework.Extended – Jaider Sep 13 '16 at 19:08

score 1 · Answer 2 · edited May 23 '17 at 12:07

1

Check out Bulk-deleting in LINQ to Entities or PLINQO for Entity Framework if it's the sort of delete you could do in a single batch, i.e.

DELETE FROM Entities WHERE [some condition]

Otherwise, maybe check you've got an index on the x column you're using to find each record.

edited May 23 '17 at 12:07

Community

1
1

answered May 19 '11 at 14:30

Chris Fulstow

41,170
10
86
110

query.Delete(); What logic Do I need to implement in Delete method? foreach loop and do the same ? How it will different? – Michael Born May 19 '11 at 14:31

score 0 · Answer 3 · answered Oct 18 '12 at 22:49

0

In your loop:

using (var entities = new Entity()) 
{                                
    foreach (Item item in entities.Items.Where(x => x.id == id)) 
           entities.DeleteObject(item); 
    entities.SaveChanges(); 
}

If you move the entities.SaveChanges(); so that it runs after the loop, it will run substantially faster.

using (var entities = new Entity()) 
{                                
    foreach (Item item in entities.Items.Where(x => x.id == id)) 
          entities.DeleteObject(item); 
} 
entities.SaveChanges();

answered Oct 18 '12 at 22:49

Ken

50
1

3

-1: This code stub makes no sense, since the call to SaveChanges is placed outside the using scope where the entities variable does not exist. – reSPAWNed Feb 11 '13 at 09:29
1

Actually, this makes perfect sense. Correct that he mis-typed on accident, but this is the most correct answer from what I can tell. Just have to edit it a little, which I'll do now. – Suamere Aug 26 '13 at 16:55

Efficient way to delete multiple rows with Linq to Entity?

3 Answers3