Kendo Grid sorting and filtering

Kendo Grid Default Sort Order

The kendo grid can ignore the ordering on a datasource if the read action gets an Iquerable or Ienumerable. This is because the call to SQL is not made until the kendo code performs its actions that trigger the call. As a result the order by included on the original can be lost.
To specify a default sort then add the following to the Datasource

   .DataSource(dataSource => dataSource
        .Ajax()
        .PageSize(100)
        .Sort(x => x.Add("Order"))

Where the "order" is the field name to sort on.
Use .Descending() after the add to change the direction.


Kendo Grid Default Filtering

To specify any default filterings on the grid then its as easy as …

.DataSource(dataSource => dataSource
    .Ajax()
    .Sort(x => x.Add("Reference").Descending())
    .Filter(filters=>{
        filters.Add(p => p.StatusId).IsEqualTo(1);
    })  

Kendo Grid Client Filtering

In JavaScript the filtering can be controller with

grid = $("#grid").data("kendoGrid");  

to setup a filters (will lose any existing)

grid.dataSource.filter({ field: "CategoryID", operator: "eq", value: parseInt(value) });  

to clear all filter

grid.dataSource.filter({});  

http://docs.telerik.com/kendo-ui/api/javascript/data/datasource#methods-filter


Kendo Grid Server side Sorting and Filtering

The controller gets the data from the database or wherever. If the data returned is IQuerable then the ToDataSourceResult will apply its stuff in order to push the filtering as far downstream as possible ie at database SQL level
In some cases the data is not simple enough that it is a query to the database. Some calculations may need to be performed so in that cases then all the data is pulled from the database and performs necessary calculations, therefore is Ienumarable rather than a Iqueryable list. In this case ToDataSourceResult is just working on the list in memory.

Since that means everything has to be pulled from database then in attempt to speed that operation up I checked if it is possible to get the list of data required first before doing the data generated.

Commonly the data is retrieved and the sort and filtering is applied in the controller with an ToDataSourceResult
example:

public ActionResult GridRead([DataSourceRequest] DataSourceRequest request) {  
    IQueryable<MyModel> list = GetAllTheData();
    return Json(list.ToDataSourceResult(request));
}

So the plan was to get the necessary list of items from the database using the filtering from the grid then pass that list of Ids to another function that will get the desired calculated data. Thus reducing the amount of data pulled from the database.
Testing code used as follows

Controller had  
DataSourceResult dsr = _service.ReadTest(projid).ToDataSourceResult(request);  
IEnumerable taglist = dsr.Data;  
IEnumerable<TagId> lt = taglist.Cast<TagId>();  
List<TagId> lt2 = lt.ToList();  
return Json(_service.Read(projid, lt2).ToDataSourceResult(request));

where  
public class TagId {  
    public int Id { get; set; }
}
and  
public IQueryable<TagId> ReadTest(int projectId) {  
    IQueryable<TagId> list = _uow.Tags.Where(x => x.ProjectId == projectId).Select(x => new TagId { Id = x.Id });
    return list;
}
and  
public IEnumerable<TagAttributeEditorGridViewModel> Read(int projectid, List<TagId> idList) {  
    List<int> ids = idList.Select(x => x.Id ).ToList();
    IQueryable<Tag> list = _uow.Tags.Include("TagData").Where(x => x.ProjectId == projectid && ids.Contains(x.Id) );
    //do calcs etc on the list before return

This meant the controller applied filtering to get a list of tag id and then past that smaller set to the read() that could filter a smaller SQL set and just do the property calculations for that set.

However fundamentally flawed as filtering from the kendo grid could be on a calculated field so can never be done at SQL level. Always need to pull the full set. (Unless you want to disable the filtering on the calc fields in the grid)
Caching is required instead.


Above referring to version
Telerik Kendo UI Professional Q1 2015
Telerik UI for ASP.NET MVC Q1 2015


comments powered by Disqus