8

I got this error when I was using "LINQ to entities" to show every single product and implement paging in ASP.NET MVC.:

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

LINQ:

Model.Name = db.Products.Where(p => p.ProductSubcategoryID == id)
                        .Skip((page - 1) * pageSize)
                        .Take(pageSize)
                        .ToList();

How can I fix it? What would happen if I put OrderBy instead of Where?

Servy
  • 202,030
  • 26
  • 332
  • 449
Trung Pham
  • 233
  • 2
  • 6
  • 19
  • Did you try using `OrderBy` instead of `Where`? What happened when you did? –  Apr 02 '14 at 03:28
  • @Cupcake Yes, I tried it. The result of `OrderBy` is returned all of product not related with specific ID. – Trung Pham Apr 02 '14 at 03:39

3 Answers3

23

You don't "put OrderBy instead of Where"...you combine them:

Model.Name = db.Products.Where(p => p.ProductSubcategoryID == id)
                        .OrderBy(p => p.ProductSubcategoryID) // <---- this
                        .Skip((page - 1) * pageSize)
                        .Take(pageSize)
                        .ToList();

This is required because the generated SQL will produce something like:

WHERE generated_id BETWEEN x AND y

If you don't explicitly tell the DB server what order to return results in...your results would be different (possibly) every time. Whereas, if you order by a field, they are guaranteed to come out in order and therefore your paging will produce consistent results.

Simon Whitehead
  • 63,300
  • 9
  • 114
  • 138
1

You can add ".OrderBy(i => 0)" to work around the limitation, if you do not know which column needs to be sorted. It is very useful if you want to write some Generic Function that you don't know the Type of the class at front.

Xudong Jin
  • 99
  • 1
  • 2
0

There is no need in putting OrderBy instead of Where, in such case you will loose your where clause.

OrderBy method is used to sort the data in ascending order. The most important point that you need to keep in mind is this method is not going to change the data rather it is just changing the order of the data.

So, you can use the OrderByDescending() or OrderBy() method to avoid the error you're getting.

Model.Name = db.Products.Where(p => p.ProductSubcategoryID == id)
                    .OrderByDescending(p => p.ID) // or put some other column from your table for ordering data

or

Model.Name = db.Products.Where(p => p.ProductSubcategoryID == id)
                    .OrderBy(p => p.ID)

You can use the OrderBy method on any data type i.e. you can use character, string, decimal, integer, etc.

Viktor
  • 380
  • 5
  • 14