实体框架&存储程序 [英] Entity Framework & Stored Procedures

问题描述

我在Entity Framework设计器中注意到可以映射插入，更新和删除操作的存储过程。对于选择操作，还有什么办法可以做到这一点吗，还是有数据库访问代码的新方向，我们还没有真正为基本的选择操作写入存储过程？

我所工作的公司非常坚持使用存储过程进行每个数据库操作，即使实体框架通过调用sp_executesql使呼叫安全。

LINQ to SQL和Entity Framework似乎已经不再使用存储过程来选择数据了。这是一个准确的陈述吗？

只是为了澄清我的问题：

我的数据库中有一个表，称为产品。我使用实体框架中的向导生成我的模型...所以我现在有一个实体称为产品。当我执行以下查询：

  db.Products.SingleOrDefault（p => p.Id == 1）; 
  

它生成的代码类似于：

  EXEC sp_executesql N'SELECT * FROM产品'
  

当我真的想做一些类似的事情：

  EXEC up_GetProduct @Id = 1 
  

如果不可能使用SingleOrDefault做到这一点我很好。我更愿意如下：

  db.Products.GetProduct（1）; 
  

这是通常是做的，还是大多数人只是动态生成SQL？ p>

解决方案

实体本身不允许存储过程进行选择。有几个原因离开我的头顶：

1. 如何在存储过程中添加where子句？您将不得不使用LINQ to Objects执行此操作，并且查询效率低下，或者以某种方式可以向存储过程属性添加自定义映射。虽然并不是不可能，但它肯定会引入一些实现的复杂性。


2. 实体可以具有用于在查询中进行连接的关系。使用存储过程，您再次遇到类似的问题。如果您有订单和 OrderItem 表，您如何加入？您运行 SelectOrder ，并为您运行的每个订单 SelectOrderItem （1 + n个查询），或者有一个存储过程返回两者，作为一个结果集与重复的订单数据或两个结果集。那么你必须指定如何映射到实体。如果您必须手动指定映射，则会破坏您必须设置的实体关系的目的。


3. 如何进行分页？使用LINQ to Entities，您可以将 IQueryable 公开到业务层或UI层（由您决定）。然后，然后执行修改SQL并使其高效的LINQ过滤。使用存储过程，您将再次以某种方式手动定义所有这些，或者使用LINQ对对象进行过滤。


4. LINQ允许您 select new {o .Column1，o.Column2} 。这会生成只选择您需要的2列的SQL。如果您有一个 BLOB / VARCHAR（MAX），则非常有用。通过存储过程，您通常会返回每一列（在许多方面都是浪费的）。您可以分成 GetOrderDetailMain 和 GetOrderDetailImages （或类似的）存储过程，但创建每个组合是不可行的。 / li>
   

在我看来，如果你使用EF框架，让它为你做CRUD。使用存储过程进行复杂逻辑，如全文搜索，特定查询太慢等，否则您将无法从中获益。

编辑：当然存储过程有好处。它们是预编译/预编译，定义好的数据库API，您不需要访问表（尽管使用SP CRUD，您实际上可以做同样的事情），更容易调试/调整查询，更容易知道 要求调整，可以进行批量处理等。对于简单的CRUD，您必须问问自己是否实现/管理存储过程的开销是值得的。 >

I noticed in the Entity Framework designer that you can map stored procedures for Insert, Update, and Delete operations. Is there any way to do this for Select operations as well, or is there a new direction for database access code where we don't really write stored procedures for our basic select operations any more?

The company I work for is pretty adamant about always using stored procedures for every database operation, even though the Entity Framework makes the calls safe by calling sp_executesql.

It seems like both LINQ to SQL and Entity Framework have moved away from using stored procedures for selecting the data. Is this an accurate statement?

Just to clarify my question:

I have a table in my database called Product. I use the wizard in the Entity Framework to generate my models...so I now have an Entity called Product. When I do the following query:

db.Products.SingleOrDefault(p => p.Id == 1);

It generates code similar to:

EXEC sp_executesql N'SELECT * FROM Product'

When I really want to do something like:

EXEC up_GetProduct @Id = 1

If it's not possible to do that using SingleOrDefault I'm fine with that. I'd much rather have the following:

db.Products.GetProduct(1);

Is this something that is normally done or do most people just have it dynamically generate the SQL?

解决方案

The entities themselves don't allow stored procedures for selecting. There are several reasons. Off the top of my head:

1. How would you add a where clause to your stored procedure? You would either have to do it with LINQ to Objects and have an inefficient query, or somehow be able to add custom mapping to stored procedure properties. Though not impossible, it definitely introduces some implementation complexity.
2. The entities can have relationships which are used to do joins in the queries. With stored procedures you have a similar problem again. If you have Order and OrderItem tables, how do you join? You either run SelectOrder and for each order you run SelectOrderItem (1+n queries), or have one stored procedure that returns both, either as one result set with duplicated Order data, or two result sets. Then you have to specify how that maps to the entities. If you have to manually specify the mapping, it defeats the purpose of the entity relationships you had to set up.
3. How do you do paging? With LINQ to Entities, you can expose IQueryable to the business layer or UI layer (up to you). You then do your LINQ filtering which modifies the SQL and makes it efficient. With stored procedures, you would once again have to somehow manually define all this, or do the filtering with LINQ to objects.
4. LINQ allows you to select new { o.Column1, o.Column2 }. That generates SQL which only selects those 2 columns you need. Very useful if you have a BLOB/VARCHAR(MAX). With stored procedures you usually return every column (wasteful in many ways). You CAN split into GetOrderDetailMain and GetOrderDetailImages (or similar) stored procedures, but it's not feasible to create each combination.

In my opinion, if you'll be using the EF framework, let it do CRUD for you. Use stored procedures for complex logic like full text searching, a specific query which is too slow, etc. Otherwise you won't get much benefit from it.

Edit: Of course there are benefits to stored procedures. They are preparsed/precompiled, well defined "database API", you don't need to give access to tables (though with SP CRUD, you can in effect do the same things), easier to debug/tune the queries, easier to know which queries to tune, can do batch processing, etc. For simple CRUD, though, you have to ask yourself if the time-to-implement/manage overhead of stored procedures is worth it.

这篇关于实体框架&存储程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！