SQL tracing LINQ to Entities

Question

So, I would like to know oh to do a "full" tracing of Linq to Entities?

In other words:

I already know about the ToTraceString() method, but this only works on an ObjectQuery. I need it to work on on the entire Linq layer... so when I am doing IQueryable "Where" expressions and additional filtering that I can see the entire query, not just the initial ObjectQuery that was created. Am I using this wrong? I need some good examples of how to trace "everything" (at least tracing everything from one entity).

---

Edit 1: Remember this is for "Linq-to-Entities"

This is Linq-to-Entities NOT Linq-to-Sql (DataContext does not exist)

Edit 2:

I discovered the answer to my question by experimenting.

Answer 1

You can log everything that is done on your DataContext like so:

var dc = New MyDataContext();
var sb = New StringBuilder();
dc.Log = New StringWriter(sb);

// do some stuff with dc

dc.Log.Flush();
// now sb has everything that happened on the context.

Answer 2

I found the answer...

So the IQueryable object that I am using for my final query (after defining all of my expressions select and include everything that I need) can be casted to a ObjectQuery.

Once you do that the method ToTraceString() contains all of the SQL generated!

objectQuery.ToTraceString()

If you are building a query and do this earlier(on an earlier variable) it will return the SQL generated up until that point.

Also, the Parameters property contains all of the SQL parameters.

I made a method that I am calling before I return any results for a Linq routine. This method makes the output of the query look pretty for a console application:

private const string debugSeperator =
    "-------------------------------------------------------------------------------";

public static IQueryable<T> TraceQuery<T>(IQueryable<T> query)
{
    if (query != null)
    {
        ObjectQuery<T> objectQuery = query as ObjectQuery<T>;
        if (objectQuery != null && Boolean.Parse(ConfigurationManager.AppSettings["Debugging"]))
        {
            StringBuilder queryString = new StringBuilder();
            queryString.Append(Environment.NewLine)
                .AppendLine(debugSeperator)
                .AppendLine("QUERY GENERATED...")
                .AppendLine(debugSeperator)
                .AppendLine(objectQuery.ToTraceString())
                .AppendLine(debugSeperator)
                .AppendLine(debugSeperator)
                .AppendLine("PARAMETERS...")
                .AppendLine(debugSeperator);
            foreach (ObjectParameter parameter in objectQuery.Parameters)
            {
                queryString.Append(String.Format("{0}({1}) \t- {2}", parameter.Name, parameter.ParameterType, parameter.Value)).Append(Environment.NewLine);
            }
            queryString.AppendLine(debugSeperator).Append(Environment.NewLine);
            Console.WriteLine(queryString);
            Trace.WriteLine(queryString);
        }
    }
    return query;
}

Note: Debugging needs to be set to true in your config file.

    <configuration>
      ...
      <appSettings>
        <add key="Debugging" value="true" />
        ...
      </appSettings>
      ...
    <configuration>

Answer 3

Use SQL Profiler instead - then you will get not only the SQL but also important related information such as I/O impact, timings etc etc.

For a more detailed answer, see: http://stackoverflow.com/questions/76208/good-way-to-time-sql-queries-when-using-linq-to-sql#79665

Answer 4

Hi, You can use the "Linq to Entities debug visualizer" to view the native SQL during debug time. This tool is available at "http://visualstudiogallery.msdn.microsoft.com/en-us/99468ece-689b-481c-868c-19e00e0a4e69".

Important of all, this tool can give you the native sql irrespective of the database like MS SQL, DB2 for LUW, DB2, Oracle, MySQL, etc as far as you have created an edmx file.

Please post a rating/review if you like the tool.

Thanks Venkat

Answer 5

Hi Richard, Thanks for your comment. VS2010 does not add any visualizer dll into itself like VS2008. So you have to add under of devenv.exe.config. This allows the visualizers placed in the ..\Common7\Packages\Debugger\Visualizers folder to be loaded.

In Vista and Win7 after downloading the visualizer you have to accept to use internet downloaded files so that your OS trusts the tool in the properties window in Windows explorer.

The Q&A section in visualstudiogallery site explains steps to get the tool working.

Today i've released a WPF version of the same with additional feature to Add/Edit/View parameters in the L2E query.

The idea of VS extension is good but for this purpose a visualizer would be lighter.

Please note to rate the tool at http://visualstudiogallery.msdn.microsoft.com/en-us/99468ece-689b-481c-868c-19e00e0a4e69

Thanks and Regards

Venkat

Answer 6

Hi Richard, Visualizers are idea from Microsoft and thats why we have a Visualizers folder under the Visual Studio installation folder. However i'll try to make a Visual Studio extension. In between please visit http://www.rajavenkatesh.com where you'll have a link to "How to Use Visualizer". This helps you step by step to get LInQ to Entity debug visualizer.

Thanks

Venkat