Kendo UI for ASP.NET MVC grid virtualization to remote data

Although there is documentation and demos for implementing binding to remote data, it can not be applied to the three-layer architecture. For example, if your web application does not have any local database and for displaying it’s content it is connected to the separated application via WCF or WEP API, which returns required data and you want to implement virtualization (or paging) including the set of basic grid operations, such as filtering, aggregations and sorting.

The following article could be useful if for the data retrieving you need to create instance of IQueryable and then working with the result of this query, also you does not trust Kendo UI .ToDataSourceResult() method, or want to implement some additional work on results, or you have some unrecognizable problems with using it.

The basic example of your initial solution:

Page:

@(Html.Kendo().Grid()
    .Name("gridUsers")
    .Columns(columns =>
     {
         columns.Bound(c => c.Name);
         columns.Bound(c => c.Description);
         columns.Bound(c => c.Cost)
             .ClientFooterTemplate("#= kendo.toString(sum, 'n2')#");
     })
     .ColumnMenu()
     .Resizable(resizable => resizable.Columns(true))
     .Reorderable(reorderable => reorderable.Columns(true))
     .Groupable()
     .Sortable(sortable => sortable
         .AllowUnsort(true)
         .SortMode(GridSortMode.MultipleColumn))
         .Scrollable(scrollable => scrollable.Virtual(true))
     .DataSource(dataSource => dataSource
         .Ajax()
         .PageSize(100)
         .Read("Virtualization", "Prices")
         .Aggregates(aggregates =>
         {
             aggregates.Add(p => p.Cost).Sum();
         })
     )
 )

Controller:

 public ActionResult Virtualization([DataSourceRequest] DataSourceRequest request)
 {
     IQueryable data = _factory.Create(storages => storages.Prices);
     return Json(data.ToDataSourceResult(request));
 }

As we assumed it will does not work. For example the number of returning entities is setting locally on the separated server to 100 and could not be controlled from web application. So it performs filtering and aggregating only on this 100 entities.

The general problem is paging. Let solve it. So you should create request to skip the number of pages and take the resulting number of entities:

 public ActionResult Virtualization([DataSourceRequest] DataSourceRequest request)
 {
     IQueryable data = _factory.Create(storages => storages.Prices);
     var skipCount = (request.Page - 1) * request.PageSize;
     data = data.Skip(skipCount).Take(request.PageSize);
     
     request.Page = 1;
     return Json(data.ToDataSourceResult(request));
 }

Why do we do (request.Page – 1) * request.PageSize? It so because Telerik implementation is not zero-based but 1-based indexing. So, if we want to get the first 100 entities we should have and offset = 0.

Why do we set request.Page to 1? Because if we will not set it to initial value, it will execute paging on the resulting 100 entities. So, the resulting set will be empty.

The following problem is aggregating. We can solve it as following

private List Aggregates(IQueryable data, DataSourceRequest request)
{
List aggregateResults = null;

if (request.Aggregates != null && request.Aggregates.Any())
{
    aggregateResults = new List();
    foreach (var item in request.Aggregates)
    {
        var paramExpr = Expression.Parameter(typeof(IQueryable), "x");

        foreach (var aggregate in item.Aggregates)
        {
            MethodCallExpression expr = aggregate.CreateAggregateExpression(paramExpr, true);
            Expression<Func<TReturnType, double>> sumExpr = expr.Arguments[1];
            AggregateResult aggregateResult = null;
            if (aggregate is SumFunction)
            {
               aggregateResult = new AggregateResult(data.Sum(sumExpr), aggregate);
            }

            aggregateResults.Add(aggregateResult);
        }
     }
 }
return aggregateResults;
}

Hopefully, Kendo UI provides a method for creating expression for resulting method.

As you know, LINQ extension methods are static functions with first argument of IEnumerable/IQueryable and second argument is an expression. So, for the sumExpr we getting the second argument of constructed expression.

We calling Aggregates method before paging methods because we want sum of all values and not only of paged 100.

The filtering implementation is most interesting. Actually, I have implement the whole technique because of custom filtering. My task was to implement a separate search field, which will filter grid in following way: row should not be displayed if each cell values of which does not contain search string. Kendo provides some types for filtering values: numeric columns could be filtered due to equality, inequality, lower or higher search number, string columns could be filtered as equality, containing or not containing search substring. As you can note, if there is containing filter for string columns, but there is not contain for numeric columns.

private void Filters(IQueryable data, DataSourceRequest request)
{
    if (request.Filters != null && request.Filters.Any())
    {
        var kendoExpressionModifier = new KendoExpressionModifier();

        foreach (var item in dataSourceRequest.Filters)
        {
            var paramExpr = Expression.Parameter(typeof(TReturnType), "item");
            var expr = kendoExpressionModifier.Modify(item.CreateFilterExpression(paramExpr));

            var lambda = Expression.Lambda<Func<TReturnType, bool>>(expr, paramExpr);
            data = data.Where(lambda);
        }
    }
}

What is KendoExpressionModifier and what type of expression generates Kendo, you can see at this StackOverflow question.

We also calling Filters before paging, because in the paged 100 entities there could not be any entity which is match our search condition.

And we final it is sorting, which is implemented as follows:

private void Sort(IQueryable data, DataSourceRequest request)
{
    if (request.Sorts != null && request.Sorts.Any())
    {
        var isOrdered = data is IOrderedEnumerable;
        foreach (var item in request.Sorts)
        {
            switch (item.SortDirection)
            {
                case ListSortDirection.Ascending:
                    data = data.OrderBy(x => x.GetProperty(item.Member).GetValue(x));
                    break;
               case ListSortDirection.Descending:
                    data = data.OrderByDescending(x => x.GetProperty(item.Member).GetValue(x));
                    break;
             }
       }
    }
}

That’s all about basic operations. But you will get the strange behaviour if you will not set the Total property of DataSourceResult.

For the sake of simplicity and future reusage all of this methods could be nested inside the separate class:

public class QueryBuilder
{
    private DataSourceRequest _request;
    private IQueryable _query;
    
    public QueryBuilder(IQueryable query, DataSourceRequest dsr)
    {
        this._query = query;
        this._request = dsr;
    }
    
    private IEnumerable Aggregates() { /**/ }
    private void Sorts() { /**/ }
    private void Filters() { /**/ }
    private void Paging() { /**/ }

    public DataSourceResult Process()
    {
         this.Filters();
         var aggregates = this.Aggregates();
         this.Sorts();
         
         var total = this._query.Count();
         this.Paging();
  
         this._request.Page = 1;
         this._request.Filters = null;
         var dataSourceResult = this._query.ToDataSourceResult(this._request);

        return dataSourceResult;
    }
}

And the result controller method will look like

 public ActionResult Virtualization([DataSourceRequest] DataSourceRequest request)
 {
     IQueryable data = _factory.Create(storages => storages.Prices);
     var queryBuilder = new QueryBuilder(data, request);
     
     return Json(queryBuilder.Process());
 }

Thanks for your attention.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s