NHibernate from the ground up – Part 5: Queries

Download NHibernateQueries.zip (4442.6K)

NHibernate offers multiple options for queries, each with its trade offs.

Two options, HQL and Criteria have been around quite a while and Ayende does a phenomenal job at explaining some of the basics here http://ayende.com/Blog/archive/2009/05/19/nhibernate-queries-examples.aspx. I don’t want to re-hash all of Ayende’s work, but we will go over a bit of HQL and Criteria to get a base comparison for the queries.

In this post, we will go over some of the newer options for writing queries with NHibernate using Linq and Lambdas as well as how we can batch queries together to reduce calls to the database server.

The code download is a slight deviation from the other posts in this series because I wanted to highlight the different queries which I thought could be better displayed in a console app.

HQL

HQL is the “Hibernate Query Language”. HQL is typically written as a string and looks very similar to SQL. Because of that, it is quite easy to get the two mixed up, but the important difference to remember is that with HQL you are writing the query against the objects, where in SQL you are writing the query against the tables. Here is quick sample of HQL that gets all posts by a commenter’s name.

"select p from PostEntity p join p.Comments as c where c.Name = :name"

You can see we have very similar constructs that we have in SQL, select, join and where to name a few and we can alias elements of the query.

Notice that we are selecting Posts and joining by the Post’s Comments collection. Unlike SQL, we are not joining by ID, but instead by the objects’ relationships.

Specifying parameters is done by prefixing a colon “:” to a parameter name. In this case we defined a parameter “:name” and will assign a value to it in a second.

Now lets take a look at how we use this HQL query.

hql

We need to get a session from the SessionFactory and then use the CreateQuery method passing in the HQL string. Setting the parameter of the query is done simply with the SetParameter method passing in the name of the parameter (without the “:” prefix) and the value of the parameter.

To execute the query, simply call List and in this case were are telling it the type of object to return. There is a non-generic version of this method that will return an array of objects that you must then cast to the appropriate type.

Executing this query results in the following SQL being executed.

hql_results

Criteria

You can imagine that if you need to formulate a dynamic query, with HQL you would have to get into some nasty string parsing.

This is where the Criteria API comes in. It allows you to create a criteria object and then dynamically added relations and predicates onto the query without the pain of string parsing.

The above query using criteria could be represented as the following.

criteria

Here I am using a DetachedCriteria which does not require a session to create. A typical criteria object requires an open session just like the HQL query.

The first part of creating a criteria is done by using the factory method For and passing the type, which will become the “root” of the query.

The next line creates the “join” to the Comments collection and we assign it an alias. Here I am using the CreateAlias method, but there is another CreateCriteria method that does the same thing and then lets you append predicates onto it.

The next line adds the predicate to the query. In this case the Restrictions.Eq is stating where c.Name is equal to “Commenter 2”. There are numerous predicates available and there are all in the Restrictions namespace.

Once we have our criteria created, we open up session and then pass that session to the criteria in the GetExecutableCrtiera method finally calling the List method.

The results of this query result in slightly different SQL being generated.

criteria_results

You can see that we are forced to load the comments as well even though we did not ask for them. I honestly don’t know why this is, and it is one drawback of the Criteria API, but if you specifically do not want to load them, you will need to use a sub query. Ayende shows how to do this in the link referenced above.

Linq

You can find the Linq provider next to the NHibernate assemblies at source forge.

http://sourceforge.net/projects/nhibernate/files/

You should just have to reference the assembly, drop in the required using statements and you should be good to go.

The linq provider is relatively new and as such there is not much information on it around so your best bet to find out how to do something is to peruse the tests for it, just grab the source from the above link to find the tests.

The same query done in linq looks like the following.

linq

Its pretty basic Linq if you are familiar with Linq. The difference being that we need to use the Linq extension method on the Session to get the root of the query.

Executing this query results in the following:

linq_results

This is much like the criteria results, but notice it is using a left join. I don’t know why this is or how to change it but it could be problematic I think. For example, you will get a post back even if there have been no comments. But having said that the Linq provider has been in production systems a couple years now and I don’t think majors issues have come up with it.

Lambda Extensions

The lambda extensions provide strongly types to the Criteria API for those of you that don’t like strings. :D

Using it is similar to the Criteria just with the funky lambda syntax.

lamda

Executing the query we get the following SQL.

lamda_results

Projections

In all of the above queries, we were fetching an entity. There are times however that you only want pieces of the entity or pieces of different entities. The way to get this is to use projections.

To get started, we need an object that we will “project” the results onto. Here I created a simple class that contains just the Post Id and Title.

postqueryresult

Its a pretty simple class, but notice the constructors. The default constructor is required when using the Criteria API (there is a way around this though) and the parameterized constructor is for HQL.

Lets take a look at the HQL query first.

hql_projection

The difference from the previous HQL query is in the selection. Instead of just “select p from …” we are asking for a new PostQueryResult object and passing in the Id and Title of the post. In order for NHibernate to know about this new type, we have to import it in a mapping file.

The mapping file is much simpler that mapping entities.

postqueryresult_mapping

If you are using the Criteria API, you don’t need the mapping file because you explicitly tell NHibernate the type.

criteria_projection

Its the same criteria as before, but we set the projection to a projection list. To which we add the individual properties we are interested in.

Setting the projection, essentially sets what we are selecting from the database.

The next line, setting the result transformer sets the type for the results and shows its Java roots with the AliasToBean transformation.

One caveat I have found here.

When using the AliasToBean transformer, if you are using an alias in the query, you will need to alias the projections, as done with the Id and Title in the ProjectionsList. This way the transformer will find the properties to set on your result type. This is also why you need the default constructor on your type.

Batching Queries and Paging

Batching queries allows us to make multiple queries with only one trip to the database. A great use of this functionality is with paging, as normally not only do you need your paged result data, but you also need the total number of records associated with the query.

First lets look at the HQL version.

hql_batch

There are a few things to notice.

First, we need to use multiple query objects which means we need multiple HQL statements.

Second, even though we have two different query objects, the parameters in those queries cannot share names, which is why you will see the parameters are name1 and name2.

Third, on our first query (the main query if you will) we set the first and max results. This will restrict the record set we get back.

Fourth, the count(*) in the row count query will return an long and will need to be specified with FutureValue<long>.

Finally, we use Future<> and FutureValue<> to get the results. Future<> will return a delayed enumerator and FutureValue returns an IFutureValue<T>. It is very important to realize that this query has not executed yet. It has added these two queries to an in memory queue that will be executed at a later time? When is that later time? Its whenever an enumerator is accessed, in this case the foreach of the results OR when the .Value property is called on the FutureValue.

While using the Future functionality is very powerful, have to write two HQL queries is a royal pain. Fortunately the Criteria API makes this much easier.

criteria_batch

With the Criteria API, we still need two criteria objects, but NHibernate will give one to us once we define our main criteria. 

We do this by using the CriteriaTransformer to create a new criteria based on our configured criteria that is specifically setup just get us the row count of the query.

Everything else if very similar in regards to setting the first and max values and setting the Future values, with one difference. Unlike the HQL, the row count transformation will return an int and not a long.

What to use and when

So with all these query options what type should you use and when should you use it?

It’s really up to and I can’t tell you. Each option has its strengths and weaknesses.

However, IMHO, the HQL provides the most powerful descriptive queries but makes dynamic queries very difficult by requiring string parsing. The Criteria API, while somewhat un-natural to me, allows good abstraction an reuse capabilities. So if you have a know query that is not dynamic go HQL, if you have an unknown query or want to get a bit more reuse out of them, go with the Criteria API.

Conclusion

So that wraps up this post. Hopefully you gleaned something about the options available to you when querying NHibernate and how to use them.

What's Next

The next post will cover a feature that is pretty unique to NHibernate, Caching. I don’t know of another ORM that gives you caching capabilities right out of the box.

Hope this helps and if you have any questions or thoughts, please leave them in the comments below.

Thanks,

Joe

TrackBack

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a0133ecbab7ab970b0134858d86fe970c

Listed below are links to weblogs that reference NHibernate from the ground up – Part 5: Queries:

Comments

You can follow this conversation by subscribing to the comment feed for this post.

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment