Executing native SQL using NHibernate named queries

I’ve been doing a lot of work with NHibernate lately, particularly with named queries. It took a while to get it just right, so I figured it would be helpful to others (and to myself in the future) to note some of the gotchas and how-to steps to get it just right.

What is a named query?

An NHibernate named query is essentially a native SQL statement that can be invoked by NHibernate and can return strongly-typed object data. It allows you to leverage native SQL code — parametrized statements or stored procedures — to perform complex data manipulation and retrieval.

In other words, let’s say you’re writing a baseball game, and you have three objects in your domain model: a Player, a Team, and a DrugTestResult model. You write a stored procedure, spSelectPlayersByLastDrugTestDate, which returns all players who haven’t had a drug test since a given date.

Now that you have a domain model and a SQL statement, how do you execute it through NHibernate?

Mapping your named queries

NHibernate needs to know about your named queries in order to execute them. Much like you tell NHibernate about your domain model using XML mapping files (*.hbm.xml), you tell NHibernate about your named queries using those same files.

Since the named query mappings do not relate directly to your domain model mappings, you can create a separate mappings file just for your named queries. A sample mappings file for our spSelectPlayersByLastDrugTestDate could look like the following.

< ?xml version="1.0" encoding="utf-8"?>
<hibernate -mapping xmlns="urn:nhibernate-mapping-2.2">
<sql -query name="FindPlayerByLastDrugTest">
<query -param name="LastDrugTestDate" />
<return class="Player">
<return -property column="PlayerID" name="PlayerID" />
<return -property column="PlayerName" name="Name" />
<return -property column="TeamID" name="Team" />
<return -property column="LastDrugTestID" name="LastDrugTestResult" />
</return>
exec spSelectPlayersByLastDrugTestDate @LastDrugTest=:LastDrugTestDate
</sql>
</hibernate>

The named query part of the mapping is in the <sql-query> section. I’ll describe its most important sections below.

• The sql-query tag defines the named query. The name attribute is the name that NHibernate (and, in turn, your code) will use to reference the query. In our example, we are stating that this named query is called FindPlayerByLastDrugTest.
• The query-param tag defines a parameter in your query. The name attribute reflects the name by which you are referring to that parameter, not the parameter name in the actual SQL statement. The type is the data type of that parameter. In our example, we have one parameter, LastDrugTestDate, which is a DateTime.
• The return tag defines the type which is returned by the named query. In our example, the return type is a class, Player. Our SQL statement must therefore return fields which map to the Player object. Note that named queries do not have to return domain model objects; they can return ordinals, other types, or nothing at all. (As a result, the return tag is optional.)
• The many return-property tags tells NHibernate how to map a column in the query results (the column attribute) to a property name in the return type. NHibernate gets rather crafty here. For normal properties (PlayerID and Name in our example) the mappings are simple column-name-to-property-name. Note, however, the TeamID mapping, where we map a TeamID in our result set (which could be an integer, the primary key for a Team domain object) to a property named Team. In our domain model, the Player.Team property is not an integer, it is a Team. NHibernate, because it knows how a Player relates to a Team, will auto-map the player to the team. This nuance is important in getting your named queries to work with your domain model: you map foreign key values in your result set to objects in your data model. (A similar thing happens for the LastDrugTestResult property.)
• Finally, after we close our return tag, we provide our actual SQL statement. This is a standard parametrized SQL statement with a twist. To inject your query-param values into the SQL statement, you specify :Name — that is, a colon followed by the name specified for the query-param.

Got all that? Good. We’re almost done!

Running your named queries

Finally, the easy part — running the named query! Named queries are run against the NHibernate ISession. Various methods are available to set parameters using a fluent interface.

Running our FindPlayerByLastDrugTest query could look like this:

// get your NHibernate session however you normally would
//ISession session = ...

// create an IQuery based on your named query, specifying parameters
IQuery namedQuery = session.GetNamedQuery("FindPlayerByLastDrugTest")
.SetDateTime(new DateTime(2003, 1, 1));

// execute the query
IList list = query.List();

// if you were running a query that returned a single, scalar value, you could say...
var result = query.UniqueResult();

Note that there are generic versions of List() and UniqueResult(), but I had problems getting them to work. Not sure why (I didn’t dig that far), but the above queries will do the same (though they require some type-casting).

Named queries do a lot more. The following links may prove useful in your learning.

• NHibernate’s “Stored Procedures” – Named Queries (Ayende)
• Using nhibernate’s named queries with ActiveRecord (Ken Egozi)
• NHibernate Bulk Updates/Inserts/Deletes with Named Queries (Nathan Scott)

If you find other great resources, be sure to share!