How to monitor and debug LINQ to SQL queries

In one of my previous posts where I tried to explain how to query an Sql Server database with LINQ to SQL, I said that LINQ to SQL queries get translated into commands that SQL Server understands. The LINQ runtime knows how to translate LINQ to SQL queries to T-SQL, the only language SQL Server knows!

We can see the communication between the Runtime and SQL Server using several tools.

Some tools are built into the Visual Studio 2008 sp1 for looking into the results of a LINQ query.

If you follow the exact same steps of my previous post until step 8 (the first example), then

you can put a breakpoint in this line

var tauthors = from ta in db.titleauthors
select ta;

run your application now and then press F11 to move one step forward from the above statement

Then place your cursor in the tauthors variable and select the Results View.

It has all the instances of author entity that has been retrieved from the database and populated in the titleauthors class.

You can go throuch each one of them and their respective private fields.

see the picture below and consider it as a visual aid

That is one approach viewing-debugging the results before they actually executes

There is another tool available.

Fire the browser of your choice and type the following url from Scott’s Gu website

http://www.scottgu.com/blogposts/linqquery/sqlserverqueryvisualiser.zip

Scott includes this tool as an add-on on his website.

Download this tool to your desktop and then unzip it.

Go then to the Debug/bin folder , copy the SqlServerQueryVisualizer.dll and place it in the following folder

C:\Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers

Save all your work. Close your project and exit Visual Studio

Fire up Visual Studio 2008 again and open your project again.

If you put a breakpoint in the same line,  run your application, press F11 and place your cursor above the

tauthors variable. You will see a magnifier icon. Click on it. You will see a screen that looks like the one below

When you click on the magnifier icon, you will see the SQL Server Query Visualiser which shows the query that is going to be executed.

You can hit the “Execute” button to preview the results before they executed.If you want to see when commands are executed you can use a different tool called LINQ to SQL Debug Writer.

LINQ to SQL Debug Writer is basically just a class that you add to your applications that outputs the generated T-SQL to the Debug window.

So you need to add another class to your project. Name it “class1.cs”

clear all the code in the class file and go to the following url

http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?List=4a6d97a9-b3b0-45a7-88a0-1a7f4819a678&ID=11&Source=http%3A%2F%2Fwww.u2u.info%2FBlogs%2FKris%2FLists%2FPosts%2FAllPosts.aspx

You will need to copy all the contents from this line

Here’s the code:

until the end…

and place it in the class1.cs file.

make sure you change this line inside the above file

namespace Vandermotten.Diagnostics

with whatever you named your project.

Then you need to add this line

db.Log = new DebuggerWriter();

immediately after this line

pubsDataContext db = new pubsDataContext();

Save and run your application by placing the breakpoint in the same place.

Clear all windows (like watch,locals) and just make sure you have the “Output” window open.

Step into your code. You can see in the output window, LINQ to SQL runtime, generates the T-SQL and sends the statement to the SQL SERVER.With this tool we can see both what is executed and what is executed.

We can also use the SQL Server Profiler to watch the traffic between LINQ to SQL and SQL Server.

Select Sql Profiler from the Performance Tools, start a new trace,connect to your SQL SERVER ,accept all the defaults and hit the Run button.

If you run the application again and have this break point and run the code step by step,you can see the entire T-SQL statement that has been executed in the SQL profiler window.

Hope it helps…..

About these ads