The method 'Skip' is only supported for sorted input in LINQ to Entities

Question

What could be causing this problem?

public ActionResult Index(int page = 0)
{
    const int pageSize = 3;
    var areas = repo.FindAllAreas();
    var paginatedArea = new PaginatedList<Area>(areas, page, pageSize);

    return View(paginatedArea);
}


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace UTEPSA.Controllers
{
    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);
//ERROR HERE->>this.AddRange(source.Skip(PageIndex * PageSize).Take(PageSize));
        }
        public bool HasPreviousPage
        {
            get
            {
                return (PageIndex > 0);
            }
        }
        public bool HasNextPage
        {
            get
            {
                return (PageIndex + 1 < TotalPages);
            }
        }
    }
}

Any suggestions?

Have you looked at the mvccontrib library? It contains a paging component: http://www.jeremyskinner.co.uk/2010/03/14/mvccontrib-grid-part-6-sorting/. Not sure how it approaches this problem though... — jeroenh, Aug 09 '10 at 11:46

score 33 · Accepted Answer · edited Feb 25 '19 at 08:59

33

Seems like the error is exactly what it is says. "Skip is only allowed on Sorted inputs". Searching for this error, I've found this.

It should be fixed if you include an OrderBy before Skip:

source.orderBy(???).Skip(PageIndex * PageSize).Take(PageSize));

Which might be a problem since you are passing a generic object T. You might need to expand your class to receive another parameter to indicate the order by element.

edited Feb 25 '19 at 08:59

Uwe Keim

39,551
56
175
291

answered Aug 09 '10 at 03:49

Wagner Silveira

1,576
11
9

From the article: This issue seems to have been fixed in the .NET 4.0 framework [for linq-to-sql, not linq-to-entities]... – jeroenh Aug 09 '10 at 11:48
Do not select the property for orderby base on that if it's null-able! since your pagination not perform correctly. – Elnaz Jul 13 '16 at 11:39

score 3 · Answer 2 · answered Apr 26 '11 at 10:57

that is worked (use first IOrderedQueryable):

http://msdn.microsoft.com/en-us/library/bb738702.aspx

 IOrderedQueryable<Product> products = context.Products
        .OrderBy(p => p.ListPrice);

IQueryable<Product> allButFirst3Products = products.Skip(3);

Console.WriteLine("All but first 3 products:");
foreach (Product product in allButFirst3Products)
{
    Console.WriteLine("Name: {0} \t ID: {1}",
        product.Name,
        product.ProductID);
}

score 2 · Answer 3 · answered Aug 09 '10 at 03:59

2

An IQueryable does not have an order, so saying "ignore the next x elements" doesn't make any sense.

If you include an order by clause (or possibly an AsEnumerable() call - untested) then your data takes an order and Skip and Take are now sensible.

answered Aug 09 '10 at 03:59

Zooba

11,221
3
37
40

9

You don't want to add AsEnumerable. This would defeat the main reason of paging which is to only fetch a limited amount of data from the database... Adding AsEnumerable() will pull in EVERYTHING, and apply the paging in-memory. – jeroenh Aug 09 '10 at 11:41
1

Have you tested this (or have a source), or are you assuming it? Calling ToList or ToArray will certainly pull in everything, but there is nothing in the contract of AsEnumerable that forces it to retrieve all data before returning the first (and subsequent) elements. – Zooba Aug 14 '10 at 00:55
4

Running .Skip() and .Take() on an IEnumerable will result in the query returning ALL results from the database when .ToList() is called. In that situation, the actual pagination is performed in memory. I tested this using SQL Server Profiler and the generated sql query did not have any pagination limitations. – quakkels May 30 '13 at 14:23
I'm surprised this answer isnt more upvoted because this is the actual reasoning. – Maxim Gershkovich Nov 01 '18 at 06:03

score 2 · Answer 4 · answered Mar 02 '16 at 02:27

I wanted to validate this by running the SQL equivalent of a similar LINQ skip/take query.

SELECT * FROM [table]
--order by [column] //omitted!
OFFSET 10 ROWS
FETCH NEXT 15 rows only

Note that when the order-by clause is omitted, the SQL error is much less informative:

"Invalid usage of the option NEXT in the FETCH statement."

So the "sorted input" is actually required on the database-level. Kudos to LINQ for helping developers write advanced SQL statements!

The method 'Skip' is only supported for sorted input in LINQ to Entities

4 Answers4

Linked