Why Can't I Call Skip on an IOrderedQueryable?

Question

I am returning an IOrderedQueryable<T> like so:

response.Data = Expressions
    .ApplySorts(request, result)
    .Skip(request.Skip)
    .Take(request.Take)
    .ToList();

Here is my class which contains a method to return an IOrderedQueryable<T>.

public static class Expressions
{
    public static IOrderedQueryable<T> ApplySorts<T>(
        DataRequest request, IQueryable<T> items)
        {
            var sorts = request.Sort;

            if (sorts != null)
            {
                foreach (var sort in sorts)
                {
                    items = items.OrderBy(sort.Field + " " + sort.Dir);
                }
            }

            return (IOrderedQueryable<T>)items;
        }
}

But at runtime I get this error:

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

Note that there are no sorts currently taking place; however, the return value of ApplySorts<T>() should still be returning the type that Skip() expects, no?

Answer 1

You need a default sort. While in most databases, you can do TOP, LIMIT or BETWEEN without sorting, it doesn't really make sense (the first two actually apply to Take, but the theory is the same).

The order in which rows are returned is undefined (even if they usually appear in the order they were inserted). Therefore, your ORM is preventing you from creating a bug in your code (that is, the Skip is not consistent, and doesn't truly make sense).

To explain further: Skip(10) does not make sense without an Order, and it is functionally equivalent to not existing. Whether or not we skip 10 rows is irrelevant if we don't have ordering. If you shuffle a deck of cards, discard the top 10, and then take 5, is it any different from simply taking 5 without discarding? In both cases, you get 5 random cards - the discard is irrelevant.

You should have a default sort case if there are no sorts provided. A suitable candidate is the table's primary key.