Profiling and Logging Entity Framework Queries

Posted by: Suprotim Agarwal , on 2/5/2011, in Category Entity Framework
Views: 95841
Abstract: In this article, we will see how to log the SQL queries that get generated by Entity Framework. We will also some see profiling tools available.

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 the ObjectQuery.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 my previous 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

Query Handler

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

ObjectQuery To Trace String

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

Log Entry

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

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Suprotim Agarwal, ASP.NET Architecture MVP, MCSD, MCAD, MCDBA, MCSE, is the CEO of A2Z Knowledge Visuals Pvt. He primarily works as an Architect Consultant and provides consultancy on how to design and develop .NET centric database solutions.

Suprotim is the founder and primary contributor to DotNetCurry, DNC .NET Magazine, SQLServerCurry and DevCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls. and is authoring another one at The Absolutely Awesome jQuery CookBook.

Follow him on twitter @suprotimagarwal


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by SQLDeveloper on Friday, January 20, 2012 2:21 PM
Queries generated by entity framework are editable? In other words can we edit the queries to make them more readable?
Comment posted by James on Saturday, January 21, 2012 1:56 AM
Or even edit queries to make them perform better!
Comment posted by shrichand gupta on Thursday, June 27, 2013 5:19 AM
very usefull for beginners, thanks you 4 investing time.

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel