LinqToSQL 是否足够强大?一个更强大但同样流畅的界面是不是很容易构建? [英] Is LinqToSQL powerful enough? Isn't a more powerful but equally fluent interface easy to construct?

问题描述

在上一个问题中，我问了 ORM 库是次优解决方案 并收到了很多很好的反馈.当我进一步考虑这个问题时，我得出结论，LinqToSQL(现在)和 LinqToEntities(未来)的主要好处在于它们能够减少过程代码和 SQL 之间的不匹配".鉴于 LinqToSQL 作为一种完整的数据查询语言的局限性，我不同意它的优势远不止于此，但我想知道其他人的意见.

In a previous question, I asked whether ORM libraries were suboptimal solutions and received a lot of great feedback. As I've thought about the issue further, I've concluded that the primary benefit of LinqToSQL (now) and the promise of LinqToEntities (down the road) lies in their ability to reduce the "mismatch" between procedural code and SQL. Given the limitations of LinqToSQL as a complete data querying language, I'm not inclined to agree that its advantages go much beyond this but I'd like to know others opinion.

首先，程序代码和 SQL 之间的不匹配"是什么意思?如果您曾经开发过数据库应用程序，那么您可能已经熟悉(并且厌倦了)与数据库交互所需的步骤:设置所有参数，输入 SQL 命令，然后将预期结果手动转换回变量或代码中使用的实例.

To start, what do I mean by a "mismatch" between procedural code and SQL? If you have ever developed a database application then you have likely become familiar with (and weary of) the steps needed to interact with a database: set up all the parameters, enter the SQL command, and then hand convert the expected results back to variables or instances used in code.

LinqToSql 通过使用流畅"接口、lambda 表达式、扩展方法等使这变得更容易.最终结果是很容易将原生 C# 标量类型推送到数据检索调用中，并且易于自动生成原生C# 类实例.以微软网站上的这个例子为例:

LinqToSql makes this much easier by the use of "fluent" interfaces, lambda expressions, extension methods, etc. The end result is that it is easy to push native C# scalar types into the data retrieval call and easy to automatically generate native C# class instances. Take this example from the MS web site:

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
foreach (var cust in q)
   Console.WriteLine("id = {0}, City = {1}",cust.CustomerID, cust.City);

非常酷！

问题是什么?

好吧，正如我在链接到顶部的文章中指出的那样，存在许多问题.对我来说最大的 障碍是，任何在 SQL 方面能力最低的人都会立即遇到障碍.不仅仅是作为 SQL 替代方案而创建的许多结构不熟悉甚至笨拙(Group By?我们在 LinqToSql 中谈论丑陋).更大的问题是有许多常见的结构本身并不支持(例如 DateDiff).充其量，您可以从 Linq 中逃脱"并将 SQL 代码提交到 Linq 调用流中.

Well, as I pointed out in the article linked to at the top, there are a number of problems. The biggest showstopper for me was that anyone who is minimally competent in SQL will immediately hit roadblocks. It isn't just that many of the constructs created as alternatives to SQL are unfamiliar or even clumsy (Group By? We're talking Ugly in LinqToSql). The bigger problem is that there are a number of common constructs that just aren't natively supported (e.g. DateDiff). At best, you can sort of "escape out" of Linq and submit SQL code into the Linq call stream.

那么，简单地将 SQL 嵌入 C#(或 VB)中，以允许您自由地声明您的 SQL 但同时也为您提供简单的参数嵌入和自动转换为本机类不是更好吗?例如，如果我们在一个类中只实现六个函数，我们可以这样写(其中 qry 是我们查询类的一个实例):

So, isn't it better to simply embed SQL in C# (or VB) in ways that permit you to freely state your SQL but that also give you easy parameter embedding and automatic conversion to native classes? For example, if we implement just six functions in a single class, we can write something like this (where qry is an instance of our query class):

var ListOfEnrollees = 
           qry.Command("Select b.FirstName, b.LastName, b.ID From ClassEnroll a inner join Folder b on (a.ClientID = b.ClientID) ")
             .Where ("a.ClassID", classID)
             .AND()
             .Append("(DateDiff(d, a.ClassDate, @ClassDate) = 0) Order By LastName;")
             .ParamVal(classDate)
             .ReturnList<EnrollListMemberData>();

Command 只是让我们开始收集 SQL 语句，Where 开始我们的 SQL Where 子句并输入第一个参数，Append在更多 SQL 上，ParamVal 只输入一个参数值来匹配上一行中找到的 SQL 参数，而 ReturnList 执行转换为类所需的魔法.请注意，这只是 SQL:连接是微不足道的，支持 DateDiff(或任何其他 SQL 构造)等.我们可以在 SQL 窗口中对其进行测试，然后将其剪切并粘贴到我们的代码中，根据需要将其分解以输入我们的参数.再简单不过了.

