How to solve "The method 'Skip' is only supported for sorted input in LINQ to Entities."

Question

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?

Answer 1

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.