Paging in Entity Framework

Question

Hello,

In Entity Framework, using LINQ to Entities, database paging is usually done in following manner:

int totalRecords = EntityContext.Context.UserSet.Count;
var list     = EntityContext.Context.UserSet
           .Skip(startingRecordNumber)
           .Take(pageSize)
           .ToList();

This results in TWO database calls.

Please tell, how to reduce it to ONE database call.

Thank You.

Answer 1

Hmmm... the actual call that uses paging is the second one - that's a single call.

The second call is to determine the total number of rows - that's quite a different operation, and I am not aware of any way you could combine those two distinct operations into a single database call with the Entity Framework.

Question is: do you really need the total number of rows? What for? Is that worth a second database call or not?

Another option you would have is to use the EntityObjectSource (in ASP.NET) and then bind this to e.g. a GridView, and enable AllowPaging and AllowSorting etc. on the GridView, and let the ASP.NET runtime handle all the nitty-gritty work of retrieving the appropriate data page and displaying it.

Marc

Answer 2

Using Esql and mapping a stored procedure to an entity can solve the problem. SP will return totalRows as output parameter and current page as resultset.

CREATE PROCEDURE getPagedList(
@PageNumber int,
@PageSize int,
@totalRecordCount int OUTPUT
AS

//Return paged records

Please advise.

Thank You.

Answer 3

Whats wrong with two calls? They are small and quick queries. Databases are designed to support lots of small queries.

A developing a complex solution to do one query for paging isn't going give you much pay off.