SqlSugar 2.分页查询

关注

原创 2022-06-23 23:09:47 5.9k 阅读 AI 写同款· GEO 优化 ›

C# ORM模式之 SqlSugar使用 3、稳定性和技术支持：虽然不是官方ORM, 但在稳定性上也是有着数年用户积累，如果遇到问题可以在GITHUB提出来，会根据紧急度定期解决；1、高性能：不夸张的说，去掉Sql在数据库执行的时间，SqlSugar是EF数倍性能，另外在批量操作和一对多查询上也有不错的SQL优化；MySql、SqlServer、Sqlite、Oracle 、 postgresql、达梦、人大金仓。2、高扩展性：支持自定义拉姆达函数解析、扩展数据类型、支持自定义实体特性，外部缓存等；5、创新、持续更新，向下兼容。阅读详情

文章目录

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

方便测试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