哪个是 C# 和 .NET 的“最佳”数据访问框架/方法？

Which is the "best" data access framework/approach for C# and .NET?

提问by Frans

(EDIT: I made it a community wiki as it is more suited to a collaborative format.)

（编辑：我将其设为社区 wiki，因为它更适合协作格式。）

There are a plethora of ways to access SQL Server and other databases from .NET. All have their pros and cons and it will never be a simple question of which is "best" - the answer will always be "it depends".

有多种方法可以从 .NET 访问 SQL Server 和其他数据库。所有人都有其优点和缺点，永远不会是一个“最好”的简单问题——答案永远是“视情况而定”。

However, I am looking for a comparison at a high level of the different approaches and frameworks in the context of different levels of systems. For example, I would imagine that for a quick-and-dirty Web 2.0 application the answer would be very different from an in-house Enterprise-level CRUD application.

但是，我正在寻找在不同系统级别的上下文中对不同方法和框架进行高级别比较。例如，我认为对于一个快速而肮脏的 Web 2.0 应用程序，答案将与内部企业级 CRUD 应用程序大不相同。

I am aware that there are numerous questions on Stack Overflow dealing with subsets of this question, but I think it would be useful to try to build a summary comparison. I will endeavour to update the question with corrections and clarifications as we go.

我知道在 Stack Overflow 上有很多关于处理这个问题的子集的问题，但我认为尝试建立一个总结比较会很有用。在我们进行时，我将努力通过更正和澄清来更新问题。

So far, this is my understanding at a high level - but I am sure it is wrong... I am primarily focusing on the Microsoft approaches to keep this focused.

到目前为止，这是我在高层次上的理解 - 但我确信这是错误的......我主要关注 Microsoft 方法来保持这一点。

ADO.NET Entity Framework

ADO.NET 实体框架

• Database agnostic
• Good because it allows swapping backends in and out
• Bad because it can hit performance and database vendors are not too happy about it
• Seems to be MS's preferred route for the future
• Complicated to learn (though, see 267357)
• It is accessed through LINQ to Entitiesso provides ORM, thus allowing abstraction in your code

• 数据库不可知
• 很好，因为它允许交换后端进出
• 不好，因为它会影响性能并且数据库供应商对此不太满意
• 似乎是MS未来的首选路线
• 学习起来很复杂（虽然，请参阅267357）
• 它通过LINQ to Entities访问，因此提供 ORM，从而允许在您的代码中进行抽象

LINQ to SQL

LINQ 到 SQL

• Uncertain future (see Is LINQ to SQL truly dead?)
• Easy to learn (?)
• Only works with MS SQL Server
• See also Pros and cons of LINQ

• 不确定的未来（请参阅LINQ to SQL 真的死了吗？）
• 简单易学 （？）
• 仅适用于 MS SQL Server
• 另请参阅LINQ 的优缺点

"Standard" ADO.NET

“标准”ADO.NET

• No ORM
• No abstraction so you are back to "roll your own" and play with dynamically generated SQL
• Direct access, allows potentially better performance
• This ties in to the age-old debate of whether to focus on objects or relational data, to which the answer of course is "it depends on where the bulk of the work is" and since that is an unanswerable question hopefully we don't have to go in to that too much. IMHO, if your application is primarily manipulating large amounts of data, it does not make sense to abstract it too much into objects in the front-end code, you are better off using stored procedures and dynamic SQL to do as much of the work as possible on the back-end. Whereas, if you primarily have user interaction which causes database interaction at the level of tens or hundreds of rows then ORM makes complete sense. So, I guess my argument for good old-fashioned ADO.NET would be in the case where you manipulate and modify large datasets, in which case you will benefit from the direct access to the backend.
• Another case, of course, is where you have to access a legacy database that is already guarded by stored procedures.

• 没有 ORM
• 没有抽象，所以你回到“自己动手”并使用动态生成的 SQL
• 直接访问，允许潜在更好的性能
• 这与是否关注对象或关系数据的古老争论有关，答案当然是“这取决于大部分工作在哪里”，因为这是一个无法回答的问题，希望我们不要不得不去太多。恕我直言，如果您的应用程序主要处理大量数据，那么在前端代码中将其抽象为对象是没有意义的，您最好使用存储过程和动态 SQL 来完成尽可能多的工作可以在后端。然而，如果您的主要用户交互导致数据库交互在数十或数百行的级别，那么 ORM 就完全有意义。所以，我想我对好的老式 ADO.NET 的论点是在您操作和修改大型数据集的情况下，
• 当然，另一种情况是您必须访问已由存储过程保护的旧数据库。

