Extract sql query from LINQ expressions

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.

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 walkthrough on MSDN doing that.

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.

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

There are two ways that I know of.

First:

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

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 examples of TextWriters that let you send the output to the debugger output window.

Second:

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.

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

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

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

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.

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

---

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

You can use the DataContext.GetCommand method to get the generated SQL:

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

This example returns the following SQL from the AdventureWorks database:

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.

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

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