nhibernate - Kendo grid server side grouping

Question

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.

Answer 1

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.

The Solution

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

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.

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.

The Server Magic

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;
    }

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.

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.

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.

The Client Magic

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"], {
});

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.

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.

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);

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>

The End Result

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.

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!