Entity Framework - Efficiently Delete All Child Entities Without Loading Them

Question

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?

Answer 1

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.

Answer 2

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.