ORM tools are a common tool in database programming for task automation: translating data back and forth between the format used by the database and the format used by the actual application you’re developing. Of course, not every task can be automated perfectly, and a lot of the time you gain from automation is offset by the time you lose cleaning up after your ORM’s mistakes. In this article I’d like to talk about two of the most popular ORM tools for C# – NHibernate and Entity Framework – and how they can be made much more effective and less painful by using another pair of tools, the dedicated NHibernate Profiler and Entity Framework Profiler.
ORM tools address a classic problem in database programming: developers usually work in object-oriented programming languages, which create and manipulate data in the form of objects. Most databases on the other hand are relational – they store data in the form of rows in a table. Translating between these types of data can be difficult because, unlike objects which can have complex branching structures, rows are always ‘flat’. Furthermore, every table has a set number of columns (the components of a row) that cannot be changed after the fact. This causes difficulties whenever a developer wants to update the definitions of their objects. Another difference is relational databases rely on a mechanism called joins. A join is a part of a query that combines two existing tables in various ways to form a new table that satisfies the query. The combination of these obstacles with a few others is called the object-relational impedance mismatch.
The operation of relational databases is generally done in SQL (Standard Query Language), which is very different from C# or the other languages we normally think of when we say “programming language”. NHibernate and Entity Framework were created to automate the conversion of C# objects into relational data by generating custom SQL and performing various other database management tasks. This allows developers to forget about the operation of the database and focus exclusively on what they know best: developing their application.
That’s what ORM tools offer, in theory at least. In practice, if you don’t babysit your ORM, the SQL it generates will often be very different from what you intended, resulting in inefficiency or worse. Sometimes your application might seem fine during testing, only to break in production.
A big part of the problem is that NHibernate and Entity Framework aren’t transparent. There’s no convenient way to see the SQL the ORM is generating. There’s no way to know what part of the code a given SQL statement came from, when it was generated, or why.
This brings us to the ORM profilers: NHibernate Profiler and Entity Framework Profiler, created by Hibernating Rhinos. These tools allow real-time debugging of your ORM by showing you the SQL it’s producing. You have many options for filtering the information to suit your needs. For example, you can view only the most expensive SQL queries, or see additional statistics like how many queries it took to generate a given page of results. The profilers give you many different ways to analyze your ORM’s behavior and also fit them all into one visually intuitive dashboard.
More importantly, the profilers help you understand your own code, your ORM, and how they interact. When debugging, the profilers can show you how problematic SQL queries were created by showing you stack traces. One display allows you to see a list of queries on one side, and on the other the list of methods that produced each query, as well as the class the method belongs to. The profilers also help you track the performance of your application.
The profilers are smart enough to help you avoid many common issues with ORM tools and with database programming in general. They even suggest how to improve code performance. They act as a query analyzer and immediately warn you about problems like queries that don’t set a limit on the number of results they can retrieve, or queries on columns that don’t have an index. You get alerts when there are an excessive amount of calls to the database, or when too many joins in one query are found. They can warn you about the infamous ‘select n+1 problem’, a situation where one query for the members of an object results in a call that retrieves the root object and an additional call for each member (rather than one call for the whole list of members.) These are common mistakes that might not cause problems during testing but can make your application slower the larger your database becomes.
The NHibernate and Entity Framework Profilers have recently been updated to version 6.0. Along with many other improvements, they now feature Azure Profiling Integration. This allows you to upload the profiled output of your application directly into an Azure container, helping you to keep your development cycle inside the Azure environment. The profilers monitor the container, allowing you to track your application’s behavior in real-time, even if you don’t have direct access to the code itself (a common problem with Azure). The profilers are also perfect for Continuous Integration and even generate output that can be accessed programmatically. The output comes in the form of XML, HTML, and JSON.
NHibernate Profiler and Entity Framework Profiler came out of an intimate familiarity with the many pitfalls of database programming and the development process, resulting in a UX that knows what you need before you need it. They convert your ORM from a black box that spits out unoptimized SQL into the precise and powerful tool that you need.
The best way to experience the benefits of these profilers is to try them out for yourself. You can sign up for a free 30-day trial of your profiler of choice by clicking the buttons below.