是否有可能执行一个有效的多行DELETE或使用EF4更新？ [英] Is it possible to execute an efficient multiple row DELETE or UPDATE using EF4?

问题描述

我是一个开发商仍然在学习EF4的复杂性。我清楚地知道如何拉下对象的列表，并遍历删除它们在一个循环中，但我不能让自己写code，将执行ñ语句（和数据库往返）的n条记录时，做一个大规模更新或删除。

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)

在一个存储过程，我只是执行SQL，像这样：

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

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

在LINQ to SQL中我会做这样的：

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();

在NHibernate的我会做到这一点：

In NHibernate I would do this:

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

我看的EF相当于任何一个没有成功。 我必须真正回落到一个存储过程来EF4的范围内做到这一点？ 我希望不会;请分享一下。

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是一个ORM - 对象关系映射。这是伟大的映射的行和列从关系数据库到.NET对象模型 - 这最适合阅读，插入，更新一个或几个对象

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是不可以设计，并打算作为一种工具来处理大量的业务，它的不可以设计和优化做批量更新，批量删除等。这些都处理好很多无论是通过直接使用ADO.NET来执行的T-SQL命令对数据库，或者只是调用存储过程做的工作。

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.

因此​​，在这种情况下，我可能会创建一个存储过程来处理删除操作，然后导入存储过程中进入我的EF模型，这样我就可以把它像我的EntityContext的方法，让SQL服务器做繁重

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.

这篇关于是否有可能执行一个有效的多行DELETE或使用EF4更新？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！