剑道网格服务器端分组 [英] Kendo grid server side grouping

问题描述

我正在使用 Asp net 5、NHibernate 3.3 和 Kendo UI MVC 包装器为网格呈现客户订单表.数据库中已经有很多订单，而且数量还在不断增长.所以我决定使用服务器端分页来避免从数据库中获取所有订单.据我所知，您不能手动进行分页并将过滤、排序和分组委托给 ToDataSourceResult 方法.要么全有，要么全无.因此我尝试实现所谓的 '自定义绑定'.在我开始分组之前没问题.我需要先分组，然后在组内排序，然后提取特定页面的数据以及所有这些，而无需将所有数据加载到内存中.我的代码是这样的(为了简化阅读，我把它全部放在一起):

I am using Asp net 5, NHibernate 3.3 and Kendo UI MVC wrapper for grid to render the table of client orders. There are lots of orders in database already and the number is constantly growing. So I decided to use server side paging to avoid fetching all orders from database. As far as I know you can't do paging manually and delegate filtering, sorting and grouping to ToDataSourceResult method. It's either all or nothing. Therefore I made an attempt to implement so called 'custom binding'. No problem until I get to grouping. I need to group first, then sort inside of a group, then extract data for specific page and all that without loading all data to memory. My code is something like this (I put it all in one piece to simplify reading):

var orderList = CurrentSession.QueryOver<Order>();

// Filtering. Filter is a search string obtained from DataSourceRequest
var disjunction = new Disjunction();
disjunction.Add(Restrictions.On<Order>(e => e.Number).IsLike("%" + filter + "%"));
disjunction.Add(Restrictions.On<Order>(e => e.Customer).IsLike("%" + filter + "%"));
orderList = orderList.Where(disjunction);

// Sorting. sortColumn is also from DataSourceRequest
switch (sortColumn)
{
        case "Number":
            orderList = orderList.OrderBy(x => x.Number).Desc;
            break;
        case "GeneralInfo.LastChangeDate":
            orderList = orderList.OrderBy(x => x.LastChangeDate).Desc;
            break;
        default:
            orderList = orderList.OrderBy(x => x.Number).Desc;
            break;
     }
}

// Total is required for kendo grid when you do paging manually
var total = orderList.RowCount();


var orders = orderList
    .Fetch(x => x.OrderGoods).Eager
    .Fetch(x => x.OrderComments).Eager
    .Fetch(x => x.Documents).Eager
    .Fetch(x => x.Partner).Eager
    .Skip((request.Page - 1)*request.PageSize).Take(request.PageSize).List();

我很乐意就如何在此处添加分组提供任何建议.

I will be glad to have any advice on how to add grouping here.

推荐答案

我花了几个月的时间来找出使用 Kendo 数据源和 Kendo 网格的服务器端分组.分页、排序和过滤相当容易.但无论出于何种原因，Telerik 都没有为像分组这样的关键 LOB 流程提供足够的支持文档.很高兴你发布了这个问题，所以我有机会分享我的代码.

I worked for literally months to figure out server-side grouping using the Kendo DataSource with a Kendo Grid. Paging, sorting and filtering were fairly easy. But for whatever reason, Telerik did not offer sufficient support documentation for such a critical LOB process as grouping. I’m glad you posted this question so I’d have an opportunity to share my code.

解决方案

基本上，解决方案归结为了解 2 个关键部分，可以在以下示例项目中查看它们:https://www.dropbox.com/s/ygtk8rwl1hwjvth/KendoServerGrouping.zip?dl=0

Basically, the solution comes down to knowing 2 key parts, and they can be viewed in the following sample project: https://www.dropbox.com/s/ygtk8rwl1hwjvth/KendoServerGrouping.zip?dl=0

