Deleting a collection in an entity

Question

I have a entity that has a collection of entities and I wish to delete that collection. The collection is 15K or so and it takes a long time to do a normal delete.

Page objPage = context.Pages.Where(p => p.PageID == "27486451851").First();
objPage.UserPosts.ForEach(x => { context.Posts.Remove(x); });

How can I get better performance on this?

http://stackoverflow.com/questions/2519866/how-do-i-delete-multiple-rows-in-entity-framework-without-foreach — Steven Wexler, Apr 29 '13 at 19:15

score 1 · Answer 1 · answered Apr 29 '13 at 19:38

1

Use:

context.ExecuteStoreCommand("DELETE FROM [UsersPosts] WHERE PageID = @0", 27486451851);

answered Apr 29 '13 at 19:38

Martin Mulder

12,642
3
25
54

score 1 · Answer 2 · answered Apr 29 '13 at 19:52

I've observed significant performance degradation while adding and removing a large number of entities with DbContext.Configuration.AutoDetectChangesEnabled = true. For some strange reason, EF calls DbContext.ChangeTracker.DetectChanges() under the hood each time you call DbSet<T>.Add() or DbSet<T>.Remove(). This causes the ChangeTracker to iterate through every entity in the entire collection looking for changes with each addition/removal. This seems completely unnecessary.

A possible solution is the following, which puts off change detection until you have removed all of your Post entities, thereby calling context.ChangeTracker.DetectChanges() only once.

context.Configuration.AutoDetectChangesEnabled = false;
Page objPage = context.Pages.Where(p => p.PageID == "27486451851").First();
objPage.UserPosts.ForEach(x => { context.Posts.Remove(x); });
context.ChangeTracker.DetectChanges();
context.SaveChanges();
context.Configuration.AutoDetectChangesEnabled = true;

I would be careful to judge the authors of the EF to quickly about "unnecessary change checks." The Entity being removed could itself have a two sided Navigation Property. That said, the automatic Detect changes makes Add en Remove operations indeed very slow. — Dabblernl, Apr 29 '13 at 22:14

score -1 · Answer 3 · answered Apr 29 '13 at 19:16

-1

using (var context = new DatabaseEntities())
{
    context.ExecuteStoreCommand("DELETE FROM Pages WHERE PageID == {0}", "27486451851");
}

answered Apr 29 '13 at 19:16

AaronLS

37,329
20
143
202

Deleting a collection in an entity

3 Answers3