如何在C#中正确使用Skip()和Take()实现服务端分页？

系统加载全部4218条记录，已使用Skip和Take但仅能获取前10条数据，需实现服务端分页。

实体类代码 #

public List<NonconformingServiceModel> GetAllNSIssue(int UserID, int pageNumber = 1)
{
    using (SIRManagementSystemEntities _entities = new SIRManagementSystemEntities())
    {
        NonconformingServiceModel NSModel = new NonconformingServiceModel();
        var date1 = new DateTime(2000, 1, 1, 1, 0, 0);
        List<NonconformingServiceModel> result = new List<NonconformingServiceModel>();

        var userData = (from _user in _entities.Administrators where _user.UserID == UserID select _user).FirstOrDefault();

        if (userData != null)
        {
            if (userData.UserType == 1 || userData.UserType == 0 || userData.UserID == 38)
            {
                result = (from _NSDetails in _entities.NonconformingServices
                          join _creator in _entities.Administrators on _NSDetails.CreatedBy equals _creator.UserID
                          join _SIRDetails in _entities.SIR_InitialDetail on _NSDetails.NS_ID equals _SIRDetails.NSCCRefID into _sir
                          from _SIRDetails in _sir.DefaultIfEmpty()
                          select new NonconformingServiceModel
                          {
                              ID = _NSDetails.NS_ID,
                              Type = _NSDetails.Type,
                              Category = _NSDetails.Category,
                              Reason = _NSDetails.Reason,
                              Remarks = _NSDetails.Remarks,
                              ModeThru = _NSDetails.Mode,
                              DateDelivered = _NSDetails.DateDelivered,
                              LoadingDate = _NSDetails.LoadingDate,
                              DateIssue = _NSDetails.DateIssue,
                              Origin = _NSDetails.Origin,
                              Destination = _NSDetails.Destination,
                              CartonNumber = _NSDetails.CartonNumber,
                              ShipmentNumber = _NSDetails.ShipmentNumber,
                              ShipperName = _NSDetails.ShipperName,
                              ConsigneeName = _NSDetails.ConsigneeName,
                              Address = _NSDetails.Address,
                              NSGroup = _NSDetails.NSGroup,
                              NSCode = _NSDetails.NSCode,
                              OtherType = _NSDetails.OtherType,
                              CreatedByID = _NSDetails.CreatedBy,
                              CreatedByName = _creator.FirstName + " " + _creator.LastName,
                              SIRCode = _SIRDetails.Code == null ? "" : _SIRDetails.Code,
                              SIRDateIssue = _SIRDetails.IssueDate == null ? date1 : _SIRDetails.IssueDate,
                              SIRDueDate = _SIRDetails.DueDate == null ? date1 : _SIRDetails.DueDate,
                              Status = _SIRDetails.Code == null ? 0 : _SIRDetails.Status
                          }).ToList();

            }
            else
            {
                result = (from _creator in _entities.Administrators
                          join _NSDetails in _entities.NonconformingServices on _creator.UserID equals _NSDetails.CreatedBy
                          join _mapping in _entities.CCNSDEPTMappings on _NSDetails.NSCode equals _mapping.NSCode
                          join _SIRDetails in _entities.SIR_InitialDetail on _NSDetails.NS_ID equals _SIRDetails.NSCCRefID into _sir
                          from _SIRDetails in _sir.DefaultIfEmpty()
                          where _creator.Department == userData.Department || _mapping.ConcernDeptID == userData.Department || _creator.Department == userData.SecondDept || _mapping.ConcernDeptID == userData.SecondDept
                          select new NonconformingServiceModel
                          {
                              ID = _NSDetails.NS_ID,
                              Type = _NSDetails.Type,
                              Category = _NSDetails.Category,
                              Reason = _NSDetails.Reason,
                              Remarks = _NSDetails.Remarks,
                              ModeThru = _NSDetails.Mode,
                              DateDelivered = _NSDetails.DateDelivered,
                              LoadingDate = _NSDetails.LoadingDate,
                              DateIssue = _NSDetails.DateIssue,
                              Origin = _NSDetails.Origin,
                              Destination = _NSDetails.Destination,
                              CartonNumber = _NSDetails.CartonNumber,
                              ShipmentNumber = _NSDetails.ShipmentNumber,
                              ShipperName = _NSDetails.ShipperName,
                              ConsigneeName = _NSDetails.ConsigneeName,
                              Address = _NSDetails.Address,
                              NSGroup = _NSDetails.NSGroup,
                              NSCode = _NSDetails.NSCode,
                              OtherType = _NSDetails.OtherType,
                              CreatedByID = _NSDetails.CreatedBy,
                              CreatedByName = _creator.FirstName + " " + _creator.LastName,
                              SIRCode = _SIRDetails.Code == null ? "" : _SIRDetails.Code,
                              SIRDateIssue = _SIRDetails.IssueDate == null ? date1 : _SIRDetails.IssueDate,
                              SIRDueDate = _SIRDetails.DueDate == null ? date1 : _SIRDetails.DueDate,
                              Status = _SIRDetails.Code == null ? 0 : _SIRDetails.Status
                          }).ToList();

               //   var  _result = (List<NonconformingServiceModel>)result.GroupBy(x => x.NSCode).Select(x => x.FirstOrDefault()).ToList().Skip((pageNumber - 1) * 10).Take(10);
            }
            var _result = result.GroupBy(x => x.NSCode).Select(x => x.FirstOrDefault()).ToList().Skip((pageNumber - 1) * 10).Take(10);

            foreach (var item in _result)
            {
                if (item.Status == null || item.Status == 0)
                {
                    if (item.SIRCode != "")
                    {
                        DateTime dueDate = item.SIRDueDate.Value;
                        DateTime now = DateTime.Now;
                        if (now.Date > dueDate.Date)
                        {
                            item.FormatIssueDate = "late";
                        }
                        else
                        {
                            item.FormatIssueDate = "not";
                        }
                    }
                    else
                    {
                        item.SIRCode = "";
                        item.SIRDateIssue = null;
                        item.SIRDueDate = null;
                        item.Status = 0;
                    }
                }
                else
                {
                    item.SIRID = (from _sirDet in _entities.SIR_InitialDetail where _sirDet.Code == item.SIRCode select _sirDet.sirID).FirstOrDefault();
                    item.SubmissionDate = (from _sirDet in _entities.SIR_InitialDetail where _sirDet.sirID == item.SIRID select _sirDet.SubmissionDate).FirstOrDefault();
                    item.CEDueDate = (from _sirCA in _entities.SIR_CorrectActDetail where _sirCA.Cor_SIRID == item.SIRID select _sirCA.CEDueDate).FirstOrDefault();
                    item.FormatIssueDate = "submitted";
                }
                item.TypeName = (from _type in _entities.DDNSTypes where _type.TypeID == item.Type select _type.Label).FirstOrDefault();
                if (item.Type == 19)
                {
                    item.TypeName = item.OtherType;
                }
                item.CategoryName = (from _cat in _entities.DDCategories where _cat.SIRCAT_ID == item.Category select _cat.Label).FirstOrDefault();

                var deptArray = (from _mapping in _entities.CCNSDEPTMappings
                                 join _dept in _entities.Departments on _mapping.ConcernDeptID equals _dept.DeptID
                                 where _mapping.NSCode == item.NSCode
                                 select _dept.DeptID).ToList();

                var deptCode = (from _mapping in _entities.CCNSDEPTMappings
                                join _dept in _entities.Departments on _mapping.ConcernDeptID equals _dept.DeptID
                                where _mapping.NSCode == item.NSCode
                                select _dept.DeptCode).ToList();

                item.ConcernedDeptArray = deptArray;
                item.ConcernedDeptName = deptCode;

                item.Company = (from _user in _entities.Administrators
                                join _dept in _entities.Departments on _user.Department equals _dept.DeptID
                                where _user.UserID == item.CreatedByID select _dept.Company).FirstOrDefault();
            }

            return _result.ToList();
        }
        return null;

    }
}

