The Kendo UI grid is a snazzy little thing:

image

The grid works natively with OData, but the examples from the Kendo team are of OData services driven by WCF back ends.  I wanted to get the grid working with Web API and minimal coding to implement server-side sorting, filtering, and paging.

Until earlier this week, if you wanted to do that, you were on your own.  There was a partial OData implementation in Web API via a NuGet package, but it didn’t support $inlinecount, which is what the grid needs to know how many items there are total (e.g., the $inlinecount for the grid above is 830).

A more fully supported Web API OData implementation was rolled out this week and is available via NuGet.  Here are the steps to get it working with a Kendo UI grid.

Server side

Open your existing Web API project and install the Web API OData NuGet update:

image

Decide what data you want to send to the browser and model it.  I’ve got a Cat entity and a Dog entity:

namespace KendoGridWebApiOdata.Models
{
    public class Cat
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Color { get; set; }
    }
}
namespace KendoGridWebApiOdata.Models
{
    public class Dog
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Color { get; set; }
    }
}

Update your WebApiConfig.Register() method (in the App_Start folder):

using System.Web.Http;
using System.Web.Http.OData.Builder;
using KendoGridWebApiOdata.Models;

namespace KendoGridWebApiOdata.App_Start
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            var modelBuilder = new ODataConventionModelBuilder();
            modelBuilder.EntitySet<Cat>("Cats");
            modelBuilder.EntitySet<Dog>("Dogs");
            var model = modelBuilder.GetEdmModel();

            config.Routes.MapODataRoute(
                routeName: "OData",
                routePrefix: "api",
                model: model
                );

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
                );

            // Uncomment the following line of code to enable query support for actions with an IQueryable or IQueryable<T> return type.
            // To avoid processing unexpected or malicious queries, use the validation settings on QueryableAttribute to validate incoming queries.
            // For more information, visit http://go.microsoft.com/fwlink/?LinkId=279712.
            config.EnableQuerySupport();

            // To disable tracing in your application, please comment out or remove the following line of code
            // For more information, refer to: http://www.asp.net/web-api
            config.EnableSystemDiagnosticsTracing();
        }
    }
}

You’ll define the OData models you want to expose and set up special OData routes for them.  You can add multiple EntitySets to the model builder as needed for your OData endpoints.  Here, I’m adding Cat and Dog to the model builder.

The MapODataRoute uses the model builder values to create an OData route.  Note this is not exactly a Web API route, but you can use the same /api/{controller} convention if you like (or /odata/{controller} if you prefer) by setting the routePrefix to whatever you like. 

I tried toggling config.EnableQuerySupport on and off, and it worked in both cases, so I don’t know if that matters any more after the Web API OData update.

For more details about what is going on here, check out Youssef Moussaoui’s blog post.

Finally, you’ll need a controller, but this isn’t a Web API controller, it’s an ODataContoller.  That’s kinda close, and it inherits from the Web API controller, but it’s specifically designed for the OData calls you just set up in routing.

using System.Linq;
using System.Web.Http;
using System.Web.Http.OData;
using FizzWare.NBuilder;
using KendoGridWebApiOdata.Models;

namespace KendoGridWebApiOdata.Controllers
{
    public class CatsController : ODataController
    {
        [Queryable]
        public IQueryable<Cat> Get()
        {
            return BuildTestData();
        }

        private IQueryable<Cat> BuildTestData()
        {
            return Builder<Cat>
                .CreateListOfSize(100)
                .Build()
                .AsQueryable();
        }
    }
}

Here, I’ve got a CatsController inheriting from the ODataController.  I also have a [Queryable] attribute over the Get() action method.  This is another piece you supposedly need, and this was used in the older version of the Web API OData NuGet package, but I found my grid worked whether the attribute was on there or not.  You will likely want to leave it as a place to lock down incoming requests, since this is where you can disallow certain query parameters, limit records returned, etc.

The BuildTestData() method is using NBuilder to crank out 100 Cat entities and pretend like it’s queryable.

Test it out

That should do it for the server side.  Let’s test the route setup and see if we get data back.  I like using the Chrome plugin Advanced REST Client, but since we’re testing GET requests, you can also just type the URL in your browser.

For the URL /api/Cats, the JSON coming back looks like:

{
"odata.metadata": "http://localhost:56126/api/$metadata#Cats",
"value": [
{
"Id": 1,
"Name": "Name1",
"Color": "Color1"
},
{
"Id": 2,
"Name": "Name2",
"Color": "Color2"
},
...]}