您正在下载的 Visual Studio (2012 | 2013) 解决方案中有一个 Web 应用程序项目，其中包含对 Kendo.Mvc 库的引用.您可以从 Telerik 的控制面板安装程序下载最新的 ASP.NET 用户界面二进制文件.安装后，二进制文件将位于以下 Windows 目录中:C:Program Files (x86)TelerikUI for ASP.NET MVC [Telerik Release Version]wrappersaspnetmvcBinaries [您的 MVC 版本]]Kendo.Mvc.dll.

There is a single web application project in the Visual Studio (2012 | 2013) solution you’re downloading, which contains a reference to the Kendo.Mvc library. You can download the latest UI for ASP.NET binaries from Telerik's Control Panel installation program. The binaries will be located in the following Windows directory after install: C:Program Files (x86)TelerikUI for ASP.NET MVC [Telerik Release Version]wrappersaspnetmvcBinaries [Your Version of MVC]Kendo.Mvc.dll.

注意:我的解决方案使用 Telerik 的 MVC 传输机制，该机制提供成熟的服务器端分页、过滤、排序以及最显着的分组.但是，我使用纯 JavaScript 来配置 Kendo DataSource 而不是 MVC 包装器.不过，我最近在 Telerik 的文档中找到了一个链接，该链接显示Razor/ASPX 中的 MVC 包装器声明.

Note: My solution uses Telerik’s MVC transport mechanism, which provides full-fledged server-side paging, filtering, sorting and, most notably, grouping. However, I use pure JavaScript to configure the Kendo DataSource and not the MVC wrappers. Still, I've recently found a link in Telerik's documentation that shows the MVC wrapper declaration in Razor/ASPX.

服务器魔法

基本上，魔法的第一部分是以下服务器端代码，位于 KendoServerGrouping.WebControllers 中的示例 WebApi 控制器中强>目录:

Basically, the first part of the magic is the following server side code, residing in the sample WebApi controller in the KendoServerGrouping.WebControllers directory:

    [System.Web.Http.AcceptVerbs("GET", "ASPNETMVC-AJAX")]
    public Kendo.Mvc.UI.DataSourceResult GetAllAccounts([System.Web.Http.ModelBinding.ModelBinder(typeof(WebApiDataSourceRequestModelBinder))] Kendo.Mvc.UI.DataSourceRequest request)
    {
        var kendoRequest = new Kendo.Mvc.UI.DataSourceRequest
        {
            Page = request.Page,
            PageSize = request.PageSize,
            Filters = request.Filters,
            Sorts = request.Sorts,
            Groups = request.Groups,
            Aggregates = request.Aggregates
        };

        // Set this to your ORM or other data source
        IQueryable accounts = dbContext.Accounts;

        /*
           The data source can even be a MongoDB collection using the
           .AsQueryable() extension and the MongoDB C# driver

           var accounts = collection.FindAllAs<Account>().AsQueryable();
        */

        var data = accounts.ToDataSourceResult(kendoRequest);

        var result = new DataSourceResult()
        {
            AggregateResults = data.AggregateResults,
            Data = data.Data,
            Errors = data.Errors,
            Total = data.Total
        };

        return result;
    }

这就是当用户与网格交互时网格将自动处理的四个服务器端操作中的任何一个所需的全部内容.特别注意方法上方的 AcceptVerbs 属性；它必须包含 ASPNETMVC-AJAX" 属性，DataSourceRequest 输入参数才能正常工作.ToDataSourceResult() 是我之前提到的 Kendo.Mvc.dll 库的最新版本提供的扩展.

This is all you’ll need for any of the four server-side actions that the grid will handle auto-magically when the user interacts with it. Pay special attention to the AcceptVerbs attribute above the method; it must include the "ASPNETMVC-AJAX" attribute for the DataSourceRequest input parameter to work properly. ToDataSourceResult() is an extension provided by recent versions of the Kendo.Mvc.dll library, which I pointed to earlier.

以上代码(据我所知)适用于任何 IQueryable 数据源，例如来自 ORM 的数据源(我已经测试了实体框架和 Telerik 数据访问/开放访问).我还能够使用 MongoDB C# 驱动程序对 MongoDB 集合进行分组.但是，这只是一个概念验证，尚未经过性能测试.

