仅对 LINQ to Entities 中已排序的输入支持方法“Skip”。必须在调用“Skip”方法之前调用方法“OrderBy”

 

问题描述：

我们在使用Linq To Entities进行分页查询的时候，如果不小心，可能会出现如标题所示的错误，导致查询分页数据失败。那么原因何在呢？

 

原因：

原因在于用Linq To Entities默认查询出来的数据顺序是聚集索引的顺序，所以必须要加上排序操作。所以为OrderBy或OrderByDescending加上聚集索引字段名称即可。

 

如下代码所示：

  public List<SYS_OperatorDTO> GetSysOperators(int pageIndex, int pageSize, out int total)
        {
            var query = from a in Db.Set<SYS_Operator>()
                        join b in Db.Set<SYS_Department>() on a.DepartmentId equals b.DepartmentId into tempAb
                        from ttAb in tempAb.DefaultIfEmpty()

                        join c in Db.Set<SYS_Role>() on a.RoleId equals c.RoleId into tempAc
                        from ttAc in tempAc.DefaultIfEmpty()

                        where a.Status == 1
                        select new SYS_OperatorDTO
                        {
                            OpeId = a.OperatorId,
                            ComId = a.CompanyId,
                            DepId = a.DepartmentId,
                            DepName = ttAb == null ? "" : ttAb.DepartmentName,
                            RolId = a.RoleId,
                            RolName = ttAc == null ? "" : ttAc.RoleName,
                            Acc = a.Account,
                            Pwd = a.Pwd,
                            PhotoWidth = a.PhotoWidth,
                            PhotoHeight = a.PhotoHeight,
                            PhotoPath = a.PhotoPath,
                            Nickname = a.Nickname,
                            Realname = a.Realname,
                            WeChat = a.WeChat,
                            Microblog = a.Microblog,
                            Mobile = a.Mobile,
                            Tel = a.Tel,
                            QQ = a.QQ,
                            Email = a.Email,
                            Address = a.Address,
                            IsEnable = a.IsEnable,
                            DtlInfo = a.DtlInfo,

                            Status = a.Status,
                            CrtTime = a.CrtTime,
                            CrtOptId = a.CrtOptId,
                            ModTime = a.ModTime,
                            ModOptId = a.ModOptId
                        };

            //数据总记录数
            total = query.Count();
            //分页数据
            var list = query.OrderByDescending(m => m.CrtTime).Skip((pageIndex - 1) * pageSize).Take(pageSize);

            return list.ToList();
        }