Is it possible to execute an efficient multiple row DELETE or UPDATE using EF4?

I'm a developer still learning the intricacies of EF4. I am well aware of how to pull down a list of objects and iterate through deleting them in a loop but I can't bring myself to write code that will execute n statements (and database round-trips) for n records when doing a mass update or delete.

A classic case for this is deleting child records prior to deleting a related parent record to maintain referential integrity... (yes, I employ soft deletes by default but humor me)

In a stored procedure I'd just execute the SQL, like so:

DELETE FROM someChildTable WHERE ForeignTableId = @keyToGo
DELETE FROM parentTable WHERE Id = @keyToGo

In Linq To SQL I would do this:

dataContext.ChildrenTable.DeleteAllOnSubmit(from c in dataContext.ChildrenTable
                                            where c.ParentTableId == keyToGo
                                            select c);
dataContext.ParentTable.DeleteOnSubmit(parentToGo);
dataContext.SubmitChanges();

In NHibernate I would do this:

nhSession.CreateQuery("delete from ChildrenTable where ParentTable.Id = :keyToGo")
                        .SetInt32("keyToGo", keyToGo)
                        .ExecuteUpdate();
nhSession.Delete(parentToGo);

I've looked for the EF equivalent for any of these without success. Must I really drop back to a stored procedure to do this within the context of EF4? I hope not; please share.

Answers


EF is an ORM - object-relational mapper. It's great at mapping rows and columns from your relational database into your .NET object model - and this works best for reading, inserting, updating one or a few objects.

EF is not designed and intended as a tool to handle large operations, it's not designed and optimized to do batch updates, batch deletes etc. Those are handled much better by either using straight ADO.NET to execute those T-SQL commands against the database, or by just calling a stored procedure to do the work.

So in this situation, I would probably create a stored procedure to handle the delete operation, and then import that stored proc into my EF model so I can call it like a method on my EntityContext and let SQL Server do the heavy lifting.


Need Your Help

Rails 3.2.8 - Working with legacy system with different primary_key

ruby sql-server ruby-on-rails-3 auto-increment

I'm creating a Rails app that communicates with an old SQL database and I need to set the primary_key.

C - Strange behaviour of while and for loops

c for-loop while-loop

I am trying to write some simple code to verify how many digits each number from an array has but I got stuck with some strange behavior.