So far, we've covered various queries using NHibernate's Criteria API and its new QueryOver syntax. NHibernate provides another, more powerful query method named Hibernate Query Language, a domain-specific language that blends familiar SQL-like syntax with object-oriented thinking. In this recipe, I'll show you how to use the Hibernate Query Language to perform those same queries.
HQLExample
.NameAndPrice.hbm.xml
with this xml code. Don't forget to set the Build action to Embedded Resource.<?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="HQLExample" namespace="HQLExample"> <import class="NameAndPrice"/> </hibernate-mapping>
App.config
, add <mapping assembly="HQLExample"/>
below the mapping element for Eg.Core
.Queries
class:public IEnumerable<Movie> GetMoviesDirectedBy(string directorName) { var hql = @"from Movie m where m.Director = :director"; return _session.CreateQuery(hql) .SetString("director", directorName) .List<Movie>(); } public IEnumerable<Movie> GetMoviesWith(string actorName) { var hql = @"select m from Movie m inner join m.Actors as ar where ar.Actor = :actorName"; return _session.CreateQuery(hql) .SetString("actorName", actorName) .List<Movie>(); } public Book GetBookByISBN(string isbn) { var hql = @"from Book b where b.ISBN = :isbn"; return _session.CreateQuery(hql) .SetString("isbn", isbn) .UniqueResult<Book>(); } public IEnumerable<Product> GetProductByPrice( decimal minPrice, decimal maxPrice) { var hql = @"from Product p where p.UnitPrice >= :minPrice and p.UnitPrice <= :maxPrice order by p.UnitPrice asc"; return _session.CreateQuery(hql) .SetDecimal("minPrice", minPrice) .SetDecimal("maxPrice", maxPrice) .List<Product>(); } public IEnumerable<NameAndPrice> GetMoviePriceList() { var hql = @"select new NameAndPrice( m.Name, m.UnitPrice) from Movie m"; return _session.CreateQuery(hql) .List<NameAndPrice>(); } public decimal GetAverageMoviePrice() { var hql = @"select Cast(avg(m.UnitPrice) as Currency) from Movie m"; return _session.CreateQuery(hql) .UniqueResult<decimal>(); } public IEnumerable<NameAndPrice> GetAvgDirectorPrice() { var hql = @"select new NameAndPrice( m.Director, Cast(avg(m.UnitPrice) as Currency) ) from Movie m group by m.Director"; return _session.CreateQuery(hql) .List<NameAndPrice>(); }
Program.cs
, use the following code in the RunQueries
method:static void RunQueries(ISession session) { var queries = new Queries(session); Show("Movies directed by Spielberg:", queries.GetMoviesDirectedBy( "Steven Spielberg")); Show("Movies with Morgan Freeman:", queries.GetMoviesWith( "Morgan Freeman")); Show("This book:", queries.GetBookByISBN( "978-1-849513-04-3")); Show("Cheap products:", queries.GetProductByPrice(0M, 15M)); Show("Movie Price List:", queries.GetMoviePriceList()); Show("Average Movie Price:", queries.GetAverageMoviePrice()); Show("Average Price by Director:", queries.GetAvgDirectorPrice()); }
Hibernate Query Language (HQL) syntax resembles SQL in many ways, but operates at an object level. We build all of our queries as strings. Much like DbCommands
in ADO.NET, we create IQuery
objects around those query strings, set the parameter values, and execute our queries with List
or UniqueResult
. Similar to the "at" sign (@
) in Microsoft SQL Server queries, in HQL, we prepend our parameter names with a colon (:) in the query string. When we set the parameter value, we don't include the colon.
We have this very basic HQL query:
from Movie m where m.Director = :director
For brevity, we've aliased our movies as simply m
. In this case, there is an implied select m
to project our movies. We have a single parameter, director
, which we use to filter our movies.
select m from Movie m inner join m.Actors as ar where ar.Actor = :actorName
In this query, we join from movies to their actor roles. Notice that unlike SQL, we don't need to specify ActorRoles
or set up a comparison with an ON
clause explicitly. NHibernate already understands the relationships between our entities. We filter those actor roles based on actor name. Just as with SQL, because we use an inner join, this filter on actor role effectively filters our movies as well.
from Product p where p.UnitPrice >= :minPrice and p.UnitPrice <= :maxPrice order by p.UnitPrice asc
In this query, we filter our Product
based on a price range defined by the two parameters, minPrice
and maxPrice
. This query could also be written using HQL's between
:
from Product p where p.UnitPrice between :minPrice and :maxPrice order by p.UnitPrice asc
As with SQL, the order by clause sorts our products by unit price.
We have this simple query:
select new NameAndPrice(m.Name, m.UnitPrice) from Movie m
When working with HQL, think in terms of objects and properties, not tables and columns. This query passes the Name
and UnitPrice
properties into this constructor of our NameAndPrice
class:
public NameAndPrice(string name, decimal unitPrice)
Then it projects the resulting NameAndPrice
instances. To make NHibernate aware of this class, we use the following import mapping:
<import class="NameAndPrice"/>
As an alternative, just as with criteria and QueryOver, we could simply project Name
and UnitPrice
, return a list of object arrays, and then use LINQ to Objects to transform those object arrays into NameAndPrice
instances, as shown in the following code:
var hql = @"select m.Name, m.UnitPrice from Movie m"; var query = session.CreateQuery(hql); return query.List<object[]>() .Select(props => new NameAndPrice( (string)props[0], (decimal)props[1]));
In this case, we wouldn't need to import our NameAndPrice
class.
select Cast(avg(m.UnitPrice) as Currency) from Movie m
In this query, we use the aggregate function average. This returns a scalar value of type double
, so we cast it back to NHibernate's Currency
type. The equivalent .NET type is decimal
, so we execute the query using UniqueResult<decimal>()
.
select new NameAndPrice( m.Director, Cast(avg(m.UnitPrice) as Currency) ) from Movie m group by m.Director
In this query, we group by Director
. We then pass Director
and our average UnitPrice
into the constructor of NameAndPrice
. Just as before, because avg
returns a double
, we'll need to Cast
it back to Currency
first.
In addition to the mapped properties and collections on our entities, HQL allows you to query on two implied and special properties:
class
is the full name of the type of our entity. For example, to query for books, we could write the following:from Product p where p.class='Eg.Core.Book'
id
always represents the POID of the entity, regardless of what we may name it in our entity. We can query for three books at a time with this query:from Book b where b.id in (@id0, @id1, @id2)