Implement pagination with NHibernate ICriteria

I want to share my experience about implementing a pageable and sortable grids using NHibernate ICriteria. Many web applications use grids as a common representation of search results or list of data required by some business rules. We can extract data from database and paginate or order the records on the server side, but this creates a list of limitations in ordering the records and a big impact of the performance especially when dealing with big amount of data. One good solution is to paginate and order records on the database side and to retrieve just current page.

NHibernate (NH) ICriteria has two very powerful feature: SetFirstResult and SetMaxResults.

SetFirstResult determines 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 row and beyond. We combine this with SetMaxResults to return a single page of results. .SetFirstResult(10) .SetMaxResults(10) will return the 11th throgh 20th row.

Another interesting feature is using Future<> or FutureValue<>. When we call IQuery or ICriteria’s Future or FutureValue, NH returns an object representing the potential results of that query. It also queues up the query in hidden MultiCriteria or MultiQuery inside the session.

FutureValue return an IFutureValue<>, representing a single entity or scalar value. For Future, it returns an IEnumerable<>. NH waits until we access Value property of IFutureValue<> or enumerable the IEnumerable. When we do NH executes the hidden futures MultiCriteria and MultiQuery for this session.

example:

        /// <summary>

        /// Get Pagiable result

        /// </summary>

        /// <param name="gridMapper">Grid mapper</param>

        /// <param name="page">Page info</param>

        /// <returns></returns>

        protected IPagedList<TModel> GetPaged<TModel>(IGridMapper<TModel> gridMapper, IPageData page)

        {

            return GetPaged<TModel>(gridMapper.MappedCriteria, page);

        }

/// <summary>

        /// Get Pagiable Criteria result.

        /// </summary>

        /// <param name="criteria">NHibernate ICriteria</param>

        /// <param name="page">optional, nullable Page Info</param>

        /// <returns>pagiable list of results</returns>

        protected IPagedList<TModel> GetPaged<TModel>(ICriteria criteria, IPageData page)

        {

            IEnumerable<TModel> results =

                criteria.SetResultTransformer(Transformers.AliasToBean<TModel>()).Future<TModel>();

            var countCriteria = CriteriaTransformer.Clone(criteria); //need to investigate TransformToRowCount

            IFutureValue<int> resultCount =

                countCriteria.SetProjection(Projections.RowCount()).FutureValue<int>();

            if (page != null)

            {

                if (page.Page > 0)

                    criteria.SetFirstResult(page.Page * page.PageSize);

                if (page.PageSize > 0)

                    criteria.SetMaxResults(page.PageSize);

                if (!string.IsNullOrEmpty(page.SortBy))

                    criteria.AddOrder(GetOrder(page));

                page.TotalRows = resultCount.Value;

            }

            return new PagedList<TModel>(page, results.ToList());

        }

As you can see we first transform our ICriteria an Future object, also we are transforming results to our TModel using SetResultTransformer(Transformers.AliasToBean<TModel>()). Second we clone our criteria to calculate total rows count also using FutureValue. So in this case, both queries will be sent to database as one batch.

Next we are setting SetFirstResult and SetMaxResults depending on the page info which comes through IPageData (see attached file) .

GetOrder method returns a NHibernate order object depending on sort statement.

private static Order GetOrder(IPageData page)

        {

            if (string.IsNullOrWhiteSpace(page.SortBy))

                throw new ArgumentException("Cannot create order - orderby column is not set");

            return page.Desc ? Order.Desc(page.SortBy) : Order.Asc(page.SortBy);

        }

Read more: Amdaris

Posted via email from Jasper-net