实体框架 VS LINQ to SQL VS ADO.NET 与存储过程？

Entity Framework VS LINQ to SQL VS ADO.NET with stored procedures?

提问by BritishDeveloper

How would you rate each of them in terms of:

您如何评价他们中的每一个：

1. Performance
2. Speed of development
3. Neat, intuitive, maintainable code
4. Flexibility
5. Overall

1. 表现
2. 发展速度
3. 整洁、直观、可维护的代码
4. 灵活性
5. 总体

I like my SQL and so have always been a die-hard fan of ADO.NET and stored procedures but I recently had a play with Linq to SQL and was blown away by how quickly I was writing out my DataAccess layer and have decided to spend some time really understanding either Linq to SQL or EF... or neither?

我喜欢我的 SQL，所以一直是 ADO.NET 和存储过程的铁杆粉丝，但我最近玩了 Linq to SQL，被我写出数据访问层的速度震惊了，并决定花有一段时间真正了解 Linq to SQL 或 EF ... 或者两者都不了解？

I just want to check, that there isn't a great flaw in any of these technologies that would render my research time useless. E.g. performance is terrible, it's cool for simple apps but can only take you so far.

我只是想检查一下，这些技术中的任何一个都没有导致我的研究时间无用的大缺陷。例如，性能很糟糕，它对于简单的应用程序来说很酷，但只能带你走这么远。

Update:Can you concentrate on EF VS L2S VS SPs rather than ORM VS SPs. I'm mainly interested by EF VS L2S. But am keen to have them compared against stored procs too since plain SQl is something I know a lot about.

更新：您能否专注于 EF VS L2S VS SP 而不是 ORM VS SP。我主要对 EF VS L2S 感兴趣。但是我也很想将它们与存储过程进行比较，因为普通的 SQL 是我非常了解的东西。

采纳答案by Dave Markle

First off, if you're starting a new project, go with Entity Framework ("EF") - it now generates much better SQL (more like Linq to SQL does) and is easier to maintain and more powerful than Linq to SQL ("L2S"). As of the release of .NET 4.0, I consider Linq to SQL to be an obsolete technology. MS has been very open about not continuing L2S development further.

首先，如果您要开始一个新项目，请使用实体框架（“EF”）——它现在可以生成更好的 SQL（更像 Linq to SQL），并且比 Linq to SQL 更易于维护和更强大（“ L2S”）。随着 .NET 4.0 的发布，我认为 Linq to SQL 是一种过时的技术。MS 对于不再继续 L2S 开发持非常开放的态度。

1) Performance

1) 性能

This is tricky to answer. For most single-entity operations (CRUD) you will find just about equivalent performance with all three technologies. You do have to know how EF and Linq to SQL work in order to use them to their fullest. For high-volume operations like polling queries, you may want to have EF/L2S "compile" your entity query such that the framework doesn't have to constantly regenerate the SQL, or you can run into scalability issues. (see edits)

这很难回答。对于大多数单实体操作 ( CRUD)，您会发现所有三种技术的性能几乎相同。您必须知道 EF 和 Linq to SQL 如何工作才能充分利用它们。对于轮询查询等大容量操作，您可能希望让 EF/L2S“编译”您的实体查询，这样框架就不必不断地重新生成 SQL，否则您可能会遇到可伸缩性问题。（见编辑）

For bulk updates where you're updating massive amounts of data, raw SQL or a stored procedure will always perform better than an ORM solution because you don't have to marshal the data over the wire to the ORM to perform updates.

对于要更新大量数据的批量更新，原始 SQL 或存储过程总是比 ORM 解决方案执行得更好，因为您不必通过线路将数据编组到 ORM 来执行更新。

2) Speed of Development

2）发展速度

In most scenarios, EF will blow away naked SQL/stored procs when it comes to speed of development. The EF designer can update your model from your database as it changes (upon request), so you don't run into synchronization issues between your object code and your database code. The only time I would not consider using an ORM is when you're doing a reporting/dashboard type application where you aren't doing any updating, or when you're creating an application just to do raw data maintenance operations on a database.

在大多数情况下，EF 会在开发速度方面击败裸 SQL/存储过程。EF 设计器可以在您的数据库更改时（根据请求）更新您的模型，因此您不会遇到对象代码和数据库代码之间的同步问题。我唯一不会考虑使用 ORM 的时候是当你在做一个不做任何更新的报告/仪表板类型的应用程序时，或者当你创建一个应用程序只是为了对数据库进行原始数据维护操作时。

3) Neat/Maintainable code