ASP.NET Data Source Controls

ASP.NET 数据源控件

Are these something altogether different or just a layer over standard ADO.NET? - Would you really use these if you had a DAL or if you implemented LINQ or Entities?

这些是完全不同的东西还是只是标准 ADO.NET 上的一个层？- 如果你有一个 DAL 或者你实现了 LINQ 或实体，你真的会使用这些吗？

NHibernate

NHibernate

• Seems to be a very powerful and powerful ORM?
• Open source

• 似乎是一个很强大很强大的ORM？
• 开源

Some other relevant links; NHibernate or LINQ to SQLEntity Framework vs LINQ to SQL

其他一些相关链接； NHibernate 或 LINQ to SQL实体框架与 LINQ to SQL

回答by Priyan R

I think LINQ to SQL is good for projects targeted for SQL Server.

我认为 LINQ to SQL 非常适合针对 SQL Server 的项目。

ADO.NET Entity Framework is better if we are targeting different databases. Currently I think a lot of providers are available for ADO.NET Entity Framework, Provider for PostgreSQL, MySQL, esql, Oracle and many other (check http://blogs.msdn.com/adonet/default.aspx).

如果我们针对不同的数据库，ADO.NET Entity Framework 会更好。目前我认为很多提供程序可用于 ADO.NET 实体框架、PostgreSQL、MySQL、esql、Oracle 和许多其他的提供程序（查看http://blogs.msdn.com/adonet/default.aspx）。

I don't want to use standard ADO.NET anymore because it's a waste of time. I always go for ORM.

我不想再使用标准的 ADO.NET，因为这是浪费时间。我总是选择 ORM。

回答by MrTelly

Having worked on 20+ different C#/ASP.NET projects I always end up using NHibernate. I often start with a completely different stack - ADO.NET, ActiveRecord, hand rolled wierdness. There are numerous reasons why NHibernate can work in a wide range of situations, but the absolutely stand out for me is the saving in time, especially when linked to code generation. You can change the datamodel, and the entities get rebuilt, but most/all the other code doesn't need to be changed.

在 20 多个不同的 C#/ASP.NET 项目上工作后，我最终总是使用NHibernate。我经常从一个完全不同的堆栈开始——ADO.NET、ActiveRecord、手工卷曲。NHibernate 可以在各种情况下工作的原因有很多，但对我来说绝对突出的是节省时间，尤其是与代码生成相关联时。您可以更改数据模型，并重建实体，但大多数/所有其他代码不需要更改。

MS does have a nasty habit of pushing technologies in this area that parallel existing open source, and then dropping them when they don't take off. Does anyone remember ObjectSpaces?

MS 确实有一个讨厌的习惯，就是在这个领域推动与现有开源并行的技术，然后在它们没有起飞时就放弃它们。有人记得 ObjectSpaces 吗？

回答by balexandre

I must say that I never used NHibernatefor the immense time that needed to start using... time wasted on the XML setup.

我必须说，我从来没有使用过NHibernate需要开始使用的大量时间......浪费在XML 设置上的时间。

I recently did a web application in MVC2, where I did choose ADO Entities Frameworkand I use Linq all the time.

我最近在MVC2 中做了一个 web 应用程序，在那里我确实选择了ADO 实体框架并且我一直使用 Linq。

I must say, I was impressed with the speed! and our site was having around 35 000 unique visitors per day, in around 60Gb bandwidth per day (I reduced radically this 60Gb number by hosting all static files in Amazon S3 - Great .NET wrapper they have, I must say).

我必须说，我对速度印象深刻！并且我们的网站每天有大约 35 000 名独立访问者，每天大约有 60 Gb 带宽（我通过在 Amazon S3 中托管所有静态文件来彻底减少这个 60Gb 的数量——我必须说，他们拥有很棒的 .NET 包装器）。

I will always go this way. It's easy to start (just add new data item, choose tables and that's it! for every change in the database we just need to refresh the model - made automatically in just 2 clicks) and it's fun to use - Linq rules!

我会一直走这条路。它很容易开始（只需添加新的数据项，选择表，就是这样！对于数据库中的每个更改，我们只需要刷新模型 - 只需单击 2 次即可自动完成）并且使用起来很有趣 - Linq 规则！

回答by Ryan Mann

Added for new technologies:

为新技术添加：

With Microsoft Sql Server out for Linux in Beta right now, I think it's ok to not be database agnostic. The .Net Core Path and MS-SQL route allows you to run on Linux servers like Ubuntu entirely with no windows dependencies.

