Telerik的MVC扩展电网 - 如何让电网滤波器适用于初次LINQ查询或得到传下来的分贝？ [英] Telerik MVC Extensions Grid - How to have the grid filter apply to initial LINQ query or get passed down to db?

问题描述

目前在我的MVC网格我使用正常的服务器绑定，并且过滤器，然后附加到URL作为查询字符串。这种方法的问题是，如果我查询，默认情况下有数千条记录的网格，但我只显示我的网格的第30条记录的第一页（呼叫过滤器）。同样的事情也适用于为姓氏的字符串过滤器。我筛选我在2000年记录的姓史密斯，获得100条记录，30只显示第一页。然后我实际上将查询一个人的对象，有充分的2K对象返回，它过滤到100，和显示器30这是可怕的效率低下。

Currently in my MVC grids I am using normal Server Bindings, and the filters then are appended to the URL as query strings. The problem with this method is that if I am querying a grid that by default has thousands of records, but I am only displaying the first 30 records on the first page (paging filter) of my grid. Same thing would apply to a string filter for last name. I filter my 2000 records for last name smith, get 100 records, only 30 displayed on first page. I will then actually query a person object, have the full 2k objects returned, filter it to 100, and display 30. This is horribly inefficient.

怎样才能通过过滤器参数转换为LINQ查询为例所以最初的查询只返回页面上显示的结果吗？也有一些自动化的方式来为任何网格做到这一点一般？或者，你必须写这个逻辑为每一个网格，你有吗？

How does one pass the filter parameters into a LINQ query for example so the initial query only returns results shown on that page? Also is there some automated way to do this generically for any grid? Or would you have to write this logic for every single grid you have?

我知道，如果 ToGridModel ，我导出网格时使用Excel中：

I know if ToGridModel, which I use when exporting a grid to excel:

 public ActionResult Export(int page, string orderBy, string filter, string groupBy)
    {
        //Get the data representing the current grid state - page, sort and filter
        List<Building> buildings = _buildingService.GetList();
        List<BuildingModel> buildingModels = new List<BuildingModel>();

        buildings.ForEach(x => buildingModels.Add(_buildingService.ConvertToModel(x)));

        GridModel model = buildingModels.AsQueryable().ToGridModel(1, buildingModels.Count, orderBy, groupBy, filter);

        MemoryStream fileOutput = ExcelUtil.ExportGridModelToExcel<BuildingModel>(model);

        return File(fileOutput.ToArray(),   //The binary data of the XLS file
            "application/vnd.ms-excel", //MIME type of Excel files
            "BuildingsReport.xls");     //Suggested file name in the "Save as" dialog which will be displayed to the end user
    }

，但我想则另一问题是，网格本身是由的ViewModels，而不是POCO对象。因此，即使那时，当我导出到Excel。我不得不再次查询整个结果集，然后筛选下来。

but I guess then another problem is that the grid itself is made up of ViewModels, not the POCO object. So even then, when I export to excel. I have to requery the whole result set, and then filter it down.

当然，还有一个更好的方法来做到这一点？

Surely there is a better way to do this?

推荐答案

您可以使用自定义绑定来做到这一点。

You can use Custom Binding to do this.

它简单的例子，你可以在这里阅读：<一href=\"http://www.telerik.com/help/aspnet-mvc/telerik-ui-components-grid-data-binding-custom-binding.html\"相对=nofollow> Telerik的文档

simple example of it you can read here: Telerik Documentation

有关更通用的方法，你可以使用方法 CreateFilterEx pression 类的<​​code> FilterDescriptor

For more generic approach you can use method CreateFilterExpression of class FilterDescriptor

更新

通用示例：

[GridAction(EnableCustomBinding = true)]
public ViewResult GetRecords(GridCommand command)
{
    using (var context = _contextFactory())
    {
        var records = context.Set<Records>();
        if (command.FilterDescriptors.Any())    //RequestNumber
        {                    
            var filter = command.FilterDescriptors.GetFilter<ChangeRecord>();
            records = records.Where(filter);
        }
        return View(new GridModel(records.ToArray()));
    }
}

