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

One approach could be, using Esql and mapping a stored procedure to an entity.
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 2

Hello,

For a stored procedure that you describe, the way to return the actual rows would be to finish the stored procedure with a SELECT statement that provides all the necessary columns. That said, I need some help in understanding the need to reduce the number of database roundtrips from 2 to 1 for a case like this. Could you please explain it to me?

Thanks,
Diego

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 3

I have found that calling skip and take in this manner did not work for a SQL2000 database, only from SQL2005 and SQL2008 onwards.  I think it's because with SQL2000 you can't easily perform a select that returns x number of rows from a certain row onwards.

Answer 4

Thanks for replies. I was trying to optimize a page by minimize database calls.
I was able to do so by calling Function Import method (with ObjectParameter), generated on the objectcontex, to call above stored procedure (which returns resultset & output parameter).
 

Answer 5

Could someone please explain this as code?