实体框架存储过程与生成的SQL [英] Entity Framework Stored Procedures vs Generated SQL

问题描述

我在几个项目中使用了实体框架。在每个项目中，由于存储过程的众所周知的好处 - 安全性，可维护性等，我已经使用映射到实体的存储过程。但是，99％的存储过程是基本的CRUD存储过程。这似乎是否定了实体框架 - SQL生成的主要的，节省时间的功能之一。

我已经阅读了有关存储过程的一些论点从实体框架生成SQL。虽然使用CRUD SP是更好的安全性，由EF生成的SQL通常比必要更复杂，它是否真的购买任何在性能或可维护性使用SP？

这是我相信的：

• 大多数时候，修改SP需要更新数据模型
  。所以在可维护性方面并不是很多。


• 对于Web应用程序，与数据库的连接使用特定于应用程序的单个用户标识。所以用户甚至没有直接的数据库访问。这样可以降低安全性。


• 对于一个小型应用程序，使用
  生成的SQL略有降低的性能可能不是很大的问题。对于高
  的体积，性能关键的应用程序，EF甚至是一个明智的
  选择？另外，是否通过EF生成
  的insert / update / delete语句真的那么糟糕？


• 将每个属性发送到存储过程都有自己的性能惩罚，而EF生成的代码只发送实际改变的属性。当对大型表进行更新时，增加的网络流量和更新所有属性的开销可能否定了存储过程的性能优势。

那就是说，我的具体问题是：

我上面列出的信仰是否正确？现在ORM正在越来越受欢迎，总是使用SPs老派的想法？在您的经验中，使用EF更好的方法是使用所有插入/更新/删除的SP映射SP，或者使用EF生成的SQL进行CRUD操作，并且仅使用SP进行更复杂的操作。

解决方案

我认为 始终 使用SP是有点老学校。我以前用这种方式编写代码，现在我可以在EF生成的代码中做任何事情，当我有一个性能问题或其他特殊需要时，我再加上一个策略性的SP来解决一个特殊的问题。它不一定是或者两者都使用。

我所有的基本CRUD操作都是直接的EF生成代码 - 我的网络应用程序曾经有100个或更多的SP现在，一个典型的将有十几个SP，其他一切都是在我的C＃代码中完成的，而且我的生产力已经消除了95％的CRUD存储过程。

I've used the entity framework in a couple of projects. In every project, I've used stored procedures mapped to the entities because of the well known benefits of stored procedures - security, maintainability, etc. However, 99% of the stored procedures are basic CRUD stored procedures. This seems like it negates one of the major, time saving features of the Entity Framework -- SQL generation.

I've read some of the arguments regarding stored procedures vs. generated SQL from the Entity Framework. While using CRUD SPs is better for security, and the SQL generated by EF is often more complex than necessary, does it really buy anything in terms of performance or maintainability to use SPs?

Here is what I believe:

• Most of the time, modifying an SP requires updating the data model anyway. So, it isn't buying much in terms of maintainability.
• For web applications, the connection to the database uses a single user ID specific to the application. So, users don't even have direct database access. That reduces the security benefit.
• For a small application, the slightly decreased performance from using generated SQL probably wouldn't be much of an issue. For high volume, performance critical applications, would EF even be a wise choice? Plus, are the insert / update / delete statements generated by EF really that bad?
• Sending every attribute to a stored procedure has it's own performance penalties, whereas the EF generated code only sends the attributes that were actually changed. When doing updates to large tables, the increased network traffic and overhead of updating all attributes probably negates the performance benefit of stored procedures.

With that said, my specific questions are:

Are my beliefs listed above correct? Is the idea of always using SPs something that is "old school" now that ORMs are gaining in popularity? In your experience, which is the better way to go with EF -- mapping SPs for all insert / update / deletes, or using EF generated SQL for CRUD operations and only using SPs for more complex stuff?

解决方案

I think always using SP's is somewhat old school. I used to code that way, and now do everything I can in EF generated code...and when I have a performance problem, or other special need, I then add back in a strategic SP to solve a particular problem....it doesn't have to be either or - use both.

All my basic CRUD operations are straight EF generated code - my web apps used to have 100's or more of SP's, now a typical one will have a dozen SP's and everything else is done in my C# code....and my productivity has gone WAY up by eliminating those 95% of CRUD stored procs.

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