Home > dotnet 4, Entity Framework > Entity Framework: Viewing SQL

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.

EF Publisher Data Model

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.

SQL Output to Console Window

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.

Advertisements
  1. Surya
    September 14, 2016 at 8:45 pm

    Any idea as to how to view the SQL in EF7 ???

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: