How to view LINQ Generated SQL statements?

Question

How is it done using the ObjectQuery method?

Answer 1

You can always attach something to the .Log property of your DataContext. That will show all the SQL commands as they are sent.

I do this in my base for data access objects and output it to the Visual Studio debug console. As the objects create their DataContext I check it see if its debug and attach a TextWritter helper class like this:

dbDataContext _dB = new dbDataContext();
_dB.CommandTimeout = 5000;

#if DEBUG
    _dB.Log = new DebugTextWriter();
#endif

Here is the helper object for output to the debug console:

//utility class for output of TextWriter for the Visual Sudio Debug window
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; }
    }
}

Answer 2

Here is what I found using ObjectQuery Method. Using console for testing, you can do the following:

Create an Extension Method as below, then call it. Say Product product, then SQL prints out as product.ToTraceString.

public static class MyExtensions
{
    public static string ToTraceString<T>(this IQueryable<T> t)
    {
        string sql = "";
        ObjectQuery<T> oqt = t as ObjectQuery<T>;
        if (oqt != null)
            sql = oqt.ToTraceString();
        return sql;
    }
}

Answer 3

 var q = from img in context.Images
                    ...
         select img;
 string sql = q.ToString();

sql will contain the sql select query.

EDIT: disadvantage: parameters won't have any values at this time

Answer 4

You could have a look at the Linq-to-SQL Debug Visualizer, or just hover your mouse over your Linq-to-SQL query (tooltip should show generated SQL), or access:

context.GetCommand(query).CommandText

Answer 5

You could run the SQL Server Profiler.

Answer 6

This is what I use when setting up the database context:

this.DbContext.Database.Log += s => Debug.WriteLine(s);

Answer 7

just a small update you can now use an Action to log the SQL:

// test SQL logger
Action<string> SQLLogger = (message) => System.Diagnostics.Debug.Write(message);
_dB.Context().Database.Log = SQLLogger;

Answer 8

If you are executing the linq query against a database, you can run the SQL Profiler to record the SQL query that is being executed. We do it quite often to identify any performance impact on conversion.