1.3 Query‐Page

API description

.ToPageList(pagenumber, pageSize)// Count is not returned 
.ToPageList(pagenumber, pageSize, ref totalCount)// Returns Count
.ToPageList(pagenumber, pageSize, ref totalCount,ref totalPage)// Returns Count+ total pages

//Do not consider the lower version can be used
.ToOffsetPage(pagenumber, pageSize, ref totalCount,ref totalPage)


// If the database version is newer, use ToOffsetPage instead of ToPageList
//ToPageList supports an earlier version of the database

// Note that ToPagedList with d is not sqlsugar encapsulated

Synchronous paging

int pagenumber= 1;  // pagenumber starts at 1, not zero
int pageSize = 20;
int totalCount=0;
// Single table paging
var page = db.Queryable<Student>().ToPageList(pagenumber, pageSize, ref totalCount);
// If SqlServer does not want Rownumber, you can use the newer version of ToOffsetPage to support it


// Multi-table paging
var list = db.Queryable<Student>().LeftJoin<School>((st,sc)=>st.SchoolId==sc.Id)
.Select((st,sc)=>new{Id=st.Id,Name=st.Name,SchoolName=sc.Name})
.ToPageList(pageIndex, pageSize, ref totalCount);

Asynchronous paging

RefAsync<int> total = 0; //REF and OUT do not support asynchronous, so this is the best solution for true asynchronous
Db.Queryable<Order>().ToPageListAsync(pagenumber, pageSize, total); //ToPageAsync

Generated Sql

Different databases vary

SELECT * FROM
(SELECT [ID],[SchoolId],[Name],[CreateTime],ROW_NUMBER()
OVER(ORDER BY GetDate())AS RowIndex FROM [STudent]) T
WHERE RowIndex BETWEEN 1 AND 20