Profiling and Logging Entity Framework Queries

So far, in our Entity Framework 4.0 Article series, we have covered some basics of the what, why and how of Entity Framework 4.0 and also performed some CRUD operations with Entity Framework. We have also seen how to create an independent Entity Data Model and bind the Model to controls like the ASP.NET GridView. In this article, we will see how to log the SQL queries that get generated by Entity Framework. We will also list profiling tools available.




With the Entity Framework, you are architecting, designing and developing at a conceptual level, without worrying too much about the specific details of communicating with the database. Entity Framework uses ADO.NET classes (like the SqlClient) behind the scenes to convert code into SQL queries, with the details abstracted from you.

Although the SQL generated by the System.Data.SqlClient has improved in .NET 4.0., it is always a good idea to do query profiling i.e. watch the queries and commands that get executed on the database and improve your code, if needed. You have a couple of options to watch the queries that get generated via Entity Framework. Some of them are:

- Using ObjectQuery.ToTraceString() method

- Using SQL Profiler

- Using Intellitrace (available in VS 2010 Ultimate)

- EFTracingProvider (on MSDN code gallery)

In this article, we will see how to watch some of the SQL queries generated using theObjectQuery.ToTraceString() method. We will create a simple logging mechanism that will log the query to a .txt file. You can then send the .txt file to your DBA or check the queries on your own for performance improvements.

Note: The ObjectQuery class implements common functionality for queries against a conceptual model using both LINQ to Entities and ObjectQuery<T>. Read my article Exploring how the Entity Data Model (EDM) Generates Code and Executes Queries – Entity Framework 4.0 to see how ObjectQuery functions.

Lets create our query logging mechanism. Here I am using the same code that I used in one of myprevious articles. Assuming you have downloaded the source code of the previous article, open the ‘ConsoleAppUsingMyModel’ project > right click the project in Solution Explorer > Add > Class. Rename the class to ‘QueryHandler.cs’ and click on ‘Add’. Write the following code in the class



Note: The query handler shown above is a very simple query logging mechanism and can be used by developers on their machines to log queries. In no way, should you use this logging mechanism on your production server. Explore Log4Net for advanced logging scenarios or use the different logging and profiling options listed above.

Now go back to Program.cs and add the following line in the class



As you can see, to retrieve the query generated, we are casting the result of the LINQ to Entities query to an instance of ObjectQuery class and calling the ToTraceString() method on it. We are then passing this string to the QueryHandler.WriteQuery() method to log it. Only one log file gets generated for each day the application is run.

Note: You can see only some queries using the ToTraceString() method. For eg: Queries which make use of Single() or make use of Lazy loading or insert, update, delete are not logged.

That’s it. Run the application and open the log file for that day and check the query generated



Although Entity Framework generates the query for you, it’s important to be aware of what’s happening in your database! In this article, we explored how to log simple queries. For advanced profiling scenarios, feel free to explore the other tools I mentioned at the beginning of this article.

The entire source code of this article can be downloaded over here