NHibernate Querying Cheat Sheet

The same happens with all kinds of APIs given to you by every third-party package/tool/library, including NHibernate. I bet that from time to time you are having those thoughts: “How did I do that last time?” or “Where is that method/class?”. Let’s create a cheat sheet for one of the problematic and most frequently used features of NHibernate – querying possibilities. 

Ways To Build Queries 

NHibernate allows you to query data in five ways. Starting from the most recent one, these are LINQ, QueryOver, Criteria, HQL (Hibernate Query Language), and native SQL. And even when using the last one, we can still get some help from NH to make such queries better. First, we must remember that what is not mapped with NH cannot be used in a query. This, of course, does not apply to native SQL queries where we can do anything we want. 

LINQ probably gives you the best experience as it is very natural and quite straightforward to use for C#/.NET developers – the latter may become a drawback though (see the LINQ section below). Also, the majority of simple and advanced queries can be written using this method. On the other end of the list, we have got HQL that is a text language looking very much like SQL. It is very powerful though, as it uses all mappings defined for your model, yet still allows you to write queries in a way you probably already know (SQL). In the middle of the list, there is Criteria API, which was the first step towards objectivity where joins, conditions, projections, etc. are represented by classes/interfaces or methods. It certainly improves NHibernate handling, reduces the number of bugs related to queries, and introduces static type checks, hence providing better extensibility than HQL. QueryOver is the last option; it’s nothing more than a wrapper around Criteria API which, using lambda expressions, completely turns it into a statically type-safe option. 

Good Old QueryOver 

Let’s start with QueryOver then. The main interface here is IQueryOver<T, T> which we can either obtain from a session or create independently and then run using arbitrary session. It has methods to do standard things: 

Session.QueryOver<Rectangle>()   
.JoinQueryOver() 
.JoinAlias() 
.Where() 
.WhereNot() 
.WhereRestrictionOn() 
.OrderBy() 
.ThenBy() 
.Select() 
.SelectList(); 

These methods provide obvious ways to create JOINs, apply WHEREs and ORDER BYs, and SELECT data. Since we are using QueryOver, all of the methods accept lambda expressions to make our lives easier and type-safe. A less obvious method is the WithSubquery(), which is also provided by this interface, that allows us to create some sorts of restrictions using subselects, e.g. EXISTS/NOT EXISTS, ALL, ANY. But that is not all. Another nice class is Subqueries, which has got lots of useful static methods to create all kinds of WHERE conditions. Just to name a few examples: 

Eq()/Ne() //equal/not equal 
Exists()/NotExists() 
Ge() //greater than or equal 
Gt() //greater than 
In()/NotIn() 
IsNull()/IsNotNull() 
Le() //less than or equal 
Lt() //less than 

All these methods exist in two forms – one to use with constant values and the other to use with properties/columns. In both cases, the comparison is done with the DetachedCriteria object, which is nothing more but a separate query not attached to any session. Such an object can be obtained from the QueryOver<T, T> object mentioned earlier. 

Two other worth mentioning classes with useful methods are Projections and Restrictions. The first – as the name suggests – allows you to create all kinds of projections, starting with columns and constants, through grouping functions like AVG or MIN/MAX, ending with SQL built-in functions and subqueries as well as your custom SQL queries. The latter helps to create criteria – it provides methods to apply different comparison conditions between properties/columns, constants, and other projections. All that enables you to form most of your complex queries. 

…And Modern LINQ 

We are not going to go through Criteria API here as this is what QueryOvers do, just in an object way. Instead, let’s jump to LINQ now. Since NHibernate 3.0, we can use the beauty of LINQ in our queries. No more manual JOINs, no complex restrictions or projections, (almost…) everything can be written using LINQ. It is as simple as querying in-memory collections. But beware of the rashness, N+1 or cartesian product problems are hiding around the corner so some query profiling tool should still be your best friend to make sure you are not about to make a mistake. NH LINQ provider supports many standard methods like some on the String or the DateTime types, some from the Math namespace or some conversion methods. And if you still must use any other method or want to use your own object’s method (yes, it is also possible), I suggest you have a look at the BaseHqlGeneratorForMethod class and/or LinqExtensionMethod attribute. 

Sometimes it is also useful to retrieve some additional data apart from your basic query to limit the number of calls to the DB. Here several methods come in handy to fetch additional properties/collections: 

Fetch() 
FetchMany() 
ThenFetch() 
ThenFetchMany() 

Use them wisely as they may also get you in trouble by, for example, creating cartesian product when fetching several collections. This may be avoided by putting such fetches into separate future queries. 

What is also convenient is the ability to not only fetch data using LINQ but also to insert, update, or delete it. There are some methods to do so, have a look at the following: 

InsertInto() 
InsertBuilder() 
Update() 
UpdateBuilder() 
Delete() 

Summary 

To sum up, NHibernate is a powerful ORM which can make your life easier when it comes to writing simple or even many of the complex queries. Especially in the latest version 5.x, it comes with lots of useful features. You can use modern LINQ queries with the ability to manage data, and on the other hand, it is still possible to go back to the old Criteria API or HQL whenever you are made to do so.