ToTraceString Method in Entity Framework

ToTraceString Method in Entity Framework

In this post I’m going
to introduce the ToTraceString
method which enables us to
view the generated T-SQL
statement of a specific query
we execute in Entity Framework.

The ToTraceString Method

The ToTraceString method is a method that outputs the generated
T-SQL statement that is going to be submitted to the database by a
specific query in Entity Framework. The method is part of
EntityCommand object (which is part of the System.Data.EntityClient
namespace) and is a part of the ObjectQuery object (which is part of the
System.Data.Objects namespace). The method can help us when we
don’t what to use a profiler (like SQL profiler) in order to see the
T-SQL statement that is going to be executed. One important thing about
the use of ToTraceString is that it doesn’t execute the query,
it’s only output the statement to be executed. Another important thing
when using the ToTraceString method is that it needs to have an open
connection to the database in order to work. If there is no connection
to the database an exception will be thrown.

How to use ToTraceString Method?

It is very simple to use the ToTraceString  method. The next example
shows the use of the method with ObjectQuery object:

using (SchoolEntities context = new SchoolEntities())

{

    var sql = "SELECT VALUE department FROM SchoolEntities.Department AS department";

    var query = context.CreateQuery<Department>(sql);

    if (context.Connection.State != ConnectionState.Open)

    {

        context.Connection.Open();

    }

    Console.WriteLine(query.ToTraceString());

    Console.ReadLine();

}

The next example shows how to use the ToTraceString  method with the
EntityCommand object:

using (SchoolEntities context = new SchoolEntities())

{               

    EntityConnection conn = new EntityConnection(context.Connection.ConnectionString);

    var sql = "SELECT VALUE department FROM SchoolEntities.Department AS department";

    EntityCommand cmd = new EntityCommand(sql, conn);

    if (cmd.Connection.State != ConnectionState.Open)

    {

        cmd.Connection.Open();

    }

    Console.WriteLine(cmd.ToTraceString());

    Console.ReadLine();

}

Summary

Lets sum up, when you want to get an output of the queries statements you send
to the database using the Entity Framework, you can use the ToTraceString 
method. The ToTraceString  method is a way to see the generated T-SQL statement
of a specific query without the use of a profiler. I showed two examples of how to use
the method.