Entity Framework: Viewing SQL

In this post I am going to show you a cool trick I learned regarding ObjectQuery class and how it can be useful when working with Entity Framework.

In order to see what SQL statement Entity Framework is executing we generally tend to run the SQL profiler and set custom filters like the database name or id, the current user under which our code is going to execute the linq query etc so that we can isolate our query execution.

I find this approach very ugly as you have to start-stop your profiler trace in order to capture the exact time when the SQL is executed and in multi developer environment when everyone is developing against the same database it becomes quite challenging.

Well it’s not painful if we use a better approach using the ObjectQuery class and an extension method which extends the IQueryable interface. So here is the code for the ObjectQuery Extension class.

ObjectQueryExtension.cs

public static class ObjectQueryExtension
{
    public static string ToTraceString(this IQueryable t)
    {
        var objectQuery = t as ObjectQuery;
        return objectQuery != null ? objectQuery.ToTraceString() : 
                                     String.Empty;
    }
}

So now I’ll generate the EF Model from the publisher database and the entities I am interested in are Author and Title as shown below.

and the following code to display the SQL statement in the console window.

Program.cs

class Program
{
    static void Main(string[] args)
    {
        using (var context = new PublisherContext())
        {
            var authorId = "998-72-3567";
            var query = context.Author
                            .Where(x=>x.au_id == authorId)
                            .SelectMany(ta=> ta.titleauthors,(au,ti)
                            => new {
                                    au.au_id,
                                    au.au_fname,
                                    au.au_lname,
                                    ti.title
                                    });

            var sql = query.ToTraceString();
            Console.WriteLine("Generated SQL is :");
            Console.WriteLine(sql);
            Console.ReadKey();
        }
    }
}

and this is how the output looks like.

As you can see this small extension methods has lots of potential as you can use it along with logging frameworks like log4net and output the SQL to a file.

About these ads