The code above will (to my knowledge) work with any IQueryable data source, such as those from ORMs (I've tested Entity Framework and Telerik Data Access/Open Access). I've also been able to group a MongoDB collection using the MongoDB C# driver. However, this is meant as a proof-of-concept, and it has not been tested for performance.

就本示例而言，WebAPI 控制器中有静态数据源来伪造 IQueryable 集合.当然，当您交换自己的数据源时，您可以删除第 45-57 行中的静态数据.

For the purposes of this example, there is static data source in the WebAPI controller to fake an IQueryable collection. Naturally, you can delete the static data from lines 45-57 when you've swapped in your own data source.

客户端魔术

Kendo DataSource 会自动从网格中传入一个专门的 DataSourceRequest 对象，该对象包含用于服务器端分页、过滤、排序和分组的所有参数，提供以下 JavaScript 中的数据源架构:

The Kendo DataSource automatically passes in a specialized DataSourceRequest object from the grid containing all the parameters for server-side paging, filtering, sorting and grouping, provided you wrap your DataSource schema inside the following JavaScript:

schema: $.extend(true, {}, kendo.data.schemas["aspnetmvc-ajax"], {
});

这可能是我追踪到的最难以捉摸的一行代码.我花了几个月的时间与 Telerik 进行了十几次交流，才让他们吐露心声.即便如此，它被揭露也纯属偶然.我无法理解为什么他们的文档中没有如此重要的细微差别.

This was perhaps the single most elusive line of code I’ve ever tracked down. It took about a dozen exchanges with Telerik over several months to get them to cough it up. And even then, it was by pure chance that it was revealed. Why such a critical nuance was absent in their documentation is beyond me.

仔细查看 index.html 文件下半部分的每个 Kendo DataSource 配置设置.最重要的是，注意没有的东西，例如 batch 和 mvcTransport 选项.包含后一个选项会以某种方式否定上述 aspnetmvc-ajax" 架构属性.

Carefully review each of the Kendo DataSource configuration settings toward the bottom half of index.html file. Most importantly, pay attention to what isn’t there, such as the batch and mvcTransport options. Including the latter option somehow negates the above "aspnetmvc-ajax" schema attribute.

在 DataSource 的 parmaterMap 函数中，请注意当且仅当执行读取操作时，必须存在以下行:

In the DataSource's parmaterMap function, make note that when – and only when - performing a read operation, the following line must be present:

return mvcTransport.parameterMap(options, operation);

您还需要确保在数据源执行之前将其包含在 HTML 中:

You will also want to be sure to include this in your HTML, before the DataSource executes:

<script src="//cdn.kendostatic.com/[Version]/js/kendo.aspnetmvc.min.js"></script>

最终结果

运行 KendoServerGrouping.Web 项目 (index.html)，如果一切顺利，网格将填充 5 个包含 AccountId、AccountName、AccountTypeCode 和 CreatedOn 字段.如果您将可见网格行数设置为 2 并按 AccountTypeCode 或 CreatedOn 分组，您将看到分组遍历分页，我相信这是您正在寻找的最终结果.

Run the KendoServerGrouping.Web project (index.html) and, if all goes well, a grid will be populated with 5 records containing AccountId, AccountName, AccountTypeCode and CreatedOn fields. If you set the number of visible grid rows to 2 and group by AccountTypeCode or CreatedOn, you’ll see that the grouping traverses the paging, which I believe is the end result you are looking for.

我希望示例项目有效并且非常适合您的情况.如果您有任何问题，请告诉我，我会尽力提供帮助.

I hope the sample project works and is a good fit for your situation. Please let me know if you have any questions, and I’ll do my best to help.

附言这是我在 SO 上的第一篇文章，所以如果有什么不符合 SO 标准，请对我放轻松.祝你好运！

P.S. This is my first post to SO, so please go easy on me if something isn’t up to SO standards. Good luck!

这篇关于剑道网格服务器端分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！