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.
- 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 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; } - 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:
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.