It's easy to use SQL Server Profiler to trace stored procedures activity. But how to trace SQL queries issued by LINQ via Entity Framework? I need to identify such queries (LINQ code) that consume a lot of time, are called most frequently and therefore are the first candidates for optimization.
I've found useful DbContext.Database.Log property.
MSDN article Logging and Intercepting Database Operations
The DbContext.Database.Log property can be set to a delegate for any method that takes a string. Most commonly it is used with any TextWriter by setting it to the “Write” method of that TextWriter. All SQL generated by the current context will be logged to that writer. For example, the following code will log SQL to the console:
using (var context = new BlogContext())
{
context.Database.Log = Console.Write;
// Your code here...
}
What gets logged?
When the Log property is set all of the following will be logged:
The approximate amount of time it took to execute the command. Note that this is the time from sending the command to getting the result object back. It does not include time to read the results.
SQL for all different kinds of commands. For example: Queries, including normal LINQ queries, eSQL queries, and raw queries from methods such as SqlQuery
Inserts, updates, and deletes generated as part of SaveChanges
Relationship loading queries such as those generated by lazy loading
Parameters
Whether or not the command is being executed asynchronously
A timestamp indicating when the command started executing
Whether or not the command completed successfully, failed by throwing an exception, or, for async, was canceled
Some indication of the result value