locked
FAQ Item: How do I track the LINQ to SQL generated SQL? RRS feed

  • Question

  • How do I track the LINQ to SQL generated SQL? 
    Sunday, June 20, 2010 1:00 PM

Answers

  • There are three ways that frequently used to trace the SQL commands which are generated by LINQ to SQL provider:  using DataContext.Log, using DataContext.GetCommand, and using LINQ to SQL Debug Visualizer. 

     

    1)      Using DataContext.Log

    This sentence of codes can make the generated SQL display in the console window.   The SQL can be query, insert, update and delete commands. 

    =================================================================

    db.Log = Console.Out;

    =================================================================

     

    Additional references:

    http://msdn.microsoft.com/en-us/library/bb386961.aspx

     

    2)      Using DataContext.GetCommand

    This method provides information about SQL commands generated by LINQ to SQL, but it is only a getter and does not affect DataContext state.   Note the following considerations (quote from MSDN document):

    ·         The argument must be non-null.  Otherwise, a null argument exception is thrown.

    ·         Normal query translation exceptions thrown during LINQ to SQL query execution apply for a query that cannot be translated.

    ·         Only the first query command is returned.  Specially, additional commands that are used for eager loading are not included.

    ·         DataContext does not track what the user does with the command.   E.g. results from the execution of the returned command are not tracked and do not affect DataContext state. 

    The following example displays the SQL command generated by LINQ to SQL query in the console window.

    =================================================================

    MyDataContext db = new MyDataContext();

    var query = from d in db.Departments select d;

    DbCommand dc = db.GetCommand(q);

    Console.WriteLine(dc.CommandText);

    =================================================================

     

    Additional references:

    http://msdn.microsoft.com/en-us/library/bb882652.aspx

     

    3)      Using LINQ to SQL Debug Visualizer

    LINQ to SQL Debug Visualizer can help us debug the LINQ to SQL IQueryable<> query by seeing the generated SQL in the Visual Studio debug Visualizer.   We can also execute the generated SQL in the Visualizer to check the return results.  

    To install the LINQ to SQL Debug Visualizer, please follow these steps:

    a)      Download the LINQ to SQL Visualizer .zip package here.

    b)      Shutdown all running versions of Visual Studio 2008. 

    c)       Copy the SqlServerQueryVisualizer.dll assembly from \bin\debug\ directory in the .zip package into the local directory \Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers\.  

    d)      Start up Visual Studio 2008 again.  Now we can use the LINQ to SQL Debug Visualizer.  

     

    Additional references:

    http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx http://weblogs.asp.net/bilalshouman/archive/2009/02/17/linq-to-sql-debug-visualizer.aspx

    • Marked as answer by MSDN FAQ Sunday, June 20, 2010 1:01 PM
    Sunday, June 20, 2010 1:01 PM