Does OrderBy followed by Skip and Take compile into query or run in memory?

Question

Say I have a query like this one:

var result=collection.OrderBy(orderingFunction).Skip(start).Take(length);

Will the whole query run on SQL server and return the result, or will it return the whole ordered table and then run Skip and Take in memory? I am concerned because I noticed that OrderBy returns IOrderedEnumerable.

How about something like this:

if(orderAscending)
   orderedCollection=collection.OrderBy(orderingFunction);
else
   orderedCollection=collection.OrderByDescending(orderingFunction);
var result=orderedCollection.Skip(start).Take(length);

Will the Skip and Take part run on Server or in memory in this case?

possible duplicate of [OrderBy is not translated into SQL when passing a selector function](http://stackoverflow.com/questions/12210799/orderby-is-not-translated-into-sql-when-passing-a-selector-function) — usr, Aug 01 '13 at 21:46

score 3 · Answer 1 · answered Aug 01 '13 at 20:22

3

This query is translated into SQL. An Entity Framework query such as

myTable.OrderBy(row => row.Id).Skip(10).Take(20);

Will produce SQL resembling the following:

SELECT TOP (20) [Extent1].[Id] AS [Id]
FROM ( SELECT [Extent1].[Id], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
        FROM [my_table] AS [Extent1]
)  AS [Extent1]
WHERE [Extent1].[row_number] > 10
ORDER BY [Extent1].[Id] ASC

I recommend downloading LinqPad, a utility that allows you to execute EF queries (and other queries), and see the results and the corresponding SQL. It is an invaluable tool in developing high-quality EF queries.

answered Aug 01 '13 at 20:22

Ben Reich

16,222
2
38
59

Sadly, actually no... your answer is correct in answering the question I put in title, but not correct if you looked in description of the problem. I think I'm having the problem described in http://stackoverflow.com/questions/12210799/orderby-is-not-translated-into-sql-when-passing-a-selector-function/12214145#12214145 – m0s Aug 01 '13 at 20:51
@m0s if your orderingFunction is not an expression then yes you have that problem and both answers did not help you :( This question is a duplicate then. Voting to close. – usr Aug 01 '13 at 21:46
@usr yeah... I voted to close my question too, not sure how this works. Now only if there was a way to convert Func into Expression... Will probably have to rewrite few hundred of these by hand. – m0s Aug 01 '13 at 21:58
@m0s Sorry for the confusion. What type does `orderingFunction` have? How is it instantiated or where is passed in from? – Ben Reich Aug 02 '13 at 01:26
@BenReich no problem, it is a Func that basically maps selected sorting column index from view to corresponding entity property, which in retrospect was probably a pretty bad idea. – m0s Aug 02 '13 at 05:05

score 1 · Answer 2 · answered Aug 01 '13 at 20:10

1

Yes, it does translate to SQL. This is essential for paging.

You can verify this using SQL Profiler.

answered Aug 01 '13 at 20:10

usr

168,620
35
240
369

Does OrderBy followed by Skip and Take compile into query or run in memory?

2 Answers2