To display many forms and web pages, we need to run several queries. 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, speeding up our application. In this recipe, I'll show you how to use MultiCriteria to fetch a paged result set of products.
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 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>() .Select(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; }
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); }
The MultiCriteria API may be used with any NHibernate-supported RDBMS. However, 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's 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 notice 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.