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;

alt text 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.

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 ?

Bourbon

What you are trying to do is understandable, but writing your own Linq Provider is a very large effort. There is no short cut that I am aware of to get you there faster. – Mandelbaum

Your linked png link is dead. Nice pet project, but your screen shot is dead there, too. – Sears

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.

Convict

Create your own custom logger: msdn.microsoft.com/en-us/library/dn469464(v=vs.113).aspx – Floccule

Thanks for the answer. For Web API (EF) the code above (to log SQL statements) translates to: public ApplicationDbContext() : base("DefaultConnection") { Database.Log = Console.WriteLine; } it should be placed in DbContext constructor. – Thermomotor

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.

Adjunct

well I'm not re-inventing the wheel. DAL uses traditional ADO.NET Datasets so that's how we access data. we just want to produce SQL statements using LINQ to our custom objects. The reason why we want to do this is we have our own ORM and we develop are apps on this ORM. – Bourbon

Answer 3

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

Nickeliferous

Answer 4

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

Attraction

Answer 5

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:

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

Platinumblond