Paging in Entity Framework

Question

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

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.

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

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 4

ALTER proc [dbo].[GetNames]
    @lastRow bigint,
    @pageSize bigint,
    @totalRowCount bigint output
as
begin

select @totalRowCount = count(*) from _firstNames, _lastNames

select
    FirstName,
    LastName,
    RowNumber
from
(
    select
        fn.[FirstName] as FirstName,
        ln.[Name] as LastName,
        row_number() over( order by FirstName ) as RowNumber
    from
        _firstNames fn, _lastNames ln
) as data
where
    RowNumber between ( @lastRow + 1 ) and ( @lastRow + @pageSize )

end 

There is no way to get this into one call, but this works fast enough.

Answer 5

This queries are too small for DBManager and I can not understand why you want to do this, anyway for reduce it to ONE database call use this:

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

Answer 6

Suppose you want to get the details of Page 2 with a pagesize=4

int page =2;
int pagesize=4;

var pagedDetails= Categories.Skip(pagesize*(page-1)).Take(pagesize)
.Join(Categories.Select(item=>new {item.CategoryID,Total = Categories.Count()}),x=>x.CategoryID,y=>y.CategoryID,(x,y)=>new {Category = x,TotalRows=y.Total});

The Output will have all details of Category and TotalRows.

One DB call.

Generated SQL

-- Region Parameters
DECLARE @p0 Int = 2
DECLARE @p1 Int = 4
-- EndRegion
SELECT [t2].[CategoryID], [t2].[CategoryName], [t2].[Description], [t2].[Picture], [t5].[value] AS [TotalRows]
FROM (
    SELECT [t1].[CategoryID], [t1].[CategoryName], [t1].[Description], [t1].[Picture], [t1].[ROW_NUMBER]
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CategoryID], [t0].[CategoryName]) AS [ROW_NUMBER], [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture]
        FROM [Categories] AS [t0]
        ) AS [t1]
    WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
    ) AS [t2]
INNER JOIN (
    SELECT [t3].[CategoryID], (
        SELECT COUNT(*)
        FROM [Categories] AS [t4]
        ) AS [value]
    FROM [Categories] AS [t3]
    ) AS [t5] ON [t2].[CategoryID] = [t5].[CategoryID]
ORDER BY [t2].[ROW_NUMBER]