7

I'm completely new to Entity Framework, so please forgive me if my logic is skewed / this is the way things already work, but I'm working on an application where:

  1. I store Parent Information in one table with a Primary key = ParentId
  2. For each ParentId, I store thousands of records in a child table with a one-to-many foreign-key relationship on ParentId.

So, if the information ever changes for a parent (which can happen fairly often), what I would like to do is have my program perform the equivalent of:

DELETE FROM ChildTable WHERE ParentId = 'xx'

Before updating the child table with the new / updated values for the same ParentId.

From what I've seen, the way I would do that is to either:

  • Put in an actual SQL command using the ctx.Database.ExecuteSqlCommand() kind-of-concept
  • On some level, actually loop through the children elements and set them to delete before updating the DB context (which seems like it would be greatly inefficient since I'm guessing it will have to have pulled them from the DB in order to do that and all I want to do is just delete them all).

What is the correct way to do this in EF in the most efficient way possible?

John Bustos
  • 19,036
  • 17
  • 89
  • 151
  • Possible duplicate http://stackoverflow.com/questions/2519866/how-do-i-delete-multiple-rows-in-entity-framework-without-foreach – Stilgar Jun 30 '14 at 20:30
  • @Stilgar, thanks for pointing that out, I did see that question, but am still confused (maybe what I need is just more explanation) as to whether this would entail looping through all the child elements or not... That part is still not making sense to me.... – John Bustos Jun 30 '14 at 20:33
  • 1
    If you are worried about efficiency just write a Stored Procedure or raw SQL. EF has fine support for this. – Stilgar Jun 30 '14 at 20:39
  • 1
    based on your statement *I'm completely new* the term typically used for this is a bulk delete. but you won't know that, until you know that. With that in mind you would probably be able to find better results but to save you some time. Here is a good one on SO http://stackoverflow.com/questions/869209/bulk-deleting-in-linq-to-entities . To save you even more time here is a nice library that builds in a lot of functions for you. It also has good documentation on how to use it. It uses Linq FYI. https://github.com/loresoft/EntityFramework.Extended – Mabdullah Jun 30 '14 at 20:45
  • 1
    also to your point on *most efficient way* here is a quote on the Extended library you may be interested in "A current limitations of the Entity Framework is that in order to update or delete an entity you have to first retrieve it into memory. Now in most scenarios this is just fine. There are however some senerios where performance would suffer. Also, for single deletes, the object must be retrieved before it can be deleted requiring two calls to the database. Batch update and delete eliminates the need to retrieve and load an entity before modifying it." – Mabdullah Jun 30 '14 at 20:49
  • @Madullah, that is EXACTLY what I was looking for!! - Thank you and please do post it as an answer and I'll happily accept it!! – John Bustos Jun 30 '14 at 20:54
  • And, @Madullah, also, just in case you do know... There is nothing built in to EF6 that already does this, correct?? – John Bustos Jun 30 '14 at 20:56

2 Answers2

4

For simple bulk deletes, I typically just issue a SQL statement directly. ie:

context.Database
    .ExecuteSqlCommand("DELETE FROM TheTable Where MyColumn = {0}", parameter);

If you need more advanced support, then the other answer of use Ef Extended works as well. This is just a simple way to do simple queries that doesn't need additional dependencies.

Be aware, however, that when doing this the DbContext's internal cache may become out of sync with your database. So after performing such a command, it's best to not do any more Object related queries until you've newed up a new context.

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
  • Thanks so much, Erik, I was thinking that was a way to do it, just being so new to EF, I had hoped they had already programmed in something that seems so reasonably necessary... And thanks especially for the hint on no using the same context... That I'm sure will come in very handy!! – John Bustos Jul 01 '14 at 14:08
  • One more question - Given your knowledge, would you prefer to do it as you wrote or would a stored procedure be better? – John Bustos Jul 01 '14 at 14:09
  • 1
    @JohnBustos - There is very little value in a stored procedure for something this simple, unless your company requires you to use them. – Erik Funkenbusch Jul 01 '14 at 14:25
3

Bulk Delete and any Bulk update statement is a limitation off the default Entity Framework behavior. One work around is to use this extension library that allows for these bulk commands without having to pull entities into memory to delete them.

https://github.com/loresoft/EntityFramework.Extended. It also has good documentation on that site, for how to use the various functions.

Mabdullah
  • 1,013
  • 6
  • 26
  • Thank you!!! - Just curious, there is nothing built in to EF6 that already does this, correct?? – John Bustos Jun 30 '14 at 21:01
  • 1
    Not that I know of, and a quick google search didn't turn anything up. which means probably not. What I could find said this was out side of the scope of what EF was trying to do, which is strict Object Mapping. hope that helps. – Mabdullah Jun 30 '14 at 21:03
  • If you're asking because you don't want to have a third party lib, FWIW you can write these extensions yourself. the SO question in the comments had a secondary answer showing example code for a bulk delete. – Mabdullah Jun 30 '14 at 21:06
  • Madullah, I can't thank you enough... I'm just always a bigger fan of using as few "additions" as possible, so if EF6 already had such a function I'd have preferred to use it, but that library just seems clean and efficient, so why the heck not, right?? :) I'm still contemplating just writing a stored procedure and calling it from the code too, but this library seems like a great tool to use!! – John Bustos Jun 30 '14 at 21:10