随着 Microsoft Sql Server 现在在 Beta 版中用于 Linux，我认为不必与数据库无关。.Net Core Path 和 MS-SQL 路由允许你在像 Ubuntu 这样的 Linux 服务器上运行，完全没有 Windows 依赖。

As such, imo, a very good flow is to not use a full ORM framework or data controls and leverage the power of SSDT Visual Studio Projects (Sql Server Data Tools) and a Micro ORM.

因此，imo，一个非常好的流程是不使用完整的 ORM 框架或数据控件，而是利用 SSDT Visual Studio 项目（Sql Server 数据工具）和微型 ORM 的强大功能。

In Visual Studio you can create a Sql Server Project as a legit Visual Studio Project. Doing so allows you to create the entire database via table designers or raw query editing right inside visual studio.

在 Visual Studio 中，您可以创建一个 Sql Server 项目作为合法的 Visual Studio 项目。这样做允许您通过表设计器或直接在 Visual Studio 中进行原始查询编辑来创建整个数据库。

Secondly, you get SSDT's Schema Compare tool which you can use to compare your database project to a live database in Microsoft Sql Server and update it. You can sync your Visual Studio Project with the server causing updates in your project to go out to the server. Or you can sync the server with your project causing your source code to update. Via this route you can easily pick up changes the DBA made in maintenance last night and push out your new development changes for a new feature easily with a simple tool.

其次，您可以获得 SSDT 的架构比较工具，您可以使用该工具将您的数据库项目与 Microsoft Sql Server 中的实时数据库进行比较并更新它。您可以将 Visual Studio 项目与服务器同步，从而将项目中的更新发送到服务器。或者您可以将服务器与您的项目同步，从而更新您的源代码。通过这条路线，您可以轻松地获取 DBA 昨晚在维护中所做的更改，并使用简单的工具轻松地推出新功能的新开发更改。

Using that same tool you can compute the migration script without actually running it, if you need to pass that off to an operations department and submit a change order, it works for that flow to.

使用相同的工具，您可以在不实际运行的情况下计算迁移脚本，如果您需要将其传递给运营部门并提交变更单，它适用于该流程。

Now for writing code against you MS-SQL Database, I recommend PetaPoco.

现在为了针对 MS-SQL 数据库编写代码，我推荐 PetaPoco。

Because PetaPoco works Perfectly inline with the above SSDT solution. PetaPoco comes with T4 text templates you can use to generate all your data entity classes, and it generates the bulk data layer classes for you.

因为 PetaPoco 与上述 SSDT 解决方案完美兼容。PetaPoco 带有 T4 文本模板，您可以使用它来生成所有数据实体类，并且它会为您生成批量数据层类。

The catch is, you have to write queries yourself, which isn't a bad thing.

问题是，您必须自己编写查询，这并不是一件坏事。

So you end up with something like this:

所以你最终会得到这样的结果：

var people = dbContext.Fetch<Person>("SELECT * FROM People where Username Like '%@0%'", "bob");

PetaPoco automatically handles parameterizing @0 for you, it also has the handy Sql class for building queries.

PetaPoco 自动为您处理参数化 @0，它还具有用于构建查询的方便的 Sql 类。

Furthermore, PetaPoco is an order of magnitude faster than EF6 and 8+ times faster than EF7.

此外，PetaPoco 比 EF6 快一个数量级，比 EF7 快 8 倍以上。

So in total, this solution involves using SSDT for SCHEMA management, and PetaPoco for code integration at the gain of high maintainability, customization, and very good performance.

因此，总的来说，该解决方案涉及使用 SSDT 进行 SCHEMA 管理，并使用 PetaPoco 进行代码集成，以获得高可维护性、定制化和非常好的性能。

The only downfall to this approach, is that you're hard tieing yourself to Microsoft Sql Server. However, imo, Microsoft Sql Server is one of the best RDBM's out there.

这种方法的唯一缺点是您很难将自己绑定到 Microsoft Sql Server。但是，imo，Microsoft Sql Server 是目前最好的 RDBM 之一。

It's got DBMail, Jobs, CLR object capabilities, and on and on. Plus the integration between Visual Studio and MS-SQL server is phenomenal and you don't get any of that if you choose a different RDBMS.

它具有 DBMail、Jobs、CLR 对象功能等等。另外，Visual Studio 和 MS-SQL 服务器之间的集成是惊人的，如果您选择不同的 RDBMS，您将无法获得任何集成。