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.
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.
The following example uses Take to select the first five Employees hired. Note that the collection is first sorted by HireDate.
The following example uses Skip<TSource> to select all except the 10 most expensive Products.
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:
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]