Clever tricks to find specific LINQ queries in SQL Profiler

Question

Profiling LINQ queries and their execution plans is especially important due to the crazy SQL that can sometimes be created.

I often find that I need to track a specific query and have a hard time finding in query analyzer. I often do this on a database which has a lot of running transactions (sometimes production server) - so just opening Profiler is no good.

I've also found tryin to use the DataContext to trace inadequate, since it doesnt give me SQL I can actually execute myself.

My best strategy so far is to add in a 'random' number to my query, and filter for it in the trace.

LINQ:

where o.CompletedOrderID != "59872547981"

Profiler filter:

'TextData' like '%59872547981'

This works fine with a couple caveats :

• I have to be careful to remember to remove the criteria, or pick something that wont affect the query plan too much. Yes I know leaving it in is asking for trouble.
• As far as I can tell though, even with this approach I need to start a new trace for every LINQ query I need to track. If I go to 'File > Properties' for an existing trace I cannot change the filter criteria.

You cant beat running a query in your app and seeing it pop up in the Profiler without any extra effort. Was just hoping someone else had come up with a better way than this, or at least suggest a less 'dangerous' token to search for than a query on a column.

Answer 1

You can have your datacontext log out the raw SQL, which you could then search for in the profiler to examine performance.

using System.Diagnostics.Debugger;

yourDataContext.Log = new DebuggerWriter();

All of your SQL queries will be displayed in the debugger output window now.

Answer 2

Messing with the where clause is maybe not the best thing to do since it can and will affect the execution plans for your queries.

Do something funky with projection into anonymous classes instead - use a unique static column name or something that will not affect the execution plan. (That way you can leave it intact in production code in case you later need to do any profiling of production code...)

from someobject in dc.SomeTable
where someobject.xyz = 123
select new { MyObject = someobject, QueryTraceID1234132412='boo' }

Answer 3

You can use the Linq to SQL Debug Visualiser - http://weblogs.asp.net/scottgu/archive/2007/07/31/linq-to-sql-debug-visualizer.aspx and see it in your watch window.

Or you can use DataContext.GetCommand(); to see the SQL before it executes.

You can also look at the DataContext.GetChangeSet() to view what's going to be inserted/ updated or deleted.