LIMIT (Entity SQL)

  • Article

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 ]

Arguments

  • 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.

Note

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.

Example

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:

  1. Follow the procedure in How to: Execute a Query that Returns StructuralType Results (EntityClient).

  2. Pass the following query as an argument to the ExecuteStructuralTypeQuery method:

SELECT VALUE p FROM AdventureWorksEntities.Product 
                            AS p order by p.ListPrice LIMIT(5)

The output is shown below:

ProductID: 317
Name: LL Crankarm
ProductNumber: CA-5965
MakeFlag: False
ProductID: 316
Name: Blade
ProductNumber: BL-2036
MakeFlag: True
ProductID: 4
Name: Headset Ball Bearings
ProductNumber: BE-2908
MakeFlag: False
ProductID: 3
Name: BB Ball Bearing
ProductNumber: BE-2349
MakeFlag: True
ProductID: 2
Name: Bearing Ball
ProductNumber: BA-8327
MakeFlag: False

See Also

Tasks

How to: Page Through Query Results (Entity Framework)

Reference

ORDER BY (Entity SQL)
TOP (Entity SQL)

Concepts

Paging (Entity SQL)