The Command simply starts us off collecting the SQL statement, the Where starts our SQL Where clause and enters the first parameter, Append tacks on more SQL, ParamVal just enters a parameter value to match the SQL parameter found on the previous line, and ReturnList does the magic needed to convert to a class. Notice that this is just SQL: joins are trivial, DateDiff (or any other SQL construct) is supported, etc. We can test it out in a SQL window and then just cut and paste into our code, breaking it up as appropriate to enter our parameters. It couldn't be easier.

现在，上面显示的基本构造对我来说似乎非常简单，它可以使用我已经拥有的 SQL 知识来处理任何 SQL 构造.我确实必须使用一些反射和一些其他很酷的新 C# 3 构造来使 ReturnList(和类似的)调用工作，但总的来说，它非常简单.我还添加了一些花里胡哨的东西，使界面更加流畅.所以，这是我的问题，我希望听到社区的评论:

Now, the basic construct shown above seems outrageously simple to me and it can handle ANY SQL construct using the knowledge of SQL I already have. I did have to use some reflection and some of the other cool new C# 3 constructs to make the ReturnList (and similar) calls work but, overall, it was pretty straightforward. I also added a number of bells and whistles to make the interface more fluent. So, here is my question and where I'd love to hear comments from the community:

为什么我需要掌握 LinqToEntities 的复杂性，甚至需要承担 LinqToSql 的开销?这不是给了我 LinqToSql 给我的一切吗?除了奇异的 Relational to Object 映射之外，它甚至不包括 LinqToEntities 吗?

Why would I ever need to master the intricacies of LinqToEntities or even incur the overhead of LinqToSql? Doesn't this pretty much give me everything that LinqToSql gives me and more? Doesn't it cover even LinqToEntities with the exception of exotic Relational to Object mappings?

更新:Hamish Smith 在下面认为 LINQ 可能有朝一日成为一种功能齐全的数据操作语言，功能强大到甚至不需要 SQL.这是我没有讨论但我同意的一个重要的引爆点论点.我的立场的本质是，虽然 LinqToSql 有一些实际优势，但它仍然离Hamish 的目标很远.这是一个非常真实且重要的缺点.

Update: Hamish Smith argues below that LINQ may someday be a fully capable data manipulation language so powerful that SQL isn't even needed. This is an important tipping-point argument that I didn't discuss but with which I agree. The essence of my position is that, while there are some practical advantages to LinqToSql, it still falls far short of Hamish's goal. This is a very real and significant drawback.

Hamish 还正确地指出，我仍在使用嵌入式 SQL - 我还没有解决"这个问题.然而，对我来说，这是一个功能，而不是一个错误.SQL 在选择和操作关系数据(毕竟，这是我们正在使用的数据)方面仍然要好得多，我希望能够使用它来制定我的解决方案.我认为嵌入式 SQL 不是问题，而是它与 C# 之间的阻抗不匹配.但是，通过使用 C# 3 的受 Linq 启发的新功能，我可以在很大程度上消除这种阻抗不匹配"并获得两全其美.

Hamish also argues, correctly, that I'm still working with embedded SQL - I haven't "solved" the problem. To me, however, this is a feature and not a bug. SQL is still so much better at selecting and manipulating relational data (which is, after all, what we're working with) that I want to be able to use it to craft my solution. I'm arguing that embedded SQL isn't the problem so much as the impedance mismatch between it and C#. However, by using the new, Linq-inspired features of C# 3, I can largely eliminate this "impedance mismatch" and get the best of both worlds.

推荐答案

所以，简单地嵌入不是更好吗C#(或 VB)中的 SQL 以允许的方式你可以自由地陈述你的 SQL 但那还为您提供简单的参数嵌入并自动转换为原生课程?

So, isn't it better to simply embed SQL in C# (or VB) in ways that permit you to freely state your SQL but that also give you easy parameter embedding and automatic conversion to native classes?

我通常所做的与此非常接近.我在存储过程中编写 SQL，因此我获得了真正 SQL 的自由.在 C# 中，我使用 Linq 的 DBML 以自然的方式访问存储过程.这感觉就像两全其美.

What I usually do comes very close to this. I write the SQL in a stored procedure, so I get the freedom of real SQL. In C# I use Linq's DBML to access the stored procedures in a natural way. This feels like the best of both worlds.

对我来说，真正的解决方案看起来更简单:在 C# 代码中嵌入 SQL 并让编译器生成强类.如果 Visual Studio 支持这一点，我怀疑有人会使用其他任何东西.

The real solution looks even simpler to me: embed SQL in C# code and have the compiler generate the strong classes. If Visual Studio supported that, I doubt anyone would be using anything else.

(总是有人试图创建实体框架和架构"来消除对 SQL 的需求.据我所知，这从来没有真正奏效过，只是因为结果很难维护并且速度很慢.)

(There' always people trying to create Entity Frameworks and "architecture" away the need for SQL. As far as I know, this has never really worked, if only because the result is dramatically harder to maintain and dead slow.)

这篇关于LinqToSQL 是否足够强大?一个更强大但同样流畅的界面是不是很容易构建?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！