C# 从 LINQ 表达式中提取 sql 查询

Extract sql query from LINQ expressions

提问by this. __curious_geek

Is it possible to extract sql statements from LINQ queries ?

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

Say, I have this LINQ expression.

说，我有这个 LINQ 表达式。

        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;

alt text http://ruchitsurati.net/files/linq-debugging.png

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

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

在这条语句之后，'results' 只保存了 LINQ 表达式，而不是由于 LINQ 查询的延迟执行而导致的结果。

Can I extract or produce the LINQ query out of 'results' and prepare a valid SQL statement from the query stored in 'LINQ'?

我可以从“结果”中提取或生成 LINQ 查询，并从存储在“LINQ”中的查询中准备有效的 SQL 语句吗？

EDIT

编辑

Here's My objective:

这是我的目标：

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.

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

Will I haev to write my custom Linq Providers to do what I need ?

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

采纳答案by jason

Edit: 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.

编辑：等等，你说的是 LINQ to Objects？不，那是不可能的¹。无法将 LINQ to Object 查询的表达式树转换为表示查询某个数据库的表达式树。

Edit: 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 walkthroughon MSDN doing that.

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

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.

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

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

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

There are two ways that I know of.

我知道有两种方法。

First:

第一的：

Set the DataContext.Logproperty to Console.Out(or another System.IO.TextWriterof your choice):

将DataContext.Log属性设置为Console.Out（或System.IO.TextWriter您选择的另一个）：

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

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

这将在执行 SQL 语句时将它们打印到控制台。有关此功能的更多信息，请参阅MSDN。在其他地方有s 的示例，TextWriter可让您将输出发送到调试器输出窗口。

Second:

第二：

Use the LINQ to SQL Debug Visualizerfrom 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.

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

¹: Perhaps not impossible, but certainly very hard.

¹：也许不是不可能，但肯定很难。

回答by Ahmad Mageed

EDIT #2:the update and clarification has completely changed the question.

编辑#2：更新和澄清已经完全改变了问题。

It sounds like you're reinventing the wheel and trying to accomplish what LINQ to SQL and LINQ to Entities already accomplish. For example, the providers examine the expression trees and map certain functions to SQL Server. You would be undertaking a large task that Microsoft has already provided us with and tested extensively.

听起来您正在重新发明轮子并试图完成 LINQ to SQL 和 LINQ to Entities 已经完成的工作。例如，提供程序检查表达式树并将某些函数映射到 SQL Server。您将承担一项 Microsoft 已经提供给我们并经过广泛测试的大型任务。

You would be much better off using existing ORM solutions, be it Microsoft's or NHibernate etc.

您最好使用现有的 ORM 解决方案，无论是 Microsoft 的还是 NHibernate 等。

---

---

EDIT #1:found it, I knew I saw something for this before but it eluded me.

编辑#1：找到它，我知道我之前看到过一些东西，但它让我望而却步。

You can use the DataContext.GetCommand methodto get the generated SQL:

您可以使用DataContext.GetCommand 方法来获取生成的 SQL：

var query = dc.Persons.Take(1);
string generatedSql = dc.GetCommand(query).CommandText;

This example returns the following SQL from the AdventureWorks database:

此示例从 AdventureWorks 数据库返回以下 SQL：

SELECT TOP (1) [t0].[BusinessEntityID], [t0].[PersonType], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[EmailPromotion], [t0].[AdditionalContactInfo], [t0].[Demographics], [t0].[rowguid] AS [Rowguid], [t0].[ModifiedDate] FROM [Person].[Person] AS [t0]

SELECT TOP (1) [t0].[BusinessEntityID], [t0].[PersonType], [t0].[NameStyle], [t0].[Title], [t0].[FirstName], [t0].[ MiddleName], [t0].[LastName], [t0].[Suffix], [t0].[EmailPromotion], [t0].[AdditionalContactInfo], [t0].[Demographics], [t0].[rowguid] AS [Rowguid], [t0].[ModifiedDate] FROM [Person].[Person] AS [t0]

---

---

Another option to determine the generated statements will be available in VS2010 via IntelliTrace (previously known as the Historical Debugger). For more information and screenshots see this blog post: Debugging LINQ to SQL queries using the Historical Debugger.

VS2010 中将通过 IntelliTrace（以前称为历史调试器）提供另一个确定生成语句的选项。有关更多信息和屏幕截图，请参阅此博客文章：使用历史调试器调试 LINQ to SQL 查询。

That's only good during debugging though, and doesn't provide a way to access it programmatically.

不过，这仅在调试期间有用，并且不提供以编程方式访问它的方法。

回答by user1477388

For future reference, I think this gentleman's method is the best http://damieng.com/blog/2008/07/30/linq-to-sql-log-to-debug-window-file-memory-or-multiple-writers

供以后参考，我认为这位先生的方法是最好的http://damieng.com/blog/2008/07/30/linq-to-sql-log-to-debug-window-file-memory-or-multiple-作家

class DebugTextWriter : System.IO.TextWriter {
   public override void Write(char[] buffer, int index, int count) {
       System.Diagnostics.Debug.Write(new String(buffer, index, count));
   }

   public override void Write(string value) {
       System.Diagnostics.Debug.Write(value);
   }

   public override Encoding Encoding {
       get { return System.Text.Encoding.Default; }
   }
}

myDataContext.Log = new DebugTextWriter();

回答by user1089766

you can start SQL Server Profiler on your DataBase to trace your linq query.

您可以在您的数据库上启动 SQL Server Profiler 来跟踪您的 linq 查询。

回答by Keenan Stewart

A easier method I found was to use the Command Window and you did not need to input any additional code. Here you can print a linq statement called query to the query window:

我发现一个更简单的方法是使用命令行窗口，您不需要输入任何额外的代码。在这里，您可以将名为 query 的 linq 语句打印到查询窗口：

? ((System.Data.Objects.ObjectQuery) query).ToTraceString()

? ((System.Data.Objects.ObjectQuery) 查询).ToTraceString()