3）整洁/可维护的代码

Hands down, EF beats SQL/sprocs. Because your relationships are modeled, joins in your code are relatively infrequent. The relationships of the entities are almost self-evident to the reader for most queries. Nothing is worse than having to go from tier to tier debugging or through multiple SQL/middle tier in order to understand what's actually happening to your data. EF brings your data model into your code in a very powerful way.

毫无疑问，EF 击败了 SQL/sprocs。因为您的关系是建模的，所以代码中的联接相对较少。对于大多数查询，实体的关系对于读者来说几乎是不言而喻的。没有什么比必须逐层调试或通过多个 SQL/中间层才能了解数据实际发生的情况更糟糕的了。EF 以一种非常强大的方式将您的数据模型带入您的代码中。

4) Flexibility

4) 灵活性

Stored procs and raw SQL are more "flexible". You can leverage sprocs and SQL to generate faster queries for the odd specific case, and you can leverage native DB functionality easier than you can with and ORM.

存储过程和原始 SQL 更“灵活”。您可以利用 sprocs 和 SQL 为奇怪的特定情况生成更快的查询，并且您可以比使用 ORM 更容易地利用本机 DB 功能。

5) Overall

5) 总体

Don't get caught up in the false dichotomy of choosing an ORM vs using stored procedures.You can use both in the same application, and you probably should. Big bulk operations should go in stored procedures or SQL (which can actually be called by the EF), and EF should be used for your CRUD operations and most of your middle-tier's needs. Perhaps you'd choose to use SQL for writing your reports. I guess the moral of the story is the same as it's always been. Use the right tool for the job. But the skinny of it is, EF is very good nowadays (as of .NET 4.0). Spend some real time reading and understanding it in depth and you can create some amazing, high-performance apps with ease.

不要陷入选择 ORM 与使用存储过程的错误二分法中。您可以在同一个应用程序中同时使用两者，而且您可能应该这样做。大批量操作应该在存储过程或 SQL（实际上可以由 EF 调用）中进行，并且 EF 应该用于您的 CRUD 操作和大多数中间层的需求。也许您会选择使用 SQL 来编写报告。我想这个故事的寓意和往常一样。为工作使用正确的工具。但最重要的是，EF 现在非常好（从 .NET 4.0 开始）。花一些时间阅读并深入理解它，您可以轻松创建一些令人惊叹的高性能应用程序。

EDIT: EF 5 simplifies this part a bit with auto-compiled LINQ Queries, but for real high volume stuff, you'll definitely need to test and analyze what fits best for you in the real world.

编辑：EF 5 通过自动编译的 LINQ Queries稍微简化了这部分，但是对于真正的高容量内容，您肯定需要测试和分析在现实世界中最适合您的内容。

回答by Dave Markle

Stored procedures:

存储过程：

(+)

(+)

• Great flexibility
• Full control over SQL
• The highest performance available

• 极大的灵活性
• 完全控制 SQL
• 可用的最高性能

(-)

(-)

• Requires knowledge of SQL
• Stored procedures are out of source control
• Substantial amount of "repeating yourself" while specifying the same table and field names. The high chance of breaking the application after renaming a DB entity and missing some references to it somewhere.
• Slow development

• 需要 SQL 知识
• 存储过程不受源代码控制
• 大量的“重复自己”同时指定相同的表和字段名称。在重命名数据库实体并在某处丢失对它的一些引用后，破坏应用程序的可能性很高。
• 发展缓慢

ORM:

ORM：

(+)

(+)

• Rapid development
• Data access code now under source control
• You're isolated from changes in DB. If that happens you only need to update your model/mappings in one place.

• 快速发展
• 数据访问代码现在处于源代码控制之下
• 您与数据库中的更改隔离开来。如果发生这种情况，您只需在一处更新您的模型/映射。

(-)

(-)

• Performance may be worse
• No or little control over SQL the ORM produces (could be inefficient or worse buggy). Might need to intervene and replace it with custom stored procedures. That will render your code messy (some LINQ in code, some SQL in code and/or in the DB out of source control).
• As any abstraction can produce "high-level" developers having no idea how it works under the hood

• 性能可能更差
• 对 ORM 生成的 SQL 没有或几乎没有控制（可能效率低下或更糟糕的错误）。可能需要进行干预并将其替换为自定义存储过程。这将使您的代码变得混乱（代码中的某些 LINQ、代码中的某些 SQL 和/或源代码控制之外的数据库中）。
• 因为任何抽象都会产生“高级”开发人员不知道它是如何工作的