To add some OData parameters to the URL, let’s try /api/Cats?$inlinecount=allpages.  Now the JSON has “odata.count” for the $inlinecount value:

{
"odata.metadata": "http://localhost:56126/api/$metadata#Cats",
"odata.count": "100",
"value": [
{
"Id": 1,
"Name": "Name1",
"Color": "Color1"
},
{
"Id": 2,
"Name": "Name2",
"Color": "Color2"
},
...]}

See how the records are coming across in the “value” array?  We’ll need that and the “odata.count” to help the grid know how to parse this JSON.

Client side

The HTML for the grid is minimal:

<div id="grid"></div>

And the JavaScript isn’t bad:

$(function () {
    var dataSource = new kendo.data.DataSource({
        type: "odata",
        transport: {
            read: {
                url: "/api/Cats",
                dataType: "json"
            },
        },
        schema: {
            data: function (data) {
                return data["value"];
            },
            total: function (data) {
                return data["odata.count"];
            },
            model: {
                fields: {
                    Id: { type: "number" },
                    Name: { type: "string" },
                    Color: { type: "string" }
                }
            }
        },
        pageSize: 10,
        serverPaging: true,
        serverFiltering: true,
        serverSorting: true
    });

    $("#grid").kendoGrid({
        dataSource: dataSource,
        filterable: true,
        sortable: true,
        pageable: true,
        columns: [
            { field: "Id" },
            { field: "Name" },
            { field: "Color" }
        ]
    });
});

In the dataSource variable, we’re setting the type as “odata”.  Seems like that would be enough, but you have to also set the read transport’s dataType to “json” or your request will go to the server like /api/Cats?$callback=jQuery183021786115039139986_1361332885733&$inlinecount=allpages &$format=json&$top=10.  This is a JSONP request.  We want the $callback and $format parameters to go away, and setting the dataType to “json” does that and sets the request’s Accept headers to “application/json”.

There is also some extra stuff going on in the schema section.  Normally, this is where you tell Kendo UI what your data types are so it can sort and filter correctly.  Here, we’ve added data and total to pull the values out of the JSON response so the grid needs can render correctly.  The data function is returning the “value” from the JSON we saw above, and the total function is returning the “odata.count”.

The rest of the dataSource properties are to force things to be done server side and to set the page size.  The remainder of the JavaScript is firing up the grid with this dataSource and setting properties of the grid.

When you run this, you get a Kendo UI grid working with server-side OData.  In this case, only 10 records are sent per request instead of the 100 Cat records we created on the server.  The OData handling works with sorting, filtering, and paging on the grid. 

If you get it running on your machine, check the XHRs to see the OData query string parameters getting added to the request and the response coming back with just the records requested.

Other nifty things

You can hit a URL to get the schemas of the entities you can query with /api/$metadata.  You’ll get back some XML like this:

<?xml version="1.0" encoding="utf-8" ?>
<edmx:Edmx Version="1.0">
 <edmx:DataServices m:DataServiceVersion="3.0" m:MaxDataServiceVersion="3.0">
 <Schema Namespace="KendoGridWebApiOdata.Models">
 <EntityType Name="Cat">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="Edm.Int32" Nullable="false" />
<Property Name="Name" Type="Edm.String" />
<Property Name="Color" Type="Edm.String" />
 </EntityType>
 <EntityType Name="Dog">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="Edm.Int32" Nullable="false" />
<Property Name="Name" Type="Edm.String" />
<Property Name="Color" Type="Edm.String" />
 </EntityType>
 </Schema>
<Schema Namespace="Default">
 <EntityContainer Name="Container" m:IsDefaultEntityContainer="true">
<EntitySet Name="Cats" EntityType="KendoGridWebApiOdata.Models.Cat" />
<EntitySet Name="Dogs" EntityType="KendoGridWebApiOdata.Models.Dog" />
 </EntityContainer>
</Schema>
 </edmx:DataServices>
</edmx:Edmx>

If you are worried about exposing your data online, read up on OData Security Guidance.

Limitations

URLs are case sensitive.  If you browse to /api/Cats, you get data.  If you browse to /api/cats (lower case “C”), you get a 406 error.

Kendo UI grid allows sorting by more than one column, but the current Web API OData implementation doesn’t seem to support it.

Download

You can download the code for this project from GitHub.