Paging with NHibernate using a custom Extension method to make it 'easier' :)...

Update 20081022:

I have updated the articles code to reflect the bug fixes kindly suggested by Paco :). I have now used this in my project and have had no issues with it yet.

The fixes included clearing order by's on the count query and also returning the count as an Int64.



Evening All,

What better thing to do on a friday night than code and blog :P. Thought it was about time I shared my custom paging helper methods for NHibernate. I quite liked this solution as it worked nicely and was easy to use. I will go into as much detail as I can here without putting you to sleep.

The Idea

Basically my idea was to replicate something like in Linq to SQL, where you can basically define a query and call Skip(x).Take(x), in the end I came up with the idea of calling an ToPagedResult(index, pageSize); extension method, this would then return a PagedResult<T> object, the PageResult object would basically just be a container which would hold the total results and the total item count. Simple really, reason for this is just to make paging results a little easier and reduce code waste by wrapping my common functionality in my extension method.

The Solution

The solution will need 2 things, first it will need my PagedResult<T> class, and then the ToPagedResult Extension method. Firstly the PagedResult class:

 /// <summary>
    /// A paged result set, will have the items in the page of data
    /// and a total item count for the total number of results.
    /// </summary>
    public class PagedResult<TEntity> {
        #region Properties

        /// <summary>
        /// The items for the current page.
        /// </summary>
        public IList<TEntity> Items { get; protected set; }
        /// <summary>
        /// Gets the total count of items.
        /// </summary>
        public long TotalItems { get; set; }


        #region Constructor

        /// <summary>
        /// Initialise an instance of the paged result,
        /// intiailise the internal collection.
        /// </summary>
        public PagedResult() {
            this.Items = new List<TEntity>();    

        /// <summary>
        /// Initialise our page result, set the items and the current page + total count
        /// </summary>
        /// <param name="items"></param>
        /// <param name="totalItems"></param>
        public PagedResult(IList<TEntity> items, long totalItems) {
            Items = items;
            TotalItems = totalItems;



This is a simple class really just holds the page of items and the total item count, only reason for this is just a neat way to return the results. In another version of this class I have added properties for the page index, total pages properties too but in this example I kept it simple and just added what is needed.

Next up is the extension method, basically I have in my project an NHibernateExtensions class which holds all my common extension methods, but for the example I am only including the ToPagedResult extension, the code for this is below:


public static class NHibernateExtensions {
 /// <summary>
        /// Based on the ICriteria will return a paged result set, will create two copies
        /// of the query 1 will be used to select the total count of items, the other
        /// used to select the page of data.
        /// The results will be wraped in a PagedResult object which will contain
        /// the items and total item count.
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="criteria"></param>
        /// <param name="startIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public static PagedResult<TEntity> ToPagedResult<TEntity>(this ICriteria criteria, int startIndex, int pageSize) {
            // Clone a copy of the criteria, setting a projection
            // to get the row count, this will get the total number of
            // items in the query using a select count(*)
            ICriteria countCriteria = CriteriaTransformer.Clone(criteria)

            // Clear the ordering of the results

            // Clone a copy fo the criteria to get the page of data,
            // setting max and first result, this will get the page of data.s
            ICriteria pageCriteria = CriteriaTransformer.Clone(criteria)

            // Create a new pagedresult object and populate it, use the paged query
            // to get the items, and the count query to get the total item count.
            var pagedResult = new PagedResult<TEntity>(pageCriteria.List<TEntity>(),

            // Return the result.
            return pagedResult;



The extension method works as follows, it is an extension on the ICriteria, could be made to work with DetachedCriteria too but in my case I only need it for ICriteria, based on the query it will make 2 copies using the CriteriaTransformer.Clone method, one query will be used to get the total item count, so we set a count projection on it.

The second copy is used to get the actual page of data, it uses NHibernates SetMaxResults and SetFirstResult methods to do this, then finally we create a new instance of our PagedResult container setting the items and item count using the two queries. Finally returning the paged result.

Usage Example

To use this extension method we first define an ICriteria query and then call the extension method to get the data, and example on out People table would be something like so, the search is just getting a list of people with age > 20. The page to get will be 0, i.e. the first page and there will be 10 items per page.

// Create the criteria to get people with age  > 20
ICriteria criteria = this.session.CreateCriteria(typeof (Person))
                .Add(Restrictions.Gt("Age", 20));

// Get the paged result using the above criteria and our new extension method.
PagedResult<Person> pagedResult = criteria.ToPagedResult<Person>(0, 10);


Hope you might find this useful in some of your projects, although it it something very simple it has saved me a great deal of time with paging in my project and means writing less code which is always a bonus. Send me any comments/suggestions you wish I am always open to criticism :).




  • Why are you bothering with NHibernate dude? sheesh. LINQ to Entities.

  • Very good, thanks for your pointless opinions what is wrong are you too much of a wanker to give your real name?

    Maybe next time let me know something useful and maybe backup your useless claims. Maybe let me know who you are I do have a rough idea though.

  • Thanks Stefan, I just started using NHibernate and this is very useful for me!

  • I tried this code yesterday, and I had 2 issues:
    - the unique result in the count criteria returns a long not an int, so you (long) is needed instead of (int)
    - an exception is thrown when orderby is used in the criteria. It can be solved by: countCriteria.Orders.Clear();

  • Paco,

    Thanks I noticed the count must of been an old version I use a long now :).

    With the ordering thanks for that it is a scenario I forgot to test, so this works fine with your fix then?

    Thanks for the feedback it is appreciated.


  • Thanks alot. I have been searching around for such custom paging using NHibernate...your tutorial did the trick. So Iam looking forward to use it in my Telerik RadGrid controls.

  • Hello Oburak,

    Not a problem glad you found it useful, this reminds me I need to update the post with the fixes suggested by Paco to fix issues when you have ordering, I will do so now.

    Thanks for the feedback.

  • Hi Stefan,

    I've a correction in the NHibernateExtension class [ guess is a typo mistake].
    Where it reads:


    Must be: .SetFirstResult(startIndex*pageSize);

    With that the code is just working perfect.

  • Oburak,

    In my case I treat start index as this number already when passed in, I guess in this case you would change the passed in parameter to pageIndex, startIndex to me is already this number, but thanks for the feedback :)


  • Great solution. I really like it and deffinately will use it in my project. The main thing that it is really extensive.

    Good luck.

  • Hi, I was using this extension for some time and it works fine. I have updated my NHibernate.dll to 2.1 recently and this pagination extension doesn't work anymore with no result fetched if the startIndex is not 1.

    Any advise??

  • Thank you for your help. I wanted to do the same with NHibernate 1.2. Here you are two tricks for doing that.

    // A trick for clearing orders in NHibernate 1.2 criterias:
    ICriteria countCriteria = detachedCriteria.GetExecutableCriteria(session);
    CriteriaImpl ci = (CriteriaImpl)countCriteria;

    // And another trick to copy the criteria
    public static DetachedCriteria copyCriteria(DetachedCriteria criteria)
    using (MemoryStream memStream = new MemoryStream())
    BinaryFormatter formatter = new BinaryFormatter();
    formatter.Serialize(memStream, criteria);
    memStream.Position = 0;
    return (DetachedCriteria)formatter.Deserialize(memStream);

Comments have been disabled for this content.