Using Kendo UI With MVC4, WebAPI, OData And EF

This is a quick guide for using ASP.NET MVC4 WebAPI, OData, and Entity Framework as a remote data source for Kendo UI, as well as performing some operations like filtering on the server side.

(If you just want to copy/paste some JavaScript that works, with no explanation as to why it works, then just skip to the very bottom.)

Kendo’s DataSource is a great way to automatically read data from a remote source. One important thing to keep in mind is that the Kendo DataSource is intended to work with a variety of data sources. WebAPI is just one way to get data to your Kendo widgets. Since Kendo strives to support multiple sources, there are bound to be issues with certain services when they aren’t all 100% compatible. This is the case with WebAPI And OData.

Create a WebAPI project.

I started by using the data model from Microsoft’s MVC Music Store sample project. If you don’t have your own existing MVC4 project to work with, you can use MVC Music Store by downloading the source from http://mvcmusicstore.codeplex.com/

Add Kendo UI Web.

Now lets add Kendo UI. You can download Kendo from http://www.kendoui.com/ however if you have not purchased Kendo, and just want to try it out, you can add it through NuGet (search for “kendouiweb”), or you can just include it from the CDN. See: http://docs.kendoui.com/getting-started/javascript- dependencies#cdn

Using the default WebAPI controller.

For my examples, I will be using a WebAPI controller named “AlbumsController”, which is simply:

public class AlbumsController : ApiController
{
    // GET api/Albums
    public IEnumerable<Album> Get() 
    {
        db.Configuration.ProxyCreationEnabled = false;
        var albums = db.Albums;

        return albums.AsEnumerable();
    }
}

We can test our controller by browsing to “/api/Albums” and seeing that it returns all albums as XML. In my example, the MVC Music Store contains several hundred albums, so this is a large list.

So lets see how we can bind to this in a Kendo DataSource:

The HTML page that I will be using contains a single element in its body:

<input id="albumSearch"/>

I am going to turn this into a Kendo UI AutoComplete widget, and use it to search for albums by their title.  I do this using the JavaScript:

$(document).ready(function() {
    var dataSource = new kendo.data.DataSource({
       transport: {
           read: {
               url: "/api/Albums"
           }
       } 
    });

    $("#albumSearch").kendoAutoComplete({
        dataSource: dataSource,
        dataTextField: "Title",
        minLength: 3
    });
});

My AutoComplete is configured to show the filtered options when there are at least 3 characters entered. This means that the DataSource will not be asked to read the WebAPI URL until we type the 3rd character into the AutoComplete textbox.

Our DataSource is simply set to read from our AlbumsController’s GET method.

Not surprisingly, this works as expected visually:

Now, lets take note of some things that happened here;

1. When I typed the 3rd letter, Kendo made the request to the URL, seen in the dev tools network activity.

2. If I continue to type in the box or clear the input and type something else, the URL is NOT queried again. This is because the filtering is happening client-side (this is configurable. read on!).

3. The result is now JSON. When we manually browsed to that URL, we got XML instead. WebAPI inferred the format from the request header.

4. Kendo actually passed some parameters in our request query string:

api/Albums?filter%5Blogic%5D=and&filter%5Bfilters%5D%5B0%5D%5Bvalue%5D=the&fil
ter%5Bfilters%5D%5B0%5D%5Boperator%5D=startswith&filter%5Bfilters%5D%5B0%5D%5B
field%5D=Title&filter%5Bfilters%5D%5B0%5D%5BignoreCase%5D=true

This translates to:

filter[filters][0][field]=Title
filter[filters][0][ignoreCase]=true
filter[filters][0][operator]=startswith
filter[filters][0][value]=the
filter[logic]=and

However if we look at the actual JSON response, the very first album returned contained: {"Title":"...And Justice For All"} so WebAPI is ignoring these query string parameters. It is NOT applying filtering (but most of the rest of this article will deal with making it do the filtering).

If this is what you want, then hey, great! However, I doubt it is. Lets imagine for a moment that this was a real music store album listing. How many albums do you think they have? A few million maybe? (The new Xbox Music service claims 30 million titles!) Would you REALLY want your server to send ALL of those to EACH client browser? No, I didn’t think so. This default “return them all and filter client side” works great for small lists, but is not practical for a lot of real-world applications.

So what can we do about it?

Add OData support.

We want to let the server handle the filtering for Kendo. We will do this using OData.

OData will let us send query parameters over the query string. For example, adding “?$top=5” to your URL would tell OData to only return the first 5 results. So, lets make sure WebAPI isn’t actually doing this already. In your browser, navigate to your api, and include the $top parameter:

I collapsed some of the Album elements, and you can sure tell it is returning more than the top 2 results.

Now we need to add OData support to WebAPI. The story with WebAPI and OData is a bit confusing. Some limited OData-like support was actually built-in to WebAPI for the preview releases of MVC4. However, this was pulled out for the RTM version. There is still a large number of people out there that believe that WebAPI is out-of-the-box a fully functional OData provider. It isn’t. Instead of building OData support into the RTM release, Microsoft decided to release it in an out-of-band NuGet package.

So in the NuGet console, lets add this package. We also need to modify our GET method within the WebAPI controller to:

Install-Package Microsoft.AspNet.WebApi.OData -Pre

[Queryable]
public IQueryable Get()
{
    db.Configuration.ProxyCreationEnabled = false;

    var albums = db.Albums;

    return albums;
}

We did 2 things here:

1. Added the [Queryable] attribute to the method, which indicates that it will be OData queryable.

2. Changed the method to return an IQueryable instead of an IEnumerable.

Now let’s try that URL with $top=2 again:

Awesome! Well don’t get too excited too quick. There are more bumps in the road! Note the “-Pre” parameter you had to use in NuGet? That is because this is a pre-release version of OData support for MVC4. This means it isn’t 100% complete yet. There are some features of OData that are not supported (yet). Unfortunately, some of these unimplemented features are ones that Kendo wants, so we are going to have to work around some things. But we will get to that…

Let Kendo use OData

Remember those filter parameters that Kendo added to our URL query string before? Yeah, well those aren’t formatted as OData parameters. We need to tell Kendo to use OData, by setting the "type: 'odata'” property on the data source:

var dataSource = new kendo.data.DataSource({
    type: 'odata', // <-- Include OData style params on query string.
    transport: {
        read: {
        url: "/api/Albums"
        }
    }
});

Lets try our AutoComplete now:

Uh oh, this isn't good. WebAPI tells us: "The query parameter '$callback' is not supported."

Look at the parameters Kendo is now sending to our WebAPI controller:

/api/Albums?%24callback=jQuery17207543195712677954_1350180979161&%24inlinecou
nt=allpages&%24format=json&%24filter=startswith(tolower(Title)%2C%27the+%27)

(side-note: "%24" in the query string is an escaped dollar-sign "$", which comes before each OData parameter.)

What is happening here is that it is trying to do a "JSONP" style request, and tell the WebAPI what to use as the callback. Unfortunately, WebAPI OData doesn't support this.

Dealing with the $callback parameter.

So what are we to do? Well, we have 2 options:

1) If you are querying your own data, and don't need to do a cross-domain request, we can just not use jsonp. To do this, we just tell Kendo the dataType for the read operation is "json".

var dataSource = new kendo.data.DataSource({
    type: 'odata', // <-- Include OData style params on query string.

    transport: {
        read: {
            url: "/api/Albums",
            dataType: "json"; // <-- The default was "jsonp".
        }
    }
});

If you re-try the auto complete at this point, the parameters now sent to the server are:

/api/Albums?%24inlinecount=allpages&%24format=json&%24filter=startswith(tolow
er(Title)%2C%27the+%27)

So we got rid of the jsonp callback parameter that was causing trouble.

2) If you do need to use JSONP to do a cross-domain request, then you will need to add support for that parameter to WebAPI, which is beyond the scope of this article. (Sorry! Maybe I will make a follow-up eventually… or MS will finish implementing OData…)

Dealing with the $inlinecount parameter.

If you try your auto complete now with JSON instead of JSONP, you will see a new error:

So now WebAPI OData is telling us "The query parameter '$inlinecount' is not supported." Well, it seems the $inlinecount parameter isn't supported either. Why does Kendo want this? What that parameter does is tell OData to include a count of all items. This comes into effect when setting up paging. For example, if we were doing a Grid and wanted pages of 20, in order to render the grid's paging controls, we would need to know how many pages we could have. The $inlinecount parameter gives us this ability. When that parameter is included, OData is supposed to wrap the result in another object that includes the count, which would look like:

{
    "Results": [
        /* ... normal results json goes in here ... */
    ],
    "Count": 64
}

So if we were doing pages of 20, and had a full OData implementation on the server, we could request the first page by doing:

http://wherever/Albums?$take:20&$inlinecount

Which would return our first 20 items, plus the count (lets assume 64 items) which would let us know that we are displaying 1 through 20 of 64 items, and that we will have 4 pages.

Well, since OData in WebAPI doesn't support this, we need to do something…

Again we have 2 options.

1) Do we really need the $inlinecount? If you are not doing paging in your UI, like in our AutoComplete example, I don't have "pages" of AutoComplete items, so I don't really need $inlinecount. We can remove that parameter from the parameters sent to the server. Kendo will let you edit the entire parameter map before it is sent to the server, so we can just remove the $inlinecount by doing:

transport: {
    read: {
        url: "/api/Albums", // <-- Get data from here
        dataType: "json" // <-- The default was "jsonp"
    },
    parameterMap: function (options, operation) {
        var paramMap = kendo.data.transports.odata.parameterMap(options);
        delete paramMap.$inlinecount; // <-- remove inlinecount parameter
        delete paramMap.$format; // <-- remove format parameter

        return paramMap;
    }
}

(Note that I am also removing $format here. That is another OData parameter that WebAPI OData doesn't support yet, but I wanted to demonstrate a different error and had to include that in the code.)

Now let's try the auto complete again:

Yikes! What is that JavaScript error!? Don't panic, we can fix this! Kendo doesn't really know that you removed the $inlinecount parameter manually. It still expects the results to be "wrapped" in that JSON object with .Results and .Count properties. We need to do a little extra work to override Kendo's default behavior, and skip unpacking the result and count by changing the "schema" property of the data source:

var dataSource = new kendo.data.DataSource({
    type: 'odata', // <-- Include OData style params on query string.
    transport: {
        read: {
            url: "/api/Albums", // <-- Get data from here.
            dataType: "json" // <-- The default was "jsonp".
        },
        parameterMap: function (options, type) {
            var paramMap = kendo.data.transports.odata.parameterMap(options);
            delete paramMap.$inlinecount; // <-- remove inlinecount parameter
            delete paramMap.$format; // <-- remove format parameter

            return paramMap;

        }
    },
    schema: {
        data: function (data) {
            return data; // <-- The result is just the data, it doesn't need to be unpacked.
        },
        total: function (data) {
            return data.length; // <-- The total items count is the data length, there is no .Count to unpack.

        }
    }
});

So let's try the auto complete again and see the result:

Notice that our JSON returned from the server only contains 2 results; the same 2 that are displayed in the auto complete drop down! We are now filtering server-side and showing the results!

Stop Client-Side Filtering

Ready to take on the world with your new found abilities? Well, not so fast, slick… Clear out your AutoComplete text box and try something else without refreshing the page. For example, search for "the" then clear that and search for "best". Notice a problem? Remember back at the beginning when we were pulling the entire data set, and Kendo was smart enough to do the filtering client side, and not re-hit the server? Yeah, well, it is still doing that. Now it is client-side filtering our server-side filtered results, instead of re-hitting the server for new filtered results. We need to explicitly tell Kendo that we are doing filtering server-side, with the data source's serverFiltering and serverPaging properties:

var dataSource = new kendo.data.DataSource({
    serverFiltering: true, // <-- Do filtering server-side.
    serverPaging: true, // <-- Do paging server-side.
    ...

Now Kendo will re-query the server whenever it needs new filtering or paging!

Dealing with the $format parameter.

You might have noticed earlier when I started removing the $inlinecount parameter, I also removed $format. If you don't remove it, then WebAPI OData will error on that too. It would normally be used to specify the format, for example XML or JSON. However it is already JSON without the $format parameter, and that is fine. Kendo likes JSON anyway, so we don’t really need this parameter at all.

Final thoughts. Is there a better way?

If you are keeping score, we covered 3 OData parameters that Kendo uses by default that WebAPI OData's implementation doesn't yet support. Is there a better way to handle all this? Well, that, like so many things, its situational. If you are starting a completely new project, and just want a way to serve data to a Kendo app, then WCF Data Services is actually much more capable and comes with a more complete OData implementation. (disclaimer: at the time of this writing, I haven't actually tried this against WCF Data Services to see if it supports $callback, $inlinecount, and $format).

Also, if you do some searching, there are write-ups out on the web on adding support for all 3 of these OData parameters to WebAPI; so if you are feeling adventurous, you can try adding them all yourself and removing the additional Kendo DataSource configuration that I had to do to work around them. In fact, if you want real paging to work, you will probably have to at least add support for $inlinecount to WebAPI.

Personally, I really hope there are enough people out there that want to use OData with WebAPI that MS will finish the implementation, and include these 3 parameters. However, until then, hopefully this post got you the information you needed to get started with KendoUI + WebAPI + OData.

Oh, and my final JavaScript from this example, all put together, is:

$(document).ready(function() {

    var dataSource = new kendo.data.DataSource({
        serverFiltering: true, // <-- Do filtering server-side
        serverPaging: true, // <-- Do paging server-side
        type: 'odata', // <-- Include OData style params on query string.
        transport: {
            read: {
                url: "/api/Albums", // <-- Get data from here
                dataType: "json" // <-- The default was "jsonp"
            },
            parameterMap: function (options, type) {
                var paramMap = kendo.data.transports.odata.parameterMap(options);

                delete paramMap.$inlinecount; // <-- remove inlinecount parameter.
                delete paramMap.$format; // <-- remove format parameter.

                return paramMap;

            }
        },
        schema: {
            data: function (data) {
                return data; // <-- The result is just the data, it doesn't need to be unpacked.
            },
            total: function (data) {
                return data.length; // <-- The total items count is the data length, there is no .Count to unpack.

            }

        }
    });

    $("#albumSearch").kendoAutoComplete({
        dataSource: dataSource,
        dataTextField: "Title",
        minLength: 3
    });
});