Physical paging can be performed by using LIMIT sub-clause in ORDER BY clause. LIMIT can not be used separately from ORDER BY clause.
[ LIMIT n ]
The number of items that will be selected.
If a LIMIT expression sub-clause is present in an ORDER BY clause, the query will be sorted according to the sort specification and the resulting number of rows will be restricted by the LIMIT expression. For instance, LIMIT 5 will restrict the result set to 5 instances or rows. LIMIT is functionally equivalent to TOP with the exception that LIMIT requires ORDER BY clause to be present. SKIP and LIMIT can be used independently along with ORDER BY clause.
An Entity Sql query will be considered invalid if TOP modifier and SKIP sub-clause is present in the same query expression. The query should be rewritten by changing TOP expression to LIMIT expression.
The following Entity SQL query uses the ORDER BY operator with LIMIT to specify the sort order used on objects returned in a SELECT statement. The query is based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:
Follow the procedure in How to: Execute a Query that Returns StructuralType Results.
Pass the following query as an argument to the
SELECT VALUE p FROM AdventureWorksEntities.Products AS p order by p.ListPrice LIMIT(@limit)