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 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

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

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigious Microsoft MVP award for Sixteen consecutive years. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that offers Digital Marketing and Branding services to businesses, both in a start-up and enterprise environment.

Get in touch with him on Twitter @suprotimagarwal or at LinkedIn