NHibernate Mutli Query Support
time to read 7 min | 1330 words
Anything with Multi in it sounds exciting, except the mutli penalties mode.

Developers are a suckers for performance. Give a developer the promise for a 0.5% performance gain, and they will jump through all number of hoops and go into any number of contractions just to be able to see the end of the rainbow.

One of the questions that I was asked fairly frequently is "how do I get a paged view of the data, including the total count of the rows?" When I try to explain that this is not possible to do this in SQL, they produce the following snippet:

SELECT * FROM Customers;

SELECT COUNT(*) FROM Customers;

I then have to explain that while this is a clever idea, this is not possible with NHibernate. And then I hit a case where I really needed NHibernate to send several queries to the database and get the results of all of them. This wasn't quite the case above (I may blog about this later) but it required the same abilities from NHibernate, so I set out to build the Mutli Query for NHibernate.

After a long journey, I finally commited the changes this morning. The new feature is this:

IList results = s.CreateMultiQuery()

       .Add("from Item i where i.Id > :id")

       .Add("select count(*) from Item i where i.Id > :id")

       .SetInt32("id", 50)

       .List();

You specify several queries, and execute them as one, what is actually is happening is that NHibernate will concatenate the queries, read all the result sets, and understand them correctly. It returns a list of lists, one per each executed query, so the above query results are:

IList items = (IList)results[0];

long count = (long)((IList)results[1])[0];

But you don't have to just pass the query string, you can also pass the query itself, which means that you can configure it in interesting ways, like this example of paging and counting, and if we are at it, let us add caching into the mix:

IList results = s.CreateMultiQuery()

       .Add(s.CreateQuery("from Item i where i.Id > :id")

              .SetFirstResult(10).SetMaxResults(20))

       .Add(s.CreateQuery("select count(*) from Item i where i.Id > :id") )

       .SetCacheable(true)

       .List()

Have fun...

Tweet Share Share 0 comments
Tags: