Ask Kendo UI – Server Side Sorting With WebAPI

In the past few months, I’ve learned a lot about WebAPI and using Kendo UI with ASP.NET by creating the HTML5 Development For ASP.NET Developers series. While the company behind Kendo UI has a distinguished .NET pedigree, Kendo UI itself is completely server agnostic. That means it doesn’t lean one way or the other in terms of catering to a specific server language. It’s all about HTML5, and that’s the way it should be.

When I did Hello Kendo UI Part 1, I got a few comments asking me if I could show some additional server actions, like sorting. Actually, the exact comment was

And then there was another vote for the same thing.

So, naturally, I said…

In the interest of full disclosure, I just want you all to know that this one was indeed as the commenter suggested it might be in terms of not being necessarily easy to decipher.  The end solution is by no means complicated, but the trick is knowing your way around WebAPI. There was a lot of trial and error, a bit of crying and a small amount of swearing, but today I would like to publicly declare victory.

WebAPI Idiosyncrasies

While I have a very good understanding of Kendo UI, my expertise in WebAPI is not nearly as matched. It’s a newer technology, and it’s got a few things in it that can keep you guessing. Fortunately for me, I was able to enlist of the help of the Jonathan Creamer (AKA “The Prodigy”) of AppendTo  His knowledge of WebAPI (and many other things) far exceeds my own and he was a valuable source of direction when trying to navigate the sometimes murky waters.

Source

You can download the complete source for this article here.

Turn On Sorting

I will be using the last Hello Kendo UI example and applying sorting to the same project that already has paging and editing in it. There are only 3 records on a page, but we’ll pretend we have a bunch.

The first order of business is for you to open up the Default.aspx page and in the Grid initialization you need to turn on sorting.

selectable: true,
...

sortable: true,

...
change: function (e) {

With that change, the grid is now sortable. However it’s happening client side. Of course this is fine when your grid hold 9 records but we’re trying to prove a point here. We need to get this on the server. To do that, set the serverSorting property on the DataSource to true

// do paging on the server
serverPaging: true,

...

// do sorting on the server
serverSorting: true

...

}),

Now you can sort the columns, and if you inspect the network request in the developer tools you will see that a sort parameter is being sent across.

WebAPI Parameter Binding

One of the things that Jonathan had me change was to use the Default Parameter Binding in WebAPI. It turns out that you can pass any parameter into the Get request in the Controller Method and as long as it’s present, it will be automatically bound. For instance, we could now get the take and skip paging parameters directly. We do have to provide defaults though. If no values are passed, WebAPI will bark about not getting the parameter its expecting

public Models.Response Get(int take = 10, int skip = 0)
{
    ...
}

That’s a whole lot nicer isn’t it!?!

Now you would think that you could reference the sort parameter the same way. However, this is not the case. For starters, lets look at the structure of the object being passed.

sort[0][field]:LastName
sort[0][dir]:asc

This object is actually rather complex. It’s an array of objects that contain a field and dir property. Right now, WebAPI sees both of these sort parameters as separate parameters that have a key of sort[0][field] and sort[0][dir]. That’s not exactly what we want at all.

Speaking The Same Language

What we need to do here is get Kendo UI and WebAPI on the same page. This is all about serialization. JSON is going to be the middle ground here. Kendo UI and WebAPI both read and speak JSON fluently. We essentially have two options now. We could convert just the sort objects to JSON, or we could convert the entire request to JSON and expect a single Request object in the Get controller method.

I opted for the later as it’s a bit less verbose and I think makes more sense when you go back to debug or some other pour soul gets handed your code.

Framework Methods

Kendo UI has a whole plethora of framework methods. One of them is stringify. This is a pollyfill for the JSON2 object. It takes an object and converts it to JSON. Most browsers support the JSON2 stringify natively, but older ones don’t. By using the kendo.stringify method, you will be safe in knowing that Kendo will take care of things if your browser can’t serialize to JSON.

HTML5 is about making sure things work across browsers. Kendo UI fills in the gaps (like missing JSON serialization support) for you and automatically provides fallbacks when functionality is missing. See how Kendo UI makes your life so much easier?!?

In the parameterMap, you need to convert the parameters to JSON using kendo.stringify. You can just convert the whole options object.

if (operation === "read") {
    return kendo.stringify(options);
}

We now have a different issue. We can’t send parameters like this to WebAPI in a GET. We need to do this in a POST and send the parameters as part of the payload. To tell Kendo UI to do this is quite easy. Just set the type on the read operation to POST.

read: {
    url: "api/employees",
    type: "POST"
}

In the controller, you now need to have the Get method respond to a POST. By Default, WebAPI is routing it to a GET because it’s assuming that since we called it Get, we would want to do a GET. Makes sense right?

To get around this, you need to make some routing changes. Right now, you have one simple route in the Global.asax application start event. You are again at a cross roads. On the one hand, you can create a route specific for this scenario the same way you created the default route. By now though, you can see that creating a new route every time you have something other than a super simple set of CRUD operations is not going to scale very well. I wasn’t satisfied with this answer so I did some soul searching. There has to be a better way.

Attribute Based Routing

It turns out that I’m not the only one who had this concern. Tim McCall created an Attribute Based Routing framework for MVC and then ported it for WebAPI as well. This allows you to decorate your methods with what routes you want them available at. This is frankly brilliant, and I would love to see this baked into the framework.

For now, head out to NuGet and get the AttributeRouting package. Make sure you get the WebAPI one, NOT the MVC one. They are not interchangeable.

Once you have installed this package, you can get rid of that nasty route table in the Global.asax file. Open the EmployeesController and add a reference to the AttributeRouting library.

using AttributeRouting.Web.Http;

 

If this namespace isn’t available for you, then you probably installed the MVC version instead of the WebAPI version.

Now decorate your methods as shown:

[POST("api/employees")]
public Models.Response Get(){
    ...
}

[POST("api/employees/{id}")]
public HTTPResponseMessage Post() {
    ...
}

[DELETE("api/employees/{id}")]
public HTTPResponseMessage Delete() {
    ...
}

