We need to run several queries to display forms and web pages. For example, it's common to display search results one page at a time. This typically requires two queries. The first counts all the available results and the second fetches the data for only 10 or 20 results. MultiCriteria allows us to combine these two queries into a single database round trip, potentially speeding up our application. In this recipe, we'll show you how to use MultiCriteria to fetch a paged-result set of products.
Complete the Getting Ready instructions at the beginning of Chapter 4, Queries.
- Add a new folder named
MultiCriteriato 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.MultiCriteria { 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 multiCrit = _session.CreateMultiCriteria() .Add<int>("count", countQuery) .Add<Product>("page", resultQuery); var productCount = ((IList<int>)multiCrit .GetResult("count")).Single(); var products = (IList<Product>)multiCrit .GetResult("page"); var pageCount = (int)Math.Ceiling( productCount / (double)pageSize); return new PageOf<Product>() { PageCount = pageCount, PageOfResults = products, PageNumber = pageNumber }; } private ICriteria GetCountQuery() { return _session.QueryOver<Product>() .SelectList(list => list .SelectCount(m => m.Id)) .UnderlyingCriteria; } private ICriteria GetPageQuery(int skip, int take) { return _session.QueryOver<Product>() .OrderBy(m => m.UnitPrice).Asc .Skip(skip) .Take(take) .UnderlyingCriteria; } } } - Add a new class named
Recipeto the folder:using NH4CookbookHelpers.Queries; using NHibernate; namespace QueryRecipes.MultiCriteria { 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
MultiCriteriarecipe.
The MultiCriteria API can be used with any NHibernate-supported RDBMS. However, currently only Microsoft SQL Server and Oracle can combine these queries into a single round trip to the database. For all other RDBMS, this functionality is simulated. In either case, your application doesn't need to be concerned. It just works.
In this recipe, we combine two criteria queries in a single round trip to the database. Our first query counts all the products in the database. Our second query returns a page with the first two of our three products, sorted by unit price. We use QueryOver instance skip and Take to accomplish this.
There are a couple of interesting things to point out with the MultiCriteria syntax:
var multiCrit = session.CreateMultiCriteria()
.Add<int>("count", countQuery)
.Add<Movie>("page", resultQuery);First, you'll see that we've labeled our queries with count and page. This is not required. Instead, we could use the index of each criteria object in the MultiCriteria to fetch the results. It's a little more difficult to mess up names than list indices, so we'll use names.
We use generic arguments to specify the element type for our results. That is, our first query returns a list of integers and the second returns a list of movies. The MultiCriteria doesn't provide a method for directly returning a single entity or scalar value. Instead, we use LINQ to object's single method to fetch the first and only value from the list.
When we get the product count, both queries are immediately executed, and the results are stored in memory. When we get the page of products, the MultiCriteria simply returns the results of the already-executed query.