从 LINQ 表达式中提取 sql 查询 [英] Extract sql query from LINQ expressions

问题描述

是否可以从 LINQ 查询中提取 sql 语句?

比如说，我有这个 LINQ 表达式.

Say, I have this LINQ expression.

        string[] names =
            new string[] { "Jon Skeet", "Marc Gravell", "tvanfosson", 
                           "cletus", "Greg Hewgill", "JaredPar" };

        var results = from name in names
                      where name.StartsWith("J")
                      select name;

替代文字 http://ruchitsurati.net/files/linq-debugging.png

在此语句之后，results"仅保存 LINQ 表达式，而不保存由于 LINQ 查询的延迟执行而导致的结果.

After this statement 'results' is only holding the LINQ expression and not the results due to deferred execution of LINQ queries.

我可以从结果"中提取或生成 LINQ 查询吗?准备一个有效的 SQL 语句查询存储在LINQ"中?

编辑

这是我的目标:

我们已经编写了自己的 ORM.每次需要进行数据库操作时，我们都必须编写查询.现在我们需要在 DAL 摆脱它.我们希望在代码中编写 LINQ 表达式，该表达式将针对我的 ORM 生成 SQL 语句，我们将在数据库上执行此 SQL.

We have written our own ORM. We have to write queries every-time we need to do db operations. Now we need to get rid of it at DAL. We wish to write LINQ expression in code which will produce SQL statements against my ORM and we will execute this SQL on the database.

我是否需要编写自定义 Linq 提供程序来满足我的需要?

推荐答案

等等，你在谈论 LINQ to Objects?不，这是不可能的¹.无法将 LINQ to Object 查询的表达式树转换为表示查询某个数据库的表达式树.

Wait, you're talking about LINQ to Objects? No, that is impossible¹. There is no way to convert the expression tree for a LINQ to Object query to an expression tree that represents querying some database.

不要写你自己的 ORM.这个问题有经过验证的解决方案.你试图再次解决这个问题是在浪费价值.如果您要使用自己的 ORM，将表达式转换为 SQL 语句，是的，您必须编写自己的提供程序.这是 MSDN 上的演练.

Don't write you're own ORM. There are proven solutions to this problem. You're wasting value by trying to solve this problem again. If you're going to use your own ORM, to translate an expression to a SQL statement, yes you will have to write your own provider. Here's a walkthrough on MSDN doing that.

但是说真的，不要编写自己的 ORM.五年前，在 NHibernate 和 LINQ to SQL 出现并成熟之前，很好.但是不是现在.没办法.

But seriously, do not write your own ORM. Five years ago before NHibernate and LINQ to SQL came along and matured, fine. But not now. No way.

这个答案的其余部分假设您在询问 LINQ to SQL.

The rest of this answer assumed that you were asking about LINQ to SQL.

我知道有两种方法.

首先:

设置DataContext.Log 属性到 Console.Out(或您选择的另一个 System.IO.TextWriter):

Set the DataContext.Log property to Console.Out (or another System.IO.TextWriter of your choice):

var db = new MyDataContext();
db.Log = Console.Out;

这将在执行时将 SQL 语句打印到控制台.有关此功能的更多信息，请参阅 MSDN.在其他地方有 examples 的 TextWriters 允许您将输出发送到调试器输出窗口.

This will print the SQL statements to the console as they are executed. For more on this feature see MSDN. Elsewhere there are examples of TextWriters that let you send the output to the debugger output window.

第二:

使用 LINQ来自 Scott Guthrie 的 SQL 调试可视化工具.这允许您通过 Visual Studio 中的调试器查看 SQL 语句.此选项的优点是无需执行查询即可查看 SQL 语句.您甚至可以执行查询并在可视化工具中查看结果.

Use the LINQ to SQL Debug Visualizer from Scott Guthrie. This allows you to see the SQL statements through the debugger in Visual Studio. This option has the advantage that you can see the SQL statement without executing the query. You can even execute the query and see the results in the visualizer.

¹:也许不是不可能，但肯定很难.

¹: Perhaps not impossible, but certainly very hard.

这篇关于从 LINQ 表达式中提取 sql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！