Pagination

nicyeNovember 21, 2021About 1 minAbout 371 words

Pagination

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, connectionString)
    .Build(); //Be sure to define as singleton mode

class Topic {
    [Column(IsIdentity = true)]
    public int Id { get; set; }
    public string Title { get; set; }
    public int Clicks { get; set; }
    public DateTime CreateTime { get; set; }

    public int CategoryId { get; set; }
}

20 data per page, query page 1

var list = fsql.Select<Topic>()
    .Where(a => a.Id > 10)
    .Count(out var total) //Total number of records
    .Page(1, 20)
    .Tolist();

Optimization

For the previous versions of SqlServer 2012, use row_number for pagination;

For SqlServer 2012+ version, using the latest fetch next rows pagination;

API

MethodsReturnParametersDescription
ToSqlstringReturn the SQL statement to be executed
ToListList<T1>Execute SQL query, and return the records of all the fields of the T1 entity. If there are navigation properties, they will be queried and returned together. If the record does not exist, a list with Count of 0 will be returned.
ToList<T>List<T>LambdaExecute SQL query, and return the record of the specified fields. If the record does not exist, a list with Count of 0 will be returned.
ToList<T>List<T>string fieldExecute SQL query, and return the record of the specified fields of the parameters, and receive it as a tuple or basic type (int, string, long). If the record does not exist, a list with Count of 0 will be returned.
【Pagination】
CountlongNumber of records queried
Count<this>out longThe number of records to be queried, returned in the form of parameter with out
Skip<this>int offsetQuery the number of rows shifted backward
Offset<this>int offsetQuery the number of rows shifted backward
Limit<this>int limitNumber of query data
Take<this>int limitNumber of query data
Page<this>int pageIndex, int pageSizePagination

参考资料