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.

Order repositories

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.

Order classes

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.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    10 thoughts on “Returning paged results from repositories using PagedResult

    • September 14, 2010 at 12:32 pm
      Permalink

      Interesting.

      I struggled to implement this with any kind of pattern in a recent project. So its good to see it done in a clean manner.

      Thanks for sharing

    • September 14, 2010 at 1:54 pm
      Permalink

      To me sorting is a deal breaker for paging. I don’t think I’ve ever had a paged implementation that didn’t require flexible sorting…

    • September 14, 2010 at 2:58 pm
      Permalink

      Thanks for feedback, Ben. You can implement also sorting if you like. To sort by one field you can just extend parameter list of methods. To have flexible support for sorting by multiple fields you can create one array for fields and the other for sorting orders and give them as parameters with methods. It is also possible to create query objects that have more rich ways how to filter and sort objects. In my case sorting by one field is enough for some time. If I need more then I will work out solution for it and publish it here if I find something elegant and useful.

    • September 14, 2010 at 4:01 pm
      Permalink

      Thanks for link, TaoYang! PagedList, as I see, is usable only in LinqToX cases where querying data source happens with paging parameters. You cannot use it with data sources that support paging but not through LINQ.

    • September 15, 2010 at 8:40 am
      Permalink

      Hi Gunnar,

      From my point of view, PagedList doesn’t belong in repository layer.

      Usually I create calls to the repository returning a clean list of my model object. Once down in my UI I create a PagedList using these results.

      For me = paging is a UI concern.

      My 5 cent:

      I would use this implement to avoid having repeated count logic:

      public class PagedList : List
      {
      public PagedList(IList items,int pageIndex,int pageSize)
      {
      PageSize = pageSize;
      TotalItemCount = items.Count;
      TotalPageCount = (int)Math.Ceiling(TotalItemCount / (double)PageSize);
      CurrentPageIndex = pageIndex;
      StartRecordIndex=(CurrentPageIndex – 1) * PageSize + 1;
      EndRecordIndex = TotalItemCount > pageIndex * pageSize ? pageIndex * pageSize : TotalItemCount;
      for (int i = StartRecordIndex-1; i < EndRecordIndex;i++ ) { Add(items[i]); } } public PagedList(IEnumerable items, int pageIndex, int pageSize, int totalItemCount) { AddRange(items); TotalItemCount = totalItemCount; TotalPageCount = (int)Math.Ceiling(totalItemCount / (double)pageSize); CurrentPageIndex = pageIndex; PageSize = pageSize; StartRecordIndex = (pageIndex - 1) * pageSize + 1; EndRecordIndex = TotalItemCount > pageIndex * pageSize ? pageIndex * pageSize : totalItemCount;
      }

      public int CurrentPageIndex { get; set; }
      public int PageSize { get; set; }
      public int TotalItemCount { get; set; }
      public int TotalPageCount{get; private set;}
      public int StartRecordIndex{get; private set;}
      public int EndRecordIndex{get; private set;}
      }

    • September 15, 2010 at 8:48 am
      Permalink

      Thanks for feedback bartg! Paging as showing something as pages belongs to UI level. But sometimes you need paged data coming from data layer. Can you imagine what happens to performance when data layer returns you, let’s say, 100K orders and you take the correct page then?

      What I am targeting is using server resources carefully and when working with large tables I really don’t want large object collections to go to UI because 10 rows from 100K is only needed.

      It is not problem when you have some DB related IQueryable available so you just add your paging clauses to it. But if your mapper doesn’t support LINQ then you still have to return IList with data and this means that paging is already done or this IList is large and paging is done on presentation layer. I prefer first one. :)

    • December 26, 2010 at 10:51 pm
      Permalink

      Well, it is UI thing until you have few rows. If you per request drag 100K rows from DB to UI layer… well, I don’t want to be the fool who pays the hosting fees for this disaster. :)

    • November 1, 2011 at 9:16 pm
      Permalink

      Thanks for question, mohammed. I am using all those LINQ features very carefully and I always try to avoid the situation where queries get more and more and more conditions and clauses through different methods. This methods expects that you don’t make any other restrictions elsewhere and you keep your queries strictly under your complete control.

    • January 4, 2017 at 8:53 am
      Permalink

      Hi, I am new to ASP.NET CORE MVC, I am struggling adding pagination to my application. I have checked your two posts, It would be helpful if I see a full implementation of your code

    Leave a Reply

    Your email address will not be published. Required fields are marked *