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