Programming Stuff

Entity Framework: Pagination Behind the Scenes

Data pagination is always a headache. We should customize every time we want to gain performance in a large data result.

In this post, I will show you the correct way to do pagination by snicking the code generated by Entity Framework.

In the Database, we have a table named Address:

And in the Server, we have an Entity that represents it.

Now, using LINQ to Entities (ADO.Net Entity Framework), I will mimic a pagination by using the combination of OrderBy (or  OrderByDescending),  Skip  and Take  and see what SQL script will generated.

Using the SQL Server Performance Tool: SQL Server Profiler, I captured the SQL statement generated by the ORM Framework:

And voilà, the query is created. This is composed by 2 SELECTs : In the inner SELECT have a new column named row_number and the outer SELECT just use the new column to filter data at a specified position.

The SQL statement is equivalent to the LINQ statement above, for instance:

  • ORDER BY [Extent1].[Address1] DESC  represents  .OrderByDescending(a => a.Address1)
  • WHERE [Extent1].[row_number] > 400  represents  .Skip((5-1) * 100)
  • TOP (100)  represents  .Take(100)

NOTE: For the simplicity of this example I used a SELECT over the table Address but probably your query is more complex with filtered data, joins, etc.

 FURTHERMORE

You can add MAX(row_number) to the outer  SELECT, to get the total rows. Source: Return total records from SQL Server when using ROW_NUMBER