2

Is this entity framework call actually making two trips to the database?

    var result = from p in entities.people where p.id == 6 select p;
    entities.DeleteObject(result);

It strikes me that maybe DeleteObject would force the first trip to get results and THEN, having the object to work with, would execute the delete function.

If that is the case, how do I avoid the second trip? How does one do a remove-by-query in entity framework with a single database trip?

Thanks!

EDIT

My original example was misleading, because it was a query by primary key. I guess the real question is whether there is a way to have a single-trip function that can delete items from an IQueryable. For example:

    var result = from p in entities.people where p.cityid == 6 select p;

    foreach (var r in result)
    {
        entities.DeleteObject(r);
    }

(Notice that the query is of a foreign key, so there may be multiple results).

carlbenson
  • 3,177
  • 5
  • 35
  • 54
  • 1
    This has been asked before -- this answer http://stackoverflow.com/questions/869209/bulk-deleting-in-linq-to-entities/870081#870081 links to articles describing how to implement batch updates in the form of `UPDATE ... SET ... WHERE ...`, which you should be able to adapt to create `DELETE ... WHERE ...` queries. –  Jan 15 '12 at 22:42

2 Answers2

1

Direct delete: 

var people = new people() { id = 6 };
entities.people.Attach(people);
entities.people.Remove(people);
entities.SaveChanges();

If you want to see it for yourself, fire up a profiler.

EDIT:

This will allow you to use Linq but it won't be one trip. 

var peopleToDelete = entities.people.Where(p => p.id == 6);

foreach (var people in peopleToDelete )
    entities.people.DeleteObject(people );

entities.SaveChanges();

There's no easy way to do that out of the box in EF, a big annoyance indeed (as long as one does not want to resort to using direct SQL, which personally I don't). One of the other posters links to an answer that in turn links to this article, which describes a way to make your own function for this, using ToTraceString.

diggingforfire
  • 3,359
  • 1
  • 23
  • 33
  • Thanks diggingforfire. Please see my edits though -- my original question wasn't specific enough. – carlbenson Jan 15 '12 at 22:23
  • My understanding of deferred execution is that foreach, as in your example, is a trigger that forces a db call. Check out: http://blogs.msdn.com/b/charlie/archive/2007/12/09/deferred-execution.aspx – carlbenson Jan 15 '12 at 23:07
  • I should've clarified what I meant by 'best bet', as that referred to still being able to use Linq, not the one way trip. I've updated my answer. – diggingforfire Jan 15 '12 at 23:17
1

You can do it like this:

  entities.ExecuteStoreCommand("DELETE FROM people WHERE people.cityid={0}", 6);

this is one trip to the database for sure, and effective as it can be.

EDIT: Also, take a look here, they suggest the same solution. And to answer the question, this is the only way to delete entities, not referenced by primary key, from a database using entity framework, without fetching these entities (and without writing some helper extension methods like suggested in this answer).

Community
  • 1
  • 1
Aleksandar Vucetic
  • 14,715
  • 9
  • 53
  • 56