nhibernate paging with detached criteria

Question

I am working on an application in which I would like to implement paging. I have the following class that implements detached criteria -

public class PagedData : DetachedCriteria
    {
        public PagedData(int pageIndex, int pageSize) : base(typeof(mytype))
        {

            AddOrder(Order.Asc("myId"));

            var subquery = DetachedCriteria.For(typeof(mytype2))
                .SetProjection(Projections.Property("mytype.myId"));

            Add(Subqueries.PropertyIn("myId", subquery));

            SetFirstResult((pageIndex - 1) * pageSize);
            SetMaxResults(pageSize);   
        }
    }

This works fine - it returns exactly the data that I am trying to retrieve. The problem I am running into is getting the total row count for my page navigation. since I am using the setfirstresults and setmaxresults in my detached criteria, the row count is always limited to the pageSize variable that is coming in.

My question is this: How can I get the total row count? Should I just create another detachedcriteria to calculate the row count? If so, will that add round trips to the db? Would I be better off not using detacedcriteria and using a straight criteria query in which I can then utilize futures? Or can I somehow use futures with what I am currently doing.

Please let me know if any further information is needed.

Thanks

Frederik Gheysels · Answer 1 · 2011-02-08T16:09:03.027

I do it like this, inside my class which is used for paged criteria access:

    // In order to be able to determine the NumberOfItems in a efficient manner,
    // we'll clone the Criteria that has been given, and use a Projection so that
    // NHibernate will issue a SELECT COUNT(*) against the ICriteria.
    ICriteria countQuery = 
        CriteriaTransformer.TransformToRowCount (_criteria);

    NumberOfItems = countQuery.UniqueResult<int> ();

Where NumberOfItems is a property (with a private setter) inside my 'PagedCriteriaResults' class.
The PagedCriteriaResults class takes an ICriteria instance in its constructor.

score 2 · Answer 2 · answered Feb 08 '11 at 14:48

2

you can create a second DetachedCriteria to get to row count with the build-in CriteriaTransformer

DetachedCriteria countSubquery = NHibernate.CriteriaTransformer.TransformToRowCount(subquery)

this will of course result in a second call to the db

answered Feb 08 '11 at 14:48

marc.d

3,804
5
31
46

Wow, never knew about this. The great part is that it also clears any FirstResult/MaxResults you may have set up. – Vadim Feb 09 '11 at 03:06

score 1 · Answer 3 · answered Jan 03 '14 at 09:57

Drawing on the two answers above i created this method for paged searching using detached criteria. Basically i just take an ordinary detached criteria and after i've created the real ICriteria from the session, i transform it to a rowcount critera and then use Future on both of them. Works great!

public PagedResult<T> SearchPaged<T>(PagedQuery query)
    {
        try
        {
            //the PagedQuery object is just a holder for a detached criteria and the paging variables
            ICriteria crit = query.Query.GetExecutableCriteria(_session);
            crit.SetMaxResults(query.PageSize);
            crit.SetFirstResult(query.PageSize * (query.Page - 1));

            var data = crit.Future<T>();

            ICriteria countQuery = CriteriaTransformer.TransformToRowCount(crit);

            var rowcount = countQuery.FutureValue<Int32>();

            IList<T> list = new List<T>();
            foreach (T t in data)
            {
                list.Add(t);
            }

            PagedResult<T> res = new PagedResult<T>();
            res.Page = query.Page;
            res.PageSize = query.PageSize;
            res.TotalRowCount = rowcount.Value;
            res.Result = list;
            return res;
        }
        catch (Exception ex)
        {
            _log.Error("error", ex);
            throw ex;
        }
    }

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

Discussed here:

How can you do paging with NHibernate?

edited May 23 '17 at 10:33

Community

1
1

answered Feb 08 '11 at 14:21

jishi

24,126
6
49
75

@czurski Particularly check out the second answer as it shows how to combine both queries in a MultiCriteria, which will reduce the overhead. – Vadim Feb 08 '11 at 15:44

nhibernate paging with detached criteria

4 Answers4