---

---

The general tradeoff is between having a great flexibility and losing lots of time vs. being restricted in what you can do but having it done very quickly.

一般的权衡是在具有很大的灵活性和浪费大量时间与受限于您可以做的事情但很快完成之间。

There is no general answer to this question. It's a matter of holy wars. Also depends on a project at hand and your needs. Pick up what works best for you.

这个问题没有普遍的答案。这是圣战的问题。还取决于手头的项目和您的需求。选择最适合您的方法。

回答by BlackICE

your question is basically O/RM's vs hand writing SQL

您的问题基本上是 O/RM 与手写 SQL

Using an ORM or plain SQL?

使用 ORM 还是普通 SQL？

Take a look at some of the other O/RM solutions out there, L2S isn't the only one (NHibernate, ActiveRecord)

看看其他一些 O/RM 解决方案，L2S 不是唯一的（NHibernate、ActiveRecord）

http://en.wikipedia.org/wiki/List_of_object-relational_mapping_software

http://en.wikipedia.org/wiki/List_of_object-relational_mapping_software

to address the specific questions:

解决具体问题：

1. Depends on the quality of the O/RM solution, L2S is pretty good at generating SQL
2. This is normally much faster using an O/RM once you grok the process
3. Code is also usually much neater and more maintainable
4. Straight SQL will of course get you more flexibility, but most O/RM's can do all but the most complicated queries
5. Overall I would suggest going with an O/RM, the flexibility loss is negligable

1. 取决于 O/RM 解决方案的质量，L2S 非常擅长生成 SQL
2. 一旦你掌握了这个过程，这通常会更快地使用 O/RM
3. 代码通常也更整洁，更易于维护
4. 直接 SQL 当然会给你带来更大的灵活性，但大多数 O/RM 可以做除了最复杂的查询之外的所有事情
5. 总的来说，我建议使用 O/RM，灵活性损失可以忽略不计

回答by Marcelo Cantos

LINQ-to-SQL is a remarkable piece of technology that is very simple to use, and by and large generates very good queries to the back end. LINQ-to-EF was slated to supplant it, but historically has been extremely clunky to use and generated far inferior SQL. I don't know the current state of affairs, but Microsoft promised to migrate all the goodness of L2S into L2EF, so maybe it's all better now.

LINQ-to-SQL 是一项非凡的技术，它使用起来非常简单，并且总的来说可以生成非常好的后端查询。LINQ-to-EF 计划取代它，但从历史上看，它使用起来非常笨拙，并且生成的 SQL 远不如它。我不知道目前的情况，但微软承诺将 L2S 的所有优点迁移到 L2EF，所以也许现在一切都会好起来。

Personally, I have a passionate dislike of ORM tools (see my diatribe herefor the details), and so I see no reason to favour L2EF, since L2S gives me all I ever expect to need from a data access layer. In fact, I even think that L2S features such as hand-crafted mappings and inheritance modeling add completely unnecessary complexity. But that's just me. ;-)

就个人而言，我的ORM工具（见我的谩骂一个充满激情的厌恶这里的细节），所以我认为没有理由青睐L2EF，因为L2S给了我所有我曾经想到需要从数据访问层。事实上，我什至认为手工映射和继承建模等 L2S 特性完全增加了不必要的复杂性。但这只是我。;-)

回答by Vincent

There is a whole new approach that you may want to consider if what you're after is the power and performance of stored procedures, and the rapid development that tools like Entity Framework provide.

如果您追求的是存储过程的功能和性能，以及实体框架等工具提供的快速开发，那么您可能需要考虑一种全新的方法。

I've taken SQL+ for a test drive on a small project, and it is really something special. You basically add what amounts to comments to your SQL routines, and those comments provide instructions to a code generator, which then builds a really nice object oriented class library based on the actual SQL routine. Kind of like entity framework in reverse.

我已经将 SQL+ 用于一个小项目的试驾，它真的很特别。您基本上将相当于注释的内容添加到 SQL 例程中，这些注释为代码生成器提供指令，然后代码生成器基于实际 SQL 例程构建一个非常好的面向对象的类库。有点像相反的实体框架。

Input parameters become part of an input object, output parameters and result sets become part of an output object, and a service component provides the method calls.

输入参数成为输入对象的一部分，输出参数和结果集成为输出对象的一部分，服务组件提供方法调用。

If you want to use stored procedures, but still want rapid development, you might want to have a look at this stuff.

如果你想使用存储过程，但仍然想要快速开发，你可能想看看这个东西。