在ASP.NET Core MVC中应用Syncfusion EJ2服务器端分页、筛选和排序遇到问题

Hey there! Let's get your server-side pagination, sorting, and search working for that 7000+ record grid. Right now your setup pulls all data to the client first (which is inefficient for large datasets), so here's how to switch to true server-side processing step by step:

Step 1: Fix the Frontend Grid Configuration #

Your current code uses offline: true and loads all data upfront. We'll adjust the DataManager and Grid setup to let Syncfusion send pagination/sorting/filtering requests directly to your API.

Replace your existing Grid initialization code with this:

$(function () {
    // Configure DataManager to talk directly to your API (no offline mode)
    var dataManager = ej.DataManager({
        url: "/Product/GetProduct",
        adaptor: new ej.WebApiAdaptor()
    });

    // Error handling remains similar
    dataManager.executeQuery(new ej.Query()).fail(function (e) {
        $("#sync-msg-alert").css("display", "block");
        $("#sync-msg-alert").addClass("alert alert-danger");
        $("#sync-msg-alert > strong").html("Error!");
        $("#sync-msg-alert > span").html("Unable to load the list due to some error");
        $("#sync-msg-alert").fadeTo(5000, 500).slideUp(500, function () {
            $("#sync-msg-alert").slideUp(500);
        });
    });

    // Keep your other DataManagers for foreign keys
    var dataManagerUnitOfMeasure = ej.DataManager({ url: "/api/UnitOfMeasure", adaptor: new ej.WebApiAdaptor() });
    var dataManagerBranch = ej.DataManager({ url: "/api/Branch", adaptor: new ej.WebApiAdaptor() });
    var dataManagerDepartment = ej.DataManager({ url: "/api/Department", adaptor: new ej.WebApiAdaptor() });
    var dataManagerCurrency = ej.DataManager({ url: "/api/Currency", adaptor: new ej.WebApiAdaptor() });

    // Initialize Grid with server-side processing enabled
    $("#Grid").ejGrid({
        dataSource: dataManager,
        enableServerSideProcessing: true, // Critical: tells Grid to use server-side operations
        toolbarSettings: {
            showToolbar: true,
            toolbarItems: ["add", "delete", "update", "cancel", "search", "printGrid"]
        },
        toolbarClick: toolbarClickHandler,
        editSettings: {
            allowEditing: false,
            allowAdding: true,
            allowDeleting: true,
            showDeleteConfirmDialog: true
        },
        isResponsive: true,
        enableResponsiveRow: true,
        allowSorting: true,
        allowSearching: true,
        allowFiltering: true,
        filterSettings: {
            filterType: "excel",
            maxFilterChoices: 100,
            enableCaseSensitivity: false
        },
        allowPaging: true,
        pageSettings: {
            pageSize: 10,
            printMode: ej.Grid.PrintMode.CurrentPage
        },
        columns: [
            { field: "ProductId", headerText: 'Product Id', isPrimaryKey: true, isIdentity: true, visible: false },
            { field: "Configuration", headerText: 'Configuration', validationRules: { required: true } },
            { field: "Barcode", headerText: 'Barcode' },
            { field: "DefaultBuyingPrice", headerText: 'Buying Price', editType: "numericedit", format: "{0:n2}" },
            { field: "DefaultSellingPrice", headerText: 'Selling Price', editType: "numericedit", format: "{0:n2}" },
            { field: "BranchId", headerText: 'Branch', foreignKeyField: "BranchId", foreignKeyValue: "BranchName", dataSource: dataManagerBranch },
            { field: "DepartmentId", headerText: 'Department', foreignKeyField: "DepartmentId", foreignKeyValue: "DepartmentName", dataSource: dataManagerDepartment },
            { headerText: "Action", commands: [
                    { type: ej.Grid.UnboundType.Edit, buttonOptions: { contentType: "imageonly", prefixIcon: "e-icon e-edit", click: "editClick" } },
                    { type: ej.Grid.UnboundType.Delete, buttonOptions: { contentType: "imageonly", prefixIcon: "e-icon e-delete" } }
                ], isUnbound: true }
        ],
        actionComplete: "complete",
        recordDoubleClick: "recordDoubleClickFunc",
        actionFailure: "actionFailure"
    });
});

Step 2: Update Your API Controller to Handle Server-Side Operations #

Your /Product/GetProduct endpoint needs to accept and process the pagination/sorting/filtering parameters that Syncfusion sends (OData-style parameters like $skip, $top, $orderby, $filter). Here's how to implement this with Entity Framework:

using System.Linq;
using System.Web.Http;
using System.Web.OData;
using YourProjectNamespace.Models;

public class ProductController : ApiController
{
    private YourDbContext _dbContext = new YourDbContext();

    [HttpGet]
    public IHttpActionResult GetProduct(ODataQueryOptions<Product> options)
    {
        try
        {
            IQueryable<Product> productQuery = _dbContext.Products;

            // Apply filter (search/filter logic)
            if (options.Filter != null)
            {
                productQuery = options.Filter.ApplyTo(productQuery, new ODataQuerySettings()) as IQueryable<Product>;
            }

            // Get total record count (for pagination controls)
            int totalRecords = productQuery.Count();

            // Apply sorting
            if (options.OrderBy != null)
            {
                productQuery = options.OrderBy.ApplyTo(productQuery, new ODataQuerySettings()) as IQueryable<Product>;
            }

            // Apply pagination (skip and top)
            if (options.Skip != null)
            {
                productQuery = options.Skip.ApplyTo(productQuery, new ODataQuerySettings()) as IQueryable<Product>;
            }
            if (options.Top != null)
            {
                productQuery = options.Top.ApplyTo(productQuery, new ODataQuerySettings()) as IQueryable<Product>;
            }

            // Return data in the format Syncfusion expects
            return Ok(new { Items = productQuery.ToList(), Count = totalRecords });
        }
        catch (Exception ex)
        {
            // Log exception details here
            return InternalServerError(ex);
        }
    }

    // Keep your existing Remove action and other methods
    [HttpPost]
    public IHttpActionResult Remove(Product product)
    {
        // Your existing delete logic
    }
}

Notes for the API: #

• Make sure you have the Microsoft.AspNet.OData NuGet package installed to handle OData query options.
• If you don't want to use OData, you can manually parse query string parameters like Request.QueryString["$skip"] and build LINQ queries manually.
• Include any necessary navigation properties (like Branch/Department) using .Include() if you need to display related data.

Step 3: Test and Validate #

1. Open your browser's DevTools > Network tab.
2. Switch to page 2 of the Grid—you should see a request to /Product/GetProduct with parameters like $skip=10&$top=10 (matching your page size).
3. Check the API response: it should only contain 10 records (the second page) and include a Count field with the total number of products (~7000).

Key Fixes Recap #

• Removed offline: true to stop loading all data upfront.
• Enabled enableServerSideProcessing on the Grid to trigger server-side requests.
• Updated the API to process pagination/sorting/filtering at the database level (instead of pulling all data and filtering client-side).

内容的提问来源于stack exchange，提问作者Ali Haider