分页查询

用到的表结构、数据、实体类

方便测试Demo

Student

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `StudentId` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `SchoolId` int(11) NOT NULL,
  PRIMARY KEY (`StudentId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `student` VALUES (1, 'Zzr', 1);
INSERT INTO `student` VALUES (2, 'Ls', 2);
INSERT INTO `student` VALUES (3, 'Wem', 3);
INSERT INTO `student` VALUES (4, 'Hpf', 4);
INSERT INTO `student` VALUES (5, 'Zzr', 5);
INSERT INTO `student` VALUES (6, 'hpf', 2);
INSERT INTO `student` VALUES (7, 'hpf', 2);
INSERT INTO `student` VALUES (9, 'hpf', 2);
INSERT INTO `student` VALUES (11, 'qwe', 0);
INSERT INTO `student` VALUES (12, 'qwe', 0);

public class Student
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int StudentId { get; set; }
        public string Name { get; set; }
        public int SchoolId { get; set; }

        [Navigate(NavigateType.OneToOne, nameof(SchoolId))]//一对一 SchoolId是Student类里面的
        public School School { get; set; } //不能赋值
    }

School

CREATE TABLE `school`  (
  `SchoolId` int(11) NOT NULL AUTO_INCREMENT,
  `SchoolName` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`SchoolId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `school` VALUES (1, '一中');
INSERT INTO `school` VALUES (2, '二中');
INSERT INTO `school` VALUES (3, '三中');
INSERT INTO `school` VALUES (4, '四中');
INSERT INTO `school` VALUES (5, '五中');

public class School
    {
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int SchoolId { get; set; }
        public string SchoolName { get; set; }
    }

1.单表分页查询

int pageNumber = 1;//第几页（可随意修改查看结果）
int pageSize = 2;//每页多少数据
int totalNumber = 0;//总数据
List<Student> list = db.Queryable<Student>().ToPageList(pageNumber, pageSize, ref totalNumber);

//SELECT `StudentId`,`Name`,`SchoolId` FROM `Student`     LIMIT 0,2

2.异步单表分页查询

int pageNumber = 1;//第几页
int pageSize = 2;//每页多少数据
RefAsync<int> totalNumber = 0;//总数据
List<Student> list = await db.Queryable<Student>().ToPageListAsync(pageNumber, pageSize, totalNumber);

//SELECT `StudentId`,`Name`,`SchoolId` FROM `Student`     LIMIT 0,2

3.多表分页查询

int pageNumber = 1;//第几页
int pageSize = 2;//每页多少数据
int totalNumber = 0;//总数据

var cs = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left, st.SchoolId == sc.SchoolId))
                .Select((st, sc) => new { Id = st.SchoolId, StudentName = st.Name, SchoolName = sc.SchoolName })
                .ToPageList(pageNumber, pageSize,ref totalNumber);

//SELECT  `st`.`SchoolId` AS `Id` , `st`.`Name` AS `StudentName` , `sc`.`SchoolName` AS `SchoolName`  FROM `Student` st Left JOIN `School` sc ON ( `st`.`SchoolId` = `sc`.`SchoolId` )      LIMIT 0,2

4.异步多表分页查询

int pageNumber = 1;//第几页
int pageSize = 2;//每页多少数据
RefAsync<int> totalNumber = 0;//总数据

var cs =await db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(JoinType.Left, st.SchoolId == sc.SchoolId))
                .Select((st, sc) => new { Id = st.SchoolId, StudentName = st.Name, SchoolName = sc.SchoolName })
                .ToPageListAsync(pageNumber, pageSize, totalNumber);

//SELECT `st`.`SchoolId` AS `Id` , `st`.`Name` AS `StudentName` , `sc`.`SchoolName` AS `SchoolName`  FROM `Student` st Left JOIN `School` sc ON ( st`.`SchoolId` = `sc`.`SchoolId` ) LIMIT 0,2

5.原始分页Sql语句

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

6.SqlSever2012分页

5.0.3.2以上的版本支持

把  ToPageList 换成  ToOffsetPage

📄文档参考：分页查询