LINQ-to-SQL与存储过程? [英] LINQ-to-SQL vs stored procedures?

问题描述

我在StackOverflow上查看了"LINQ入门指南"( LINQ入门指南)，但有一个后续问题:

I took a look at the "Beginner's Guide to LINQ" post here on StackOverflow (Beginners Guide to LINQ), but had a follow-up question:

我们将要启动一个新项目，其中几乎所有数据库操作都将是相当简单的数据检索(该项目的另一部分已经在写数据了).到目前为止，我们的大多数其他项目都使用存储过程来处理此类事情.但是，如果更有意义，我想利用LINQ-to-SQL.

We're about to ramp up a new project where nearly all of our database op's will be fairly simple data retrievals (there's another segment of the project which already writes the data). Most of our other projects up to this point make use of stored procedures for such things. However, I'd like to leverage LINQ-to-SQL if it makes more sense.

所以，问题是这样的:对于简单的数据检索，LINQ-to-SQL或存储的proc哪种方法更好?任何特定的优点或缺点?

So, the question is this: For simple data retrievals, which approach is better, LINQ-to-SQL or stored procs? Any specific pro's or con's?

谢谢.

推荐答案

LINQ相对于proc的一些优势:

Some advantages of LINQ over sprocs:

1. 类型安全性:我想我们都理解这一点.
2. 抽象:对于 LINQ-到实体.这种抽象还允许框架添加可以轻松利用的其他改进. PLINQ 是向LINQ添加多线程支持的示例.最小的代码更改即可添加此支持.要做这种简单地调用sproc的数据访问代码要困难得多.
3. 调试支持:我可以使用任何.NET调试器来调试查询.使用sproc，您将无法轻松调试SQL，并且经验很大程度上取决于数据库供应商(MS SQL Server提供了查询分析器，但通常这还不够).
4. 不可知的供应商:LINQ可用于许多数据库，并且支持的数据库数量只会增加.由于语法或功能支持的变化(如果数据库完全支持存储过程)，存储过程在数据库之间并不总是可移植的.
5. 部署:其他人已经提到了这一点，但是部署单个程序集比部署一组sproc更容易.这也与#4相关.
6. 更容易:您不必学习T-SQL来进行数据访问，也不必学习调用sproc所需的数据访问API(例如ADO.NET).这与#3和#4有关.

1. Type safety: I think we all understand this.
2. Abstraction: This is especially true with LINQ-to-Entities. This abstraction also allows the framework to add additional improvements that you can easily take advantage of. PLINQ is an example of adding multi-threading support to LINQ. Code changes are minimal to add this support. It would be MUCH harder to do this data access code that simply calls sprocs.
3. Debugging support: I can use any .NET debugger to debug the queries. With sprocs, you cannot easily debug the SQL and that experience is largely tied to your database vendor (MS SQL Server provides a query analyzer, but often that isn't enough).
4. Vendor agnostic: LINQ works with lots of databases and the number of supported databases will only increase. Sprocs are not always portable between databases, either because of varying syntax or feature support (if the database supports sprocs at all).
5. Deployment: Others have mentioned this already, but it's easier to deploy a single assembly than to deploy a set of sprocs. This also ties in with #4.
6. Easier: You don't have to learn T-SQL to do data access, nor do you have to learn the data access API (e.g. ADO.NET) necessary for calling the sprocs. This is related to #3 and #4.

LINQ vs sprocs的一些缺点:

Some disadvantages of LINQ vs sprocs:

1. 网络流量:当LINQ发送整个查询时，存储过程仅需要通过电线序列化存储过程名称和参数数据.如果查询非常复杂，这可能会变得很糟糕.但是，LINQ的抽象使Microsoft可以随着时间的推移改进它.
2. 缺乏灵活性:存储过程可以充分利用数据库的功能集. LINQ在其支持方面趋向于更加通用.这在任何一种语言抽象中都是很常见的(例如C#与汇编器).
3. 重新编译:如果需要更改数据访问方式，则需要重新编译，版本化和重新部署程序集. Sproc有时可以 允许DBA调整数据访问例程，而无需重新部署任何东西.

1. Network traffic: sprocs need only serialize sproc-name and argument data over the wire while LINQ sends the entire query. This can get really bad if the queries are very complex. However, LINQ's abstraction allows Microsoft to improve this over time.
2. Less flexible: Sprocs can take full advantage of a database's featureset. LINQ tends to be more generic in it's support. This is common in any kind of language abstraction (e.g. C# vs assembler).
3. Recompiling: If you need to make changes to the way you do data access, you need to recompile, version, and redeploy your assembly. Sprocs can sometimes allow a DBA to tune the data access routine without a need to redeploy anything.

安全性和可管理性也是人们争论的话题.

Security and manageability are something that people argue about too.

1. 安全性:例如，您可以通过直接限制对表的访问来保护敏感数据，并将ACL放在存储过程中.但是，使用LINQ，您仍然可以限制对表的直接访问，而可以将ACL放在可更新的表视图上，以达到类似的目的(假设您的数据库支持可更新的视图).
2. 可管理性:使用视图还为您提供了保护您的应用程序不受架构更改(例如表规范化)影响的优点.您可以更新视图而无需更改数据访问代码.

1. Security: For example, you can protect your sensitive data by restricting access to the tables directly, and put ACLs on the sprocs. With LINQ, however, you can still restrict direct access to tables and instead put ACLs on updatable table views to achieve a similar end (assuming your database supports updatable views).
2. Manageability: Using views also gives you the advantage of shielding your application non-breaking from schema changes (like table normalization). You can update the view without requiring your data access code to change.

我曾经是一个存储专家，但是我开始倾向于使用LINQ作为一般更好的选择.如果在某些地方存储过程明显更好，那么我可能仍会编写一个存储过程，但可以使用LINQ访问它. :)

I used to be a big sproc guy, but I'm starting to lean towards LINQ as a better alternative in general. If there are some areas where sprocs are clearly better, then I'll probably still write a sproc but access it using LINQ. :)

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