Why does LINQ-to-SQL Paging fail inside a function?

Question

Here I have an arbitrary IEnumerable<T>. And I'd like to page it using a generic helper function instead of writing Skip/Take pairs every time. Here is my function:

IEnumerable<T> GetPagedResults<T>(IEnumerable<T> query, int pageIndex, int pageSize)
{
   return query.Skip((pageIndex - 1) * pageSize).Take(pageSize);
}

And my code is:

result = GetPagedResults(query, 1, 10).ToList();

This produces a SELECT statement without TOP 10 keyword. But this code below produces the SELECT with it:

result = query.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();

What am I doing wrong in the function?

Answer 1

I believe you need to change it to use IQueryable instead, in order to maintain the deferred execution. When you use IEnumerable, it will apply the extra functions (Skip, Take, etc) in memory after the base query is executed. However, using IQueryable will cause the additional methods to be included in the query being sent to the database.

Answer 2

Try using IQueryable...

IQueryable<T> GetPagedResults<T>(IQueryable<T> query, 
                                 int pageIndex, 
                                 int pageSize)
{
    return query.Skip((pageIndex - 1) * pageSize).Take(pageSize);
}