Returning paged results from repositories using PagedResult

During my large database experiment I wrote simple solution for paged queries that I can use in my repositories. This far my experiments have shown pretty good results and I think it is time to share some of my code with you. In this posting I will show you how to create paged results using NHibernate and Entity Framework based repositories.

Repositories and classes

Before going to repositories I let’s draw out my solution architecture a little bit. We will cover here only the orders part of my model. Here is class diagram that illustrates how order repositories are defined and how PagedResult<T> class fits into picture.

These two order repositories are different implementations of same IOrderRepository interface. One implementation is for NHibernate and the other for Entity Framework 4.0.

Here are shown some classes from domain model. Just take a look at these classes so you have better idea what I have.

As you see, there is nothing complex this far. Classes are simple and repositories are small yet.

PagedResult<T>

To return pages results from my repositories I defined class called PagedResult<T> in my core library. PagedResult<T> defines all important properties we need when dealing with paged results. Code is here.

public class PagedResult<T>
{
    public IList<T> Results { get; set; }
    public int CurrentPage { get; set; }
    public int PageCount { get; set; }
    public int PageSize { get; set; }
    public int RowCount { get; set; }
}

Results property contains objects in current page and the other properties give informations about result set. RowCount tells how much rows there was without paging. Although it is possible to give better names to properties we are happy with current names right now.

Now let’s see how we can use PagedResult<T>. There is method called ListOrdersForCustomer() that takes customer ID and returns orders with paging. There is one improvement I made in both repositories: because there are more methods that return PagedResult<T> I moved all paging logic to private methods. This way I have query methods short and also I have less duplicated code.

NHibernate: ListOrdersForCustomer()

Here is my NHibernate implementation for IOrderRepository. Okay, it is partial implementation here but you understand my point. Basically what I do in ListOrdersForCustomer() and GetPagedResultForQuery() methods is simple:

• create criteria for required result type,
• add conditions for query,
• create count query based on criteria,
• create multi query that contains our real query and count query,
• send queries to database,
• get results and initialize PagedResult<T>.

Code is here. Sorry if it looks a little bit messy.

public class OrderRepository : IOrderRepository
{
    private readonly ISession _session;
 
    public OrderRepository(NhSession session)
    {
        _session = (ISession)session.CurrentSession;
    }
  
    public PagedResult<Order> ListOrdersForCustomer(Guid customerId, 
        int page, int pageSize)
    {
        var criteria = _session.CreateCriteria<Order>();
        criteria.Add(
            Criterion.Restrictions.Eq("Customer.Id", customerId)
        );
 
        var result = GetPagedResultForQuery(criteria, page, pageSize);
        return result;
    }
 
    private PagedResult<Order> GetPagedResultForQuery(ICriteria criteria, 
        int page, int pageSize)
    {
        var countCriteria = CriteriaTransformer.TransformToRowCount(criteria);
        criteria.SetMaxResults(pageSize)
                .SetFirstResult((page - 1) * pageSize);
 
        var multi = _session.CreateMultiCriteria()
                    .Add(countCriteria)
                    .Add(criteria)
                    .List();
 
        var result = new PagedResult<Order>();
        result.CurrentPage = page;
        result.PageSize = pageSize;
        result.RowCount = (int)((IList)multi[0])[0];
        var pageCount = (double)result.RowCount / result.PageSize;
        result.PageCount = (int)Math.Ceiling(pageCount);
        result.Results = ((ArrayList)multi[1]).Cast<Order>().ToList();
        return result;            
    } 
}

Same way I can implement also other methods that return paged results. If you look at GetOrdersForCustomer() method you see it is pretty short. So, besides readability we also avoided code duplication.

Entity Framework: ListOrdersForCustomer()

ListOrdersForCustomer() for Entity Framework follows the same pattern. You may wonder why there is no base repository to handle all paged results for all repositories. Yes, it is possible, but as it is not problem right now I deal with this issue later.

For Entity Framework we need to specify sort order for results because otherwise it is not able to skip rows. Pretty weird problem but we have to live with it right now. Here is the code.

public class OrderRepository : IOrderRepository
{
    private readonly Context _context;
 
    public OrderRepository(Context context)
    {
        _context = context;
    }
  
    public PagedResult<Order> ListOrdersForCustomer(
        Guid customerId, int page, int pageSize)
    {
        var results = from o in _context.Orders
                      where o.Customer.Id == customerId
                      orderby o.Id
                      select o;
 
        var result = GetPagedResultForQuery(results, page, pageSize);
        return result;
    }
 
    private static PagedResult<Order> GetPagedResultForQuery(
        IQueryable<Order> query, int page, int pageSize)
    {
        var result = new PagedResult<Order>();
        result.CurrentPage = page;
        result.PageSize = pageSize;
        result.RowCount = query.Count();
        var pageCount = (double)result.RowCount / pageSize;
        result.PageCount = (int)Math.Ceiling(pageCount);
        var skip = (page - 1) * pageSize;
        result.Results = query.Skip(skip).Take(pageSize).ToList();
 
        return result;            
    }
}

Entity Framework code is a little bit simpler to read but we have this sorting thing we cannot forget. It is a little bit annoying but we can survive it.

Conclusion

We saw how to implement paged queries using PagedResult<T> class and we saw two implementations on IOrderRepository. One of them was for NHibernate and the other for Entity Framework. Using private helper method for paged results made our code easier to read and also we were able to avoid duplicate code.