Similar to how we can combine several ICriteria and QueryOver queries into a single database round trip with MultiCriteria, we can combine several HQL and/or SQL 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, we'll show you how to fetch a product count and page of product results using a MultiQuery.
Complete the Getting Ready instructions at the beginning of Chapter 4, Queries.
- Add a new folder named
MultiQueriesto theQueryRecipesproject. - Add a new
structnamedPageOfto the folder:public struct PageOf<T> { public int PageCount; public int PageNumber; public IEnumerable<T> PageOfResults; } - Add a new class named
Queriesto the folder:using System; using System.Collections.Generic; using System.Linq; using NH4CookbookHelpers.Queries.Model; using NHibernate; namespace QueryRecipes.MultiQueries { public class Queries { private readonly ISession _session; public Queries(ISession session) { _session = session; } 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); } } } - Add a new class named
Recipeto the folder:using NH4CookbookHelpers.Queries; using NHibernate; namespace QueryRecipes.MultiQueries { public class Recipe : QueryRecipe { protected override void Run(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); } } } - Run the application and start the
MultiQueriesrecipe.
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 longer.
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 our queries to our MultiQuery object, specifying a label or name, and the type of list to return with the generic argument. 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 an 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 again use the label in our call to GetResults to return a specific result set. The first call to GetResults executes all the queries in a single batch. Every subsequent call only returns the results of an already executed query.