Gunnar Peipman's ASP.NET blog

ASP.NET, C#, SharePoint, SQL Server and general software development topics.

Sponsors

News

 
 
 
DZone MVB

Links

Social

Returning paged results from repositories using PagedResult<T>

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.

Class diagram for 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.

Class diagram for domain model

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.

Comments

Guy Harwood said:

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 8:32 AM

Ben said:

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 9:54 AM

DigiMortal said:

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 10:58 AM

DigiMortal said:

Thanks for link, TaoYang! PagedList<T>, 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 14, 2010 12:01 PM

bartg said:

Hi Gunnar,

From my point of view, PagedList<T> 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<T> : List<T>

   {

       public PagedList(IList<T> 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<T> 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 4:40 AM

DigiMortal said:

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. :)

# September 15, 2010 4:48 AM

DigiMortal said:

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. :)

# December 26, 2010 5:51 PM

DigiMortal said:

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.

# November 1, 2011 5:16 PM