NHibernate Mapping – Named queries <query/> and <sql-query/>

Queries are business logic, as such, they can be pretty complex, and they also tend to be pretty perf sensitive. As such, you usually want to have a good control over any complex queries. You can do that by extracting your queries to the mapping, so they don’t reside, hardcoded, in the code:

<query name="PeopleByName">
	from Person p
	where p.Name like :name
</query>

And you can execute it with:

using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
	session.GetNamedQuery("PeopleByName")
		.SetParameter("name", "ayende")
		.List();
	tx.Commit();
}

PeopleByName is a pretty standard query, and executing this code will result in:

Now, let us say that we discovered some performance problem in this query, and we want to optimize it. But the optimization is beyond what we can do with HQL, we have to drop to a database specific SQL for that. Well, that is not a problem, <sql-query/> is coming to the rescue.

All you need is to replace the query above with:

<sql-query name="PeopleByName">
	<return alias="person"
					class="Person"/>
	SELECT {person.*}
	FROM People {person} WITH(nolock)
	WHERE {person}.Name LIKE :name
</sql-query>

And you are set. You don’t need to make any changes to the code, but the resulting SQL would be:

Fun, isn’t it?