Querying Entity Model. Part 18 – Limiting The Number Of Rows Returned

When working with a large number of rows in a table, sometimes you need to retrieve just a part of them. It could be the first N rows (for example, from 1 till 100) or any of the subsequent blocks with N rows (maybe 101…200, or 701…800). The last technique is also called pagination.

LINQ offers four methods to restrict the output:

  • Take() and Skip() – with a concrete number of rows;
  • TakeWhile() and SkipWhile() – with a lambda expression.

You can find the descriptions of these methods in MSDN article Enumerable Methods.

Let’s take this simple query.

It’s not a good idea to take first rows without any ordering because SQL Server does not guarantee the order of rows, especially when the table does not have a clustered index. But it works and Entity Framework generates the following T-SQL code:

Good! The code is correct, it uses a well-known SELECT TOP statement.

Now I want to get next 100 rows. But Entity Framework requires an explicit ordering now. OK, I place an explicit OrderBy() method.

You’ll be surprised that Entity Framework could generate different T-SQL code here. It depends on the version of SQL Server you’re working with. For SQL Server 2012+ it generates code with OFFSET/FETCH.

For previous versions (I’ve tested on SQL Server 2008R2) it uses ROW_NUMBER() function.

* I’ve indented the inner subquery to improve the readability.

Great! Entity Framework does the best it could do here 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *