How to: Return or Skip Elements in a Sequence (LINQ to SQL)

Use the Take<TSource> operator to return a given number of elements in a sequence and then skip over the remainder.

Use the Skip<TSource> operator to skip over a given number of elements in a sequence and then return the remainder.

Note

Take<TSource> and Skip<TSource> have certain limitations when they are used in queries against SQL Server 2000. For more information, see the "Skip and Take Exceptions in SQL Server 2000" entry in Troubleshooting (LINQ to SQL).

LINQ to SQL translates Skip<TSource> by using a subquery with the SQL NOT EXISTS clause. This translation has the following limitations:

• The argument must be a set. Multisets are not supported, even if ordered.

• The generated query can be much more complex than the query generated for the base query on which Skip<TSource> is applied. This complexity can cause decrease in performance or even a time-out.

Example

The following example uses Take to select the first five Employees hired. Note that the collection is first sorted by HireDate.

Dim firstHiredQuery = _
    From emp In db.Employees _
    Select emp _
    Order By emp.HireDate _
    Take 5

For Each empObj As Employee In firstHiredQuery
    Console.WriteLine("{0}, {1}", empObj.EmployeeID, _
        empObj.HireDate)
Next

IQueryable<Employee> firstHiredQuery =
    (from emp in db.Employees
    orderby emp.HireDate
    select emp)
    .Take(5);

foreach (Employee empObj in firstHiredQuery)
{
    Console.WriteLine("{0}, {1}", empObj.EmployeeID,
        empObj.HireDate);
}

The following example uses Skip<TSource> to select all except the 10 most expensive Products.

Dim lessExpensiveQuery = _
    From prod In db.Products _
    Select prod _
    Order By prod.UnitPrice Descending _
    Skip 10

For Each prodObj As Product In lessExpensiveQuery
    Console.WriteLine(prodObj.ProductName)
Next

IQueryable<Product> lessExpensiveQuery =
    (from prod in db.Products
    orderby prod.UnitPrice descending
    select prod)
    .Skip(10);

foreach (Product prodObj in lessExpensiveQuery)
{
    Console.WriteLine(prodObj.ProductName);
}

The following example combines the Skip<TSource> and Take<TSource> methods to skip the first 50 records and then return the next 10.

Dim custQuery2 = _
    From cust In db.Customers _
    Order By (cust.ContactName) _
    Select cust _
    Skip 50 _
    Take 10

For Each custRecord As Customer In custQuery2
    Console.WriteLine(custRecord.ContactName)
Next

var custQuery2 =
    (from cust in db.Customers
    orderby cust.ContactName
    select cust)
    .Skip(50).Take(10);

foreach (var custRecord in custQuery2)
{
    Console.WriteLine(custRecord.ContactName);
}

Take<TSource> and Skip<TSource> operations are well defined only against ordered sets. The semantics for unordered sets or multisets is undefined.

Because of the limitations on ordering in SQL, LINQ to SQL tries to move the ordering of the argument of the Take<TSource> or Skip<TSource> operator to the result of the operator.

Note

Translation is different for SQL Server 2000 and SQL Server 2005. If you plan to use Skip<TSource> with a query of any complexity, use SQL Server 2005.

Consider the following LINQ to SQL query for SQL Server 2000:

Dim custQuery3 = _
    From custs In db.Customers _
    Where custs.City = "London" _
    Select custs _
    Order By custs.CustomerID _
    Skip 1 _
    Take 1

For Each custObj In custQuery3
    Console.WriteLine(custObj.CustomerID)
Next

IQueryable<Customer> custQuery3 =
    (from custs in db.Customers
     where custs.City == "London"
     orderby custs.CustomerID
     select custs)
    .Skip(1).Take(1);

foreach (var custObj in custQuery3)
{
    Console.WriteLine(custObj.CustomerID);
}

LINQ to SQL moves the ordering to the end in the SQL code, as follows:

SELECT TOP 1 [t0].[CustomerID], [t0].[CompanyName],
FROM [Customers] AS [t0]
WHERE (NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM (
        SELECT TOP 1 [t1].[CustomerID]
        FROM [Customers] AS [t1]
        WHERE [t1].[City] = @p0
        ORDER BY [t1].[CustomerID]
        ) AS [t2]
    WHERE [t0].[CustomerID] = [t2].[CustomerID]
    ))) AND ([t0].[City] = @p1)
ORDER BY [t0].[CustomerID]

When Take<TSource> and Skip<TSource> are chained together, all the specified ordering must be consistent. Otherwise, the results are undefined.

For non-negative, constant integral arguments based on the SQL specification, both Take<TSource> and Skip<TSource> are well-defined.

See Also

Reference

Standard Query Operator Translation (LINQ to SQL)

Other Resources

Query Examples (LINQ to SQL)