LINQ to Entities Skip and Take

Question

I'm creating page with pagination, and got method that takes argument page and numberOfElementsPerPage.

In this method im using Linq to Entities to load elements for page:

public List<Item> GetElements(int page, int numberOfElementsPerPage)
{
    return DataContext.Items.OrderBy(x => x.Id).Skip((page-1)*numberOfElementsPerPage).Take(numberOfElementsPerPage);
}

What I want to ask, how does this Skip/Take works? Does it take first all records from database, order and then Skip/Take? If yes I think this is pretty bad solution if the database got 100000 records for example or even more. So whats the best solution?

Answer 1

So whats the best solution?

This is the best solution.

If yes I think this is pretty bad solution

You are right, had it been implemented that way, it would be a pretty bad solution. Fortunately, it is not implemented that way: the values from Skip and Take are passed to your RDBMS server in a way specific to the SQL dialect, at which point the database decides how to find and serve you the records. In case of SQL Server, syntax similar to this one is used:

SELECT ...
FROM ...
WHERE ...
...
OFFSET <skip-value> ROWS
FETCH NEXT <take-value> ROWS ONLY;