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

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 2

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.

Answer 3

EFCore has a feature TagWith() exactly for this purpose.

 var nearestFriends =
      (from f in context.Friends.TagWith("This is my spatial query!")
      orderby f.Location.Distance(myLocation) descending
      select f).Take(5).ToList();

https://docs.microsoft.com/en-us/ef/core/querying/tags

Unfortunately you can't use Query Store to find them :-)

This is because comments before the query are stripped out.

Such a shame! Hope I don't have to wait another 12 years.