Just like we can combine several ICriteria and QueryOver queries into a single database round trip with MultiCriteria, we can combine several HQL queries with MultiQuery. Particularly in a production setting where the database and application are on separate machines, each round trip to the database is very expensive. Combining work in this way can greatly improve application performance. In this recipe, I'll show you how to fetch a product count and page of product results using a MultiQuery.
Queries
class:public struct PageOf<T> { public int PageCount; public int PageNumber; public IEnumerable<T> PageOfResults; }
Queries
class:public PageOf<Product> GetPageOfProducts( int pageNumber, int pageSize) { var skip = (pageNumber - 1) * pageSize; var countQuery = GetCountQuery(); var resultQuery = GetPageQuery(skip, pageSize); var multiQuery = _session.CreateMultiQuery() .Add<long>("count", countQuery) .Add<Product>("page", resultQuery); var productCount = ((IList<long>)multiQuery .GetResult("count")).Single(); var products = (IList<Product>)multiQuery .GetResult("page"); var pageCount = (int) Math.Ceiling( productCount/(double) pageSize); return new PageOf<Product>() { PageCount = pageCount, PageOfResults = products, PageNumber = pageNumber }; } private IQuery GetCountQuery() { var hql = @"select count(p.Id) from Product p"; return _session.CreateQuery(hql); } private IQuery GetPageQuery(int skip, int take) { var hql = @"from Product p order by p.UnitPrice asc"; return _session.CreateQuery(hql) .SetFirstResult(skip) .SetMaxResults(take); }
Program.cs
, use the following code in the RunQueries
method:static void RunQueries(ISession session) { var queries = new Queries(session); var result = queries.GetPageOfProducts(1, 2); var heading = string.Format("Page {0} of {1}", result.PageNumber, result.PageCount); Show(heading, result.PageOfResults); }
In this recipe, we build two HQL queries. The first returns a count of all our products. It's important to note that HQL's count returns an Int64
or long
.
The second query returns a single page of products. We use SetFirstResult
to determine where our results begin. For example, passing zero to SetFirstResult
will return all the results. Passing 10 will skip the first 10 results, returning the 11th product and beyond. We combine this with SetMaxResults
to return a single page of results. .SetFirstResult(10).SetMaxResults(10)
will return the 11th through 20th product.
We add each of our queries to our MultiQuery
object, specifying a label or name, and, with the generic argument, the type of list to return. Just as with MultiCriteria
, there's no way to return a single entity or scalar value directly. In this example, our count query will return a list of Int64s
containing one item, and our page query will return a list of Products
. We'll use LINQ to Objects's Single()
method to extract the actual count value.
We use the label again in our call to GetResults
to return a specific result set. The first call to GetResults
executes all the queries in a single batch. Each subsequent call only returns the results of an already executed query.