How to handle paging IQueryable with Linq To Entities? (problem with OrderBy)

Question

Hi There,

I am currently building a simple ASP.NET MVC site using Linq to Entities. My first foray into this world was nerd dinner, which is where I found the paginated list code that I am attempting to use. The code looks as follows:

public class PaginatedList<T> : List<T>
{

    public int PageIndex { get; private set; }
    public int PageSize { get; private set; }
    public int TotalCount { get; private set; }
    public int TotalPages { get; private set; }

    public PaginatedList(IQueryable<T> source, int pageIndex, int pageSize)
    {
        PageIndex = pageIndex;
        PageSize = pageSize;
        TotalCount = source.Count();
        TotalPages = (int)Math.Ceiling(TotalCount / (double)PageSize);

        this.AddRange(source.Skip(PageIndex * PageSize).Take(PageSize));
    }

    public bool HasPreviousPage
    {
        get
        {
            return (PageIndex > 0);
        }
    }

    public bool HasNextPage
    {
        get
        {
            return (PageIndex + 1 < TotalPages);
        }
    }
}

The problem is I am using the entity framework, and the above code throws the following error:

The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'.

Not knowing the ins and outs of Linq, I'm not sure how to add in an orderby clause when I don't know what the column names will be in the above method, what with it being generic.

Thanks!

Answer 1

I think your best bet is going to be ensuring that your queries have an order before passing them to the PaginatedList.

Answer 2

You could change your constructor's signature to require an IOrderedQueryable<T> argument, rather than IQueryable<T>. The calling code would then be responsible for handling the ordering.

public PaginatedList(IOrderedQueryable<T> source, int pageIndex, int pageSize)

Answer 3

First, I would strongly suggest using the free PagedList code rather than writing your own, as it takes into account many things which you would eventually have to discover on your own, and has close to no downside.

To answer your question, you should sort your list before passing it to the paging function. If you need to do this dynamically, you can use the Microsoft DynamicQuery library. But generally, the calling code will know how to sort the list.

Answer 4

You could always add another parameter to the method to allow the caller to define the OrderBy clause:

public PaginatedList(IQueryable<T> source, Func<T, TKey> orderBy, 
    int pageIndex, int pageSize)
{
    // your other code here
    this.AddRange(source.OrderBy(orderBy)
        .Skip(pageIndex * pageSize)
        .Take(pageSize));
}

Answer 5

Hi I've read answers but I can;t figure out how to use

public PaginatedList(IQueryable<T> source, Func<T, TKey> orderBy, 
    int pageIndex, int pageSize)

{   
 // your other code here    
this.AddRange(source.OrderBy(orderBy) 
       .Skip(pageIndex * pageSize)    
    .Take(pageSize));

}

I get an error at Func How can I use that ? Thank's

Sorry

Solved