Extract sql query from LINQ expressions.

Question

Is it possible to extract sql statements from LINQ queries ?

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;

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

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

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.

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

Answer 1

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.

Answer 2

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.