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通过使用fluent接口，lambda表达式，扩展方法等，使得这更容易。最终结果是容易将本机C＃标量类型推入数据检索调用，并且容易自动生成本机C＃类实例。从MS网站中获取此示例：

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);

非常酷！

那么问题是什么？

So what is the problem?

好吧，正如我在上面链接的文章中指出的，有很多问题。我最大的 showstopper是，任何人谁最低限度能力的SQL将立即打破路障。这不仅是作为SQL的替代品创建的许多结构不熟悉，甚至笨拙（Group By？我们在LinqToSql中讨论 Ugly ）。更大的问题是有一些常见的构造，只是不是本机支持（例如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>();

只是开始收集SQL语句，其中启动我们的SQL Where子句并输入第一个参数，追加 tacks on more SQL， ParamVal 只是输入一个参数值，上一行和 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结构已经具备的知识。我不得不使用一些反射和一些其他酷新的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给我和更多的一切吗？它不包括甚至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，它仍然落在远低于哈米什的目标。这是一个非常现实和重大的缺点。

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.

推荐答案

所以，不是最好简单地嵌入
SQL在C＃（或VB）中的方式允许
你自由地陈述你的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.

真正的解决方案看起来更简单：嵌入SQL在C＃代码，并让编译器生成强类。如果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.

（总是有人试图创建Entity框架和架构据我所知，这从来没有真正工作，如果只是因为结果是很难维持和死缓慢。）

(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屋！