视图模型代码 #

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SIMS.Model.Models
{
    public class NonconformingServiceModel
    {
        public int ID { get; set; }
        public byte Type { get; set; }
        public string TypeName { get; set; }
        public string OtherType { get; set; }
        public byte Category { get; set; }
        public string CategoryName { get; set; }
        public string OtherCategory { get; set; }
        public string Reason { get; set; }
        public string Remarks { get; set; }
        public string ModeThru { get; set; }
        public DateTime? DateDelivered { get; set; }
        public DateTime? LoadingDate { get; set; }
        public DateTime DateIssue { get; set; }
        public string ConcernedDeptString { get; set; }
        public List<int> ConcernedDeptArray { get; set; }
        public List<string> ConcernedDeptName { get; set; }
        public string Origin { get; set; }
        public string Destination { get; set; }
        public string CartonNumber { get; set; }
        public string ShipmentNumber { get; set; }
        public string ShipperName { get; set; }
        public string ConsigneeName { get; set; }
        public string Address { get; set; }
        public string NSGroup { get; set; }
        public int CreatedByID { get; set; }
        public string CreatedByName { get; set; }
        public int CreatedByDept { get; set; }
        public int UserType { get; set; }
        public string NSCode { get; set; }
        public byte? Status { get; set; }
        public int? SIRID { get; set; }
        public string SIRCode { get; set; }
        public DateTime? SIRDateIssue { get; set; }
        public DateTime? SIRDueDate { get; set; }
        public DateTime? SubmissionDate { get; set; }
        public DateTime? CEDueDate { get; set; }
        public DateTime? DetectionDate { get; set; }
        public string FormatIssueDate { get; set; }
        public int UserID { get; set; }
        public int? Company { get; set; }
    }
}

控制器代码 #

public ActionResult GetAllNSLogDetails(int UserID)
{
    JsonResult _json = new JsonResult();
    NonconformingServiceManager _NSManager = new NonconformingServiceManager();
    _json.Data = _NSManager.GetAllNSIssue(UserID);
    _json.MaxJsonLength = Int32.MaxValue;
    return _json;
}

视图代码 #

