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).

Improve this question
1
  • 1
    This has been asked before -- this answer stackoverflow.com/questions/869209/… 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. Commented Jan 15, 2012 at 22:42

2 Answers 2

Reset to default
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).

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I'm sure executing a DELETE SQL statement directly is one trip, but it does not answer the question.
You are right. I've updated the answer to be a direct response to question.
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.

Improve this answer

3 Comments

Thanks diggingforfire. Please see my edits though -- my original question wasn't specific enough.
My understanding of deferred execution is that foreach, as in your example, is a trigger that forces a db call. Check out: blogs.msdn.com/b/charlie/archive/2007/12/09/…
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.