giovedì 17 novembre 2011

Trace T-SQL statements generated by Entity Framework

If we want to keep track of a query with Entity Framework without use a profiler, we can use the method ToTraceString which is part of the ObjectQuery object. In practice, this method displays the commands sent from our T-SQL query to the database.

First, when using the ToTraceString method we need an open connection to the database, otherwise we will raise an exception.

Its use is very simple. Here an example:
using (MyEntities ctx= new MyEntities()) {
  var strSql = "SELECT VALUE t FROM MyEntities.Employees AS t";
  var myQuery = ctx.CreateQuery(strSql);

The output will be something like:
[it].[FirstName] AS [FirstName],
[it].[LastName] AS [LastName],
[it].[Age] AS [Age]
FROM [dbo].[Employees] AS [it]

One important thing about ToTraceString method is that it doesn’t execute the query but it's only output the statement to be executed.