jump to navigation

How to monitor and debug LINQ to SQL queries November 19, 2008

Posted by fofo in c# 3.0, LINQ, Visual Studio 2008.
Tags:
trackback

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

resultsview

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

scottvisualiser1

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…..

Add to FacebookAdd to NewsvineAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to Furl

Comments»

1. Shana - December 23, 2012

Way cool! Some extremely valid points! I appreciate you penning this article and the rest of the
website is really good.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: