Kendo UI jQuery Grid Server端过滤 [英] Kendo UI jQuery Grid Server Side Filtering

问题描述

我正在使用Kendo UI for jQuery Grid显示一些数据.到目前为止，我正在使用客户端过滤和分页，其中所有数据记录都是在对服务器的初始调用中返回的(服务器端使用了Web API和Dapper).但是，随着数据源随着时间的增长，在一次调用中从服务器获取所有数据的想法不再可行.

I am using Kendo UI for jQuery Grid to display some data. So far I am using Client Side Filtering and Paging where all the data records were returned in the initial call to the server (Web API and Dapper are being used at server side). However, the idea of getting all the data in a single call from the server is no more feasible as the Datasource has grown with time.

我已使用以下选项启用了服务器端分页和过滤的选项:

I have enabled the option for server-side paging and filtering using the following:

serverPaging: true,
serverSorting: true,
serverAggregates: true,
serverFiltering: true,

过滤器和分页信息作为查询参数传递，例如:

The filters and paging information is being passed as query parameters like:

take: 20
skip: 0
page: 1
pageSize: 20
filter[logic]: and
filter[filters][0][logic]: or
filter[filters][0][filters][0][value]: High
filter[filters][0][filters][0][operator]: eq
filter[filters][0][filters][0][field]: status
filter[filters][0][filters][1][value]: Medium
filter[filters][0][filters][1][operator]: eq
filter[filters][0][filters][1][field]: status
filter[filters][1][logic]: and
filter[filters][1][filters][0][field]: Name
filter[filters][1][filters][0][operator]: startswith
filter[filters][1][filters][0][value]: a
filter[filters][1][filters][1][field]: Name
filter[filters][1][filters][1][operator]: contains
filter[filters][1][filters][1][value]: a

因此，我们面临的第一个问题是尝试将这些过滤器和其他参数映射到某些Kendo提供的DTO(我们已经使用Kendo MVC控件DataSourceRequest类)，但是这些过滤器从未正确地映射到该过滤器.我也知道使用[FromUri]和[FromBody]选项，因此无需提及任何此类解决方案:)

So first issue we are facing is trying to map these filters and other parameters to some Kendo provided DTO (we have used Kendo MVC controls DataSourceRequest class) but the filters never gets mapped to that properly. Also I know the use of [FromUri] and [FromBody] options so no need to mention any such solution :)

我接下来要担心的是，即使以某种方式(现在假设)我仍然能够正确地将过滤器和其他参数映射到某些DTO，我们如何才能在数据源上实际使用这些过滤器，实际上这是由从Dapper SQL查询中.

Next concern that I have is that even though somehow (assumption for now) I am able to properly map the filters and other parameters to some DTO how can we actually use these filters on our data source, which in fact is made up from a Dapper SQL query.

我还希望避免手动过滤器解析并生成动态的where子句.

Also I would also want to avoid manual filter parsing and generating a dynamic where clause.

就Kendo文档而言，尽管有这样一种可行的演示，但是服务器功能不存在或者可用的功能对我们没有太大用，如下所示:

As far as Kendo documentation is concerned although there is a workable demo of such sort but the server functionality is either not there or the one available has not been of much use to us like the following:

    public ActionResult Remote_Binding_Orders_Read([DataSourceRequest]DataSourceRequest request)
    {
        return Json(GetOrders().ToDataSourceResult(request));
    }

正如已经提到的，当从Kendo jQuery Grid进行尝试时，过滤器永远不会映射到DataSourceRequest.

As already mentioned when tried such from the Kendo jQuery Grid the filters never gets mapped to the DataSourceRequest.

再一次，由于我们使用的是Dapper，以下代码也没有多大帮助:

Yet again the following code would also be not of much help as we are using Dapper:

public static IQueryable<OrderViewModel> ApplyOrdersFiltering(this IQueryable<OrderViewModel> data, IList<IFilterDescriptor> filterDescriptors)
{
    if (filterDescriptors != null && filterDescriptors.Any())
    {
        data = data.Where(ExpressionBuilder.Expression<OrderViewModel>(filterDescriptors, false));
    }
    return data;
}

orders = orders.ApplyOrdersFiltering(request.Filters);

如果有人遇到相同的问题并找到了解决方法，那么将不胜感激.

If anybody had the same issue and found some workaround then such help would be much appreciated.

注意:不能选择从Dapper更改为Entity Framework，并且同样适用于Kendo jQuery Grid.

Note: Changing from Dapper to Entity Framework is not an option, and the same also applies to Kendo jQuery Grid.

推荐答案

毕竟还是找不到解决方法(即使是telerik支持也无法解决，也无法提出任何解决方法).

After all was not able to find some workaround (even the telerik support just went round and round and was not able to suggest any work around).

因此决定采用自定义实现(也为可能遇到相同问题的所有开发人员创建了一个甜蜜的小工具).

So decided for a custom implementation (also created a sweet nuget for any and all developers that may run into the same).

因此必须手动生成可用于创建动态WHERE，ORDER BY，GROUP BY和SELECT(用于聚合)子句的表达式.

Thus had to manually generate expressions that can be used for creation of dynamic WHERE, ORDER BY, GROUP BY and SELECT (for aggregation) clauses.

有关更多详细信息，请参阅以下存储库: KendoGridFASMS

For further detail please refer to the following repository: KendoGridFASMS

还有nuget包: Nuget Package

Also the nuget package: Nuget Package

存储库也向所有人开放以供进一步定制:)

Also the repository is open to all for further customization :)

该库现在还支持.NET Core和.NET标准框架.

The library now supports .NET Core and .NET Standard Frameworks as well.

这篇关于Kendo UI jQuery Grid Server端过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！