On the German MyCSharp forums I recently stumbled across a question as to how to trace the actual SQL statement generated by the LINQ to SQL extension methods. This pretty powerful feature which I've used quite a lot during my first weeks with LINQ is in deed a bit hidden inside Visual Studio 2008.
Suppose you've got the following code which executes a LINQ query against the famous Northwind sample database and binds the results to a WinForms DataGrid:
code
var query = from c in db.Customers
where searchTerms.Contains(c.ContactName)
select c;
The trick is to insert a breakpoint before the query gets executed for the first time. (Remember: LINQ queries get executed once they get used for the first time. In the above example the database does not get accessed at the var assignment!)
Once the breakpoint is hit during execution, Visual Studio will break out to the debugger. If you hover over the query statement, IntelliSense displays the SQL statement generated based on your LING query:
Visual Studio 2008 goes even further. If you click the little magnifying glass, it opens the LINQ to SQL debug visualizer which allows you to inspect the raw SQL generated by the OR/M:
You can even execute the query while you're debugging to see the results returned by the database:
The LINQ to SQL debug visualizer is not included in the RTM version of Visual Studio 2008. You can get it as a separate download. To install the LINQ to SQL debug visualizer, follow the below steps:
1) Shutdown all running versions of Visual Studio 2008.
2) Copy the SqlServerQueryVisualizer.dll assembly from the \bin\debug\ directory in the .zip download above into your local \Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers\ directory.
That's it! Now you'll see the little magnifying glass which allows you to drill into the SQL query.
One last note: If you execute ToString() on the LINQ to SQL query you also get the SQL statement. So supposed we've got a TextBox control named txtLog in our WinForms application, this code:
code
txtDebug.Text = query.ToString();
would print out the SQL statement as seen with Visual Studio type inspector!