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.
- Complete the setup steps in the introduction at the beginning of this chapter.
- Add the following structure inside the
Queriesclass:public struct PageOf<T> { public int PageCount; public int PageNumber; public IEnumerable<T> PageOfResults; } - Add the following methods to the
Queriesclass: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); } - In
Program.cs, use the following code in theRunQueriesmethod: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); } - Build and run your application. You should see the following output:
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.