public static class GridCommandExtensions
{
    public static Expression<Func<TGridModel, bool>> GetFilter<TGridModel>(this IList<IFilterDescriptor> filterDescriptors)
    {
        var filters = filterDescriptors.SelectMany(GetAllFilterDescriptors).ToArray();
        var parameter = Expression.Parameter(typeof(TGridModel), "c");
        if (filters.Length == 1)
            return Expression.Lambda<Func<TGridModel, bool>>(GetExpression(parameter, filters[0]), parameter);

        Expression exp = null;
        for (int index = 0; index < filters.Length; index += 2)   // условие И
        {
            var filter1 = filters[index];

            if (index == filters.Length - 1)
            {
                exp = Expression.AndAlso(exp, GetExpression(parameter, filter1));
                break;
            }
            var filter2 = filters[index + 1];
            var left = GetExpression(parameter, filter1);
            var right = GetExpression(parameter, filter2);
            exp = exp == null
                ? Expression.AndAlso(left, right)
                : Expression.AndAlso(exp, Expression.AndAlso(left, right));
        }

        return Expression.Lambda<Func<TGridModel, bool>>(exp, parameter);
    }
    private static Expression GetExpression(ParameterExpression parameter, FilterDescriptor filter)
    {
        var containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
        var startsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
        var endsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });

        var property = filter.Member.Contains(".") ?
            filter.Member.Split('.').Aggregate((Expression)parameter, Expression.Property)  // (x => x.Property.FieldName)
            : Expression.Property(parameter, filter.Member);                                // (x => x.FieldName)
        var constant = Expression.Constant(filter.Value);               // значение для выражения

        switch (filter.Operator)
        {
            case FilterOperator.IsEqualTo:
                return Expression.Equal(property, constant);
            case FilterOperator.IsNotEqualTo:
                return Expression.NotEqual(property, constant);

            case FilterOperator.Contains:
                return Expression.Call(property, containsMethod, constant);
            case FilterOperator.StartsWith:
                return Expression.Call(property, startsWithMethod, constant);
            case FilterOperator.EndsWith:
                return Expression.Call(property, endsWithMethod, constant);

            case FilterOperator.IsGreaterThan:
                return Expression.GreaterThan(property, constant);
            case FilterOperator.IsGreaterThanOrEqualTo:
                return Expression.GreaterThanOrEqual(property, constant);
            case FilterOperator.IsLessThan:
                return Expression.LessThan(property, constant);
            case FilterOperator.IsLessThanOrEqualTo:
                return Expression.LessThanOrEqual(property, constant);
            default:
                throw new InvalidOperationException(string.Format("Неподдерживаемая операция {0} для колонки {1}", filter.Operator, filter.Member));
        }
    }
    public static IEnumerable<FilterDescriptor> GetAllFilterDescriptors(this IFilterDescriptor descriptor)
    {
        var filterDescriptor = descriptor as FilterDescriptor;
        if (filterDescriptor != null)
        {
            yield return filterDescriptor;
            yield break;
        }

        var compositeFilterDescriptor = descriptor as CompositeFilterDescriptor;
        if (compositeFilterDescriptor != null)
        {
            if (compositeFilterDescriptor.LogicalOperator == FilterCompositionLogicalOperator.Or)
                throw new ArgumentOutOfRangeException("descriptor", "В фильтрах не поддерживается OR");

            foreach (var childDescriptor in compositeFilterDescriptor.FilterDescriptors.SelectMany(GetAllFilterDescriptors))
                yield return childDescriptor;
        }
    }
 }

这篇关于Telerik的MVC扩展电网 - 如何让电网滤波器适用于初次LINQ查询或得到传下来的分贝？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！