The Get will respond to a POST and the update method will only respond to a POST when an ID is passed in on the URI (i.e. http://localhost/api/employees/2).

If you wanted to make this id parameter optional like it was before, you can simply put a “?” in front of it - POST(“api/employees/{?id}”). For a completely syntactical reference, have a look at this article.

Back To Parameter Binding

If you remember, in the parameterMap method you serialized the entire option object to JSON. This means that in the Get, you are now expecting a single object. You need to create an object that WebAPI can use to map the request parameters to. In the Models folder, create a Request class. Give it the following structure.

namespace hello_kendo_ui.Models {
    public class Request {
        public int take { get; set; }
        public int skip { get; set; }
        public int page { get; set; }
        public List<Sort> sort { get; set; }
    }

    public class Sort {
        public string field { get; set; }
        public string dir { get; set; }
    }
}

OK, so it’s actually two classes in the same file. You can now expect one Request parameter in the Get method and all of the parameters coming from the DataSource (i.e. take, skip, page, sort) will be available off of this object. The take and skip parameters are gone and the LINQ query now references the request object. I also moved the Take and Skip into the body of the LINQ query for reasons I will explain shortly.

[POST("api/employees")]
public Models.Response Get(Models.Request request) {
    // get all of the records from the employees table in the
    // northwind database.  return them in a collection of user
    // defined model objects for easy serialization. skip and then
    // take the appropriate number of records for paging.
    var employees = (from e in _context.Employees
                    .Skip(request.skip)
                    .Take(request.take)
                    select new Models.Employee(e)).ToArray();

    // returns the generic response object which will contain the 
    // employees array and the total count
    return new Models.Response(employees, _context.Employees.Count());
}

At this point, you can test the application and you will see that it’s returning an empty dataset and a count of 9? What happened?

As it turns out, since you are sending a JSON payload to WebAPI, you need to specify that you are doing that. You need to set the contentType in the header of the AJAX request. That sounds complicated, but since at it’s core the Kendo UI DataSource calls jQuery.ajax(), you can pass virtually any valid $.ajax parameter in to the transport methods. Simply set the contentType to application/json.

read: {
    url: "api/employees",
    type: "POST",
    contentType: "application/json"
},

This will give you data back. If you click on a column heading and break into the EmployeesController Get method, you will see that the sort property is now populated! You are well on your way at this point.

Dynamic Sorting With LINQ

Since you don’t know what the sort will be at runtime, you need to be able to pass in a dynamic OrderBy clause to in your LINQ query. To do this, you are going to need the Dynamic LINQ library that comes with the Visual Studio 2008 samples.

It’s just a class that you add to your project. It extends off the System.Linq namespace so no worries on where you put it in your project.

Once you have added it, you can import the System.Linq.Dynamic namespace in the EmployeesController.

using System.Linq.Dynamic;

This will allow you to pass a string to the OrderBy method. A string like “LastName asc”. It’s a standard SQL order by syntax. You do need to translate this out of the sort object and into a single string. Right now you are only passing one sort value in the 0 position of the sort object, but should you turn on multiple sorting, you will be passing 1 to many sort objects. You might do this any number of ways, but I simply iterated over the list of incoming sort objects and composed a string using the string.join method.

// compose the order by for sorting
string order = "EmployeeID";

// order the results
if (request.sort != null && request.sort.Count > 0) {
    List<string> sorts = new List<string>();
    request.sort.ForEach(x => {
        sorts.Add(string.Format("{0} {1}", x.field, x.dir));
    });

    order = string.Join(",", sorts.ToArray());
}

Notice that I gave the sort a default value. This is because the Dynamic LINQ query won’t respect null or empty string as a valid OrderBy. Also, check for a null sort object and make sure that it has a length. Before you sort, Kendo UI sends no value. On the first click, it sorts Ascending. On the second click, it sorts Descending. On the third click it turns sorting completely off, but passes a zero length object.

The Order Of The Order

You have an interesting scenario here if you stop to think about it. You are paging and sorting. That means that you need to do both of these, but in what order? The grid will display the current page of data. This means that when a user sorts, it should sort only the current page of data. Consequently, you need to page and then sort.

Fortunately for you, this is quite easy. This is also the reason that I earlier moved the Skip and Take into the body of the LINQ query right off of the _context.Empolyees object. Just add the OrderBy clause after the Skip and Take. This will get the right page of data, and then sort it. This is what the user expects would happen in the grid. If you sort and then page, you will be providing an unexpected user experience.

// get all of the records from the employees table in the
// northwind database.  return them in a collection of user
// defined model objects for easy serialization. skip and then
// take the appropriate number of records for paging. Then order
// that dataset with the dynamic linq query library
var employees = (from e in _context.Employees
                 .Skip(request.skip)
                 .Take(request.take)
                 .OrderBy(order.ToString())
                 select new Models.Employee(e)).ToArray();

That’s it! You’ve now got server sorting and paging in your grid.  Lets kick it up a notch and set the sorting to multiple on the DataSource.

editable: "inline",
selectable: true,

...

sortable: {
    mode: "multiple"
},

...

Thanks to the fact that you have already accounted for the fact that the sort object is a collection, you don’t need to make any additional changes.

Playing Nicely

As you can see, Kendo UI plays really nicely with WebAPI. You get the utility methods you need - like stringify - and the DataSource is super flexible in what it will let you do to the outgoing request.

Hopefully between this and the previous HTML5 For ASP.NET Developer articles, you have all the info you need to wire Kendo UI up to ASP.NET. If not, I would love to hear what would be helpful for you to see in addition to what’s already been provided.

As always, if you haven’t downloaded Kendo UI yet, make sure you do and see how HTML5 goes from frustrating to fun!

Source

Grab the complete project for this article here.