What is the easiest way to find the LINQ statement for a SQL statement

Question

I am a SQL Server DBA for a company that sells an ASP.NET MVC3 application that uses LINQ and Entity Framework 4 for all database access. When I find an inefficient query in my SQL Server's plan cache that was generated by LINQ, I would like to be able to find that LINQ statement in the source code so that I can optimize it. What is the best way to find the LINQ that generated a given SQL statement?

For example, is there any way to put an entry in a config file or decorate the code somehow so that the class and method name or the LINQ statement itself are included as comments in the generated SQL?

Answer 1

The commercial tools ORM Profiler, Entity Framework Profiler or Hugati Query Profiler will both give you a stack trace for the methods which generated the SQL. That makes it fairly easy to find the LINQ in code, though it isn't displayed directly.

These tools also have the advantage that they make it easy to find inefficient queries amongst the many other SQL statements executed by the app.

Answer 2

Although it is not a free tool, this may provide the information you need:

http://efprof.com/

There is also a less expensive tool described here, which I have not used, but it looks very promising:

http://huagati.blogspot.com/2010/06/entity-framework-support-in-huagati.html

http://www.huagati.com/L2SProfiler/