<!-- Begin Page Content -->
<div class="container-fluid">

    <nav aria-label="breadcrumb">
        <ol class="breadcrumb">
            <li class="breadcrumb-item"><a href="/Home/Dashboard">Dashboard</a></li>
            <li class="breadcrumb-item active" aria-current="page">Nonconforming Service</li>
        </ol>
    </nav>

    <div class="card shadow mb-4">
        <div class="card-header py-3 d-flex flex-row align-items-center justify-content-between">
            <h6 class="m-0 font-weight-bold text-primary">Nonconforming Services Log</h6>
            <div class="text-right">
                <button type="button" class="btn btn-outline-info" onclick="window.location.reload();">Show All Data</button>
                <button type="button" data-toggle="modal" data-target=".modal-filter-table" class="btn btn-outline-info"><i class="fas fa-filter"></i>Sort by</button>
                <button type="button" onclick="CreateNewConcern()" class="btn btn-outline-primary"><i class="far fa-plus-square"></i> Create New NS</button>
            </div>
        </div>

        <div class="card-body">
            <div class="card-body table-responsive">
                <table id="NonconformingServiceLogGrid" class="table table-hover" style="width:100%">
                    <thead>
                        <tr>
                            <th>Date Created</th>
                            <th>NS Code</th>
                            <th>SIR Code</th>
                            <th>Date Issue</th>
                            <th>Due Date</th>
                            <th>Submission Date</th>
                            <th>Type</th>
                            <th>Category</th>
                            <th>Concerned Dept.</th>
                            <th>SIR Status</th>
                            <th>CE Due Date</th>
                        </tr>
                    </thead>
                    <tfoot>
                        <tr>
                            <th>Date Created</th>
                            <th>NS Code</th>
                            <th>SIR Code</th>
                            <th>Date Issue</th>
                            <th>Due Date</th>
                            <th>Submission Date</th>
                            <th>Type</th>
                            <th>Category</th>
                            <th>Concerned Dept.</th>
                            <th>SIR Status</th>
                            <th>CE Due Date</th>
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>
    </div>

</div>

核心问题 #

1. 假分页而非服务端分页：当前代码先调用.ToList()把全量数据加载到内存，再执行Skip和Take，属于内存分页，既浪费资源，又因为前端未传递动态页码，导致始终返回第一页数据。
2. 控制器未接收分页参数：GetAllNSLogDetails仅接收UserID，未获取前端传来的页码，导致GetAllNSIssue默认使用pageNumber=1，只能拿到前10条。
3. 缺少总记录数返回：前端分页组件需要总记录数计算总页数，当前接口只返回分页数据，无法支撑完整分页逻辑。

分步解决 #

1. 修改实体类方法，实现真正的服务端分页 #

将分组、分页操作放在.ToList()之前，让EF将逻辑转为SQL在数据库执行，避免加载全量数据：

public (List<NonconformingServiceModel> Data, int TotalCount) GetAllNSIssue(int UserID, int pageNumber = 1, int pageSize = 10)
{
    using (SIRManagementSystemEntities _entities = new SIRManagementSystemEntities())
    {
        var date1 = new DateTime(2000, 1, 1, 1, 0, 0);
        IQueryable<NonconformingServiceModel> query = null;

        var userData = _entities.Administrators.FirstOrDefault(_user => _user.UserID == UserID);
        if (userData == null)
            return (null, 0);

        if (userData.UserType == 1 || userData.UserType == 0 || userData.UserID == 38)
        {
            query = from _NSDetails in _entities.NonconformingServices
                    join _creator in _entities.Administrators on _NSDetails.CreatedBy equals _creator.UserID
                    join _SIRDetails in _entities.SIR_InitialDetail on _NSDetails.NS_ID equals _SIRDetails.NSCCRefID into _sir
                    from _SIRDetails in _sir.DefaultIfEmpty()
                    select new NonconformingServiceModel
                    {
                        ID = _NSDetails.NS_ID,
                        Type = _NSDetails.Type,
                        Category = _NSDetails.Category,
                        Reason = _NSDetails.Reason,
                        Remarks = _NSDetails.Remarks,
                        ModeThru = _NSDetails.Mode,
                        DateDelivered = _NSDetails.DateDelivered,
                        LoadingDate = _NSDetails.LoadingDate,
                        DateIssue = _NSDetails.DateIssue,
                        Origin = _NSDetails.Origin,
                        Destination = _NSDetails.Destination,
                        CartonNumber = _NSDetails.CartonNumber,
                        ShipmentNumber = _NSDetails.ShipmentNumber,
                        ShipperName = _NSDetails.ShipperName,
                        ConsigneeName = _NSDetails.ConsigneeName,
                        Address = _NSDetails.Address,
                        NSGroup = _NSDetails.NSGroup,
                        NSCode = _NSDetails.NSCode,
                        OtherType = _NSDetails.OtherType,
                        CreatedByID = _NSDetails.CreatedBy,
                        CreatedByName = _creator.FirstName + " " + _creator.LastName,
                        SIRCode = _SIRDetails.Code == null ? "" : _SIRDetails.Code,
                        SIRDateIssue = _SIRDetails.IssueDate == null ?