code comments edit

One of the most attractive features of the Kendo Grid is that you can use it in BatchEdit Mode. This means that you can make all the changes you like to the data currently being displayed and then save them with a button click, all at the same time. This saves you several trips to the server as it all just happens in one HTTP request (per type of operation) that has all the grid items that were just added/deleted/modified.

This does not mean you shouldn’t strive to make sure that the code that runs to support these actions is as performant as possible, though, as it is possible that in these methods you are doing a lot more work than needed. This is particularly true if you are using an ORM, like Entity Framework, that abstracts database interaction so much that sometimes it’s difficult to tell what SQL queries were made and why.

Let’s take a look at a simple Grid in Batch Edit Mode, the implementation of the Controller that supports it, and how it could be significantly improved for performance.

I will be using MVC MiniProfiler and MiniProfiler.EF6 to monitor all the SQL queries that Entity Framework sends to the database to determine if there is any room for improvement.

Ground Work

The goal is to create a Grid of “Cars”, each car will have a “Category” that will be displayed as a Kendo ComboBox so the the user can either pick a pre-existing Category or simply type a new category to be added to the database when creating or editing a car in the grid. This means that we will need a Car class, a Category class, and for design principles, a CarViewModel class that will be used for the grid.

public class Car
{   
    public int Id { get; set; }
    [Required]
    [StringLength(50)]
    public string Name { get; set; }
    public int CategoryId { get; set; }
    public virtual Category Category { get; set; }

}

public class Category {
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual List<Car> Cars { get; set; }    
}

public class CarViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    [UIHint("ClientCategory")]
    [Required]
    public string Category
    {
        get;
        set;
    }
}

The UIHint on the Category property of the CarViewModel tells the MVC framework where to look for the EditorTemplate it will use for the creation/updating of the Category of our cars.

This gives us all the pieces we need to start working on creating our Grid and the Controller to support it.

The Grid and EditorTemplate

For this demo, I will use the UI For ASP.NET MVC Kendo Wrappers to generate a simple Kendo Grid of “CarViewModel” but this would be very similar using the JavaScript API for Kendo UI.

//Index.cshtml
@(Html.Kendo().Grid<TelerikMvcApp4.Controllers.CarViewModel>()
.Name("grid")
.Columns(columns =>
{
    columns.Bound(p => p.Id);
    columns.Bound(p => p.Name);
    columns.Bound(p => p.Category);
    columns.Command(command => { command.Destroy(); }).Width(172);
})
.ToolBar(toolbar =>
{
    toolbar.Create();
    toolbar.Save();
})
.Editable(e => e.Mode(GridEditMode.InCell))
.Pageable()
.Sortable()
.Scrollable(e => e.Height(340))
.DataSource(dataSource => dataSource
    .Ajax()
    .Sort(sort => sort.Add("Id").Descending())
    .Batch(true)
    .Model(model =>
    {
        model.Id(p => p.Id);
        model.Field(p => p.Id).Editable(false);
        model.Field(p => p.Category);
    })
    .PageSize(10)
    .Create(c => c.Action("Cars_Create", "Home"))
    .Read(read => read.Action("Cars_Read", "Home").Type(HttpVerbs.Get))
    .Update(update => update.Action("Cars_Update", "Home"))
    .Destroy(update => update.Action("Cars_Destroy", "Home"))
)
)
//ClientCategory.cshtml
@(Html.Kendo().ComboBoxFor(m => m)
    .Placeholder("Select a Category")
    .HighlightFirst(false)
    .DataSource(ds =>
    {
        ds.Read(r => r.Action("Categories", "Home"));
    })
)

Initial Controller Set-Up

For the sake of brevity, I am going to limit this post to examining the performance of the Update Action of the Controller but the same principles apply to the Create Action of the Controller.

[HttpPost]
public ActionResult Cars_Update([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")]IEnumerable<CarViewModel> cars)
{
    foreach (var car in cars)
    {
        if (car != null && ModelState.IsValid)
        {
            var dbCar = db.Cars.Where(c => c.Id == car.Id).SingleOrDefault();
            if (dbCar != null)
            {                        
                var category = db.Categories.Where(cat => cat.Name == car.Category).SingleOrDefault();
                if (category == null)
                {
                    category = new Category { Name = car.Category };
                }
                dbCar.Name = car.Name;
                dbCar.Category = category;
            }

        }
    }
    db.SaveChanges();
    return Json(cars.ToDataSourceResult(request, ModelState));
}

Now when I make a few edits on the page and hit the Save Changes button all of my changes are synced, but when I look at the output from MiniProfiler I see that a lot of SQL queries (up to 40 in this sample!!) were made even though I only have ten items per page on my Grid.

naive implementation

What is wrong with this

At first glance there doesn’t seem to be anything evidently wrong with this method. It’s a simple iteration over each car being updated, with a straight-forward update to the values on each car, followed by a standard SaveChanges call on the EntityFramework DbContext.

The problem, though, is that before updating each Car record on the iteration, it is being retrieved. That’s already a possibility of 10 SQL Select queries before even getting to updating any of the records. The same is happening with the related Category record, which also needs to be retrieved by using the Category (string) of the incoming CarViewModel. The fact that these operations happen for each car (because this is a Grid in BatchEdit Mode) is what aggravates this easy-to-overlook problem to a much larger scale.

How this can be fixed

Luckily for us, EntityFramework allows for entities to be Attached to the DbContext even though they were not retrieved from the database initially. This means we can write code that will tell Entity Framework to edit a record without having to manually retrieve it first.

That fixes the problem of retrieving the Car record before updating its values. But, the Category record needs to be handled differently, as we are not updating it at all. We are simply assigning a CategoryId to the Car record. In the case where the Category does not exist we are creating it first and then assigning its Id it to the CategoryId of the car record being updated.

To improve this we can change our implementation a little bit so that the Grid actually sends either a CategoryId (if the Category already exists) or a CategoryId with a null value and a string value for CategoryName so that it gets created before updating the car. We will need to change the ViewModel so that the editor applies now to the CategoryName. The CategoryId gets set with javascript (to either a numeric value or null) on the Change Event of our Kendo ComboBox.

public class CarViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? CategoryId { get; set; }
    [UIHint("ClientCategory")]
    public string CategoryName { get; set; }
}

//ClientCategory.cshtml
@(Html.Kendo().ComboBoxFor(m => m)
.Placeholder("Select a Category")
.DataTextField("Name")
.DataValueField("Name")
.DataSource(ds =>
{
    ds.Read(r => r.Action("Categories", "Home"));
})
.Events(d => d.Change("selectionChanged"))
)
function selectionChanged(e) {
    var tr = this.element.closest("tr");
    var cbBoxItem = this.dataItem();
    var grid = $("#grid").data("kendoGrid");
    var item = grid.dataItem(tr);
    item.CategoryId = cbBoxItem ? cbBoxItem.Id : null;
}

With these changes in place the Controller looks not too differently but is certainly much more performant when put to the test.

[HttpPost]
public ActionResult Cars_Update([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")]IEnumerable<CarViewModel> cars)
{
    foreach (var car in cars)
    {
        if (car != null && ModelState.IsValid)
        {
            var dbCar = new Car { Id = car.Id, Name = car.Name };
            if (car.CategoryId.HasValue)
            {
                dbCar.CategoryId = car.CategoryId.Value;
            }
            else
            {
                var category = new Category { Name = car.CategoryName };
                db.Entry(category).State = EntityState.Added;
                dbCar.Category = category;
            }

            db.Cars.Attach(dbCar);
            db.Entry(dbCar).State = EntityState.Modified;

        }
    }
    await db.SaveChanges();
    return Json(cars.ToDataSourceResult(request, ModelState));
}

performant implementation

Ahh. Much better :)

There is still room for improvement, as these operations could be done using SQL Bulk Insert/Bulk Update but Entity Framework does not support this out of the box and the implementation on this demo would require a considerable re-write. For now we have dramatically reduced the number of SQL queries to the database without changing too much of our straight-forward code.

You can find the full source-code for the original application and the improved one in my GitHub.

code comments edit

The DataSource is one of the most powerful framework components of Kendo UI. It is the best way to get data in and out of the many widgets that the Kendo suite offers. The Kendo team has done a great job at explaining how to get started using it, deep diving into it, and integrating it with Web Api and EntityFramework, Backbone JS, Breeze JS, among others.

On this blog post I will explain how you can use a Windows Azure Mobile Services (ZUMO) back-end in conjunction with the Kendo DataSource.

What is this ZUMO thing anyway?

Windows Azure Mobile Services is a a Backend as a service, similar to Facebook’s Parse or Telerik’s Everlive. It allows you to quickly build a JSON based REST API with easy to configure social network authentication, cloud code editing, full control of HTTP request-response interactions, and custom API endpoints. On top of that Microsoft provides SDKs to interact with your ZUMO REST API in C# (.NET and Xamarin apps), Java (Android), Objective-C (iOS) as well as a newly released JavaScript SDK for Web apps

The JavaScript SDK is what we can use to enable our Kendo DataSource to work with our ZUMO back-end.

OK. Let’s get started.

Evidently, the first thing that needs to be done is to go to the Windows Azure Portal and create a new Mobile Service. The Wizard will ask if you want to use an existing Azure Database or if you want to create a new one. After this you can create a new table. Azure will proceed to create the SQL table and the RESTful JSON endpoint for it.

I will start by creating a simple Games table with three columns. Id, Name and Developer.

zumo table

On to the code.

Firstly, make sure to place the necessary scripts on our HTML page to use Kendo UI and the ZUMO JavaScript SDK.

<script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
<script src="http://cdn.kendostatic.com/2013.3.1324/js/kendo.all.min.js"></script>
<script src="https://yourzumoname.azure-mobile.net/client/MobileServices.Web-1.0.0.min.js"></script>

I will use a Kendo UI Grid as example, but theoretically the DataSource can be used with many different Kendo components. Lets start by setting up a simple Video Games Kendo Grid that supports pagination, editing, inserting and deleting.

$("#grid").kendoGrid({
    pageable: true,
    dataSource: dataSource,
    columns: [
        "name",
        "developer", {
        command: [{
            name: "edit",
            text: "Edit"
        }, {
            name: "destroy",
            text: "Delete"
        }]
    }],
    toolbar: [{
        name: "create"
    }],
    editable: "inline"
});

The Kendo Grid itself is very easy to set-up. Most of the configuration necessary to work with the back-end will reside on the dataSource property.

If we want to write as little code as possible to get this grid working we simply need to make use of the ZUMO SDK and override the transport methods on the Kendo DataSource

var client = new WindowsAzure.MobileServiceClient("https:///*your ZUMO service name here*/.azure-mobile.net/", "/*Your API KEY here*/");
var table = client.getTable("games");
var dataSource = new kendo.data.DataSource({
    transport: {
        read: function (options) {
            table.includeTotalCount() //necessary for grid to paginate
                 .read()
                 .done(options.success);
        },
        update: function (options) {
            table.update(options.data)
                 .done(options.success);
        },
        create: function (options) {
            var item = options.data;
            delete item.id; //ZUMO doesnt allow you to set your own ID. It gets auto generated.
            table.insert(item)
                 .done(options.success);
        },
        destroy: function (options) {
            table.del(options.data)
                 .done(options.success);
        }
    },
    pageSize: 10,
    schema: {
        total: "totalCount",
        model: {
            id: "id",
            fields: {
                id: { type: "number" },
                name: { type: "string" },
                developer: { type: "string" },
            }
        }
    }
});

That’s it!. We now have a Video Games Grid that loads data from our ZUMO back-end and supports all CRUD operations.

Not so fast

One caveat with this approach, though, is that the paging will be done client-side. This means that all data will be loaded on one HTTP request, which might not be ideal.

In order to add server side paging we will have to use the take and skip methods on our MobileServiceTable to pass the page and pageSize to the server so that it can respond with the limited result-set that we are looking for.

var dataSource = new kendo.data.DataSource({
	transport: {
	    read: function (options) {
	        table.skip(options.data.skip)
	             .take(options.data.take)
	             .includeTotalCount()
	             .read()
	             .done(options.success);
	    },
	    update: function (options) {
	        table.update(options.data)
	             .done(options.success);
	    },
	    create: function (options) {
	        var item = options.data;
	        delete item.id;
	        table.insert(item)
	             .done(options.success);
	    },
	    destroy: function (options) {
	        table.del(options.data)
	             .done(options.success);
	    }
	},
	serverPaging: true,
	pageSize: 10,
	schema: {
	    total: "totalCount",
	    model: {
	        id: "id",
	        fields: {
	            id: { type: "number" },
	            name: { type: "string" },
	            developer: { type: "string" },
	        }
	    }
	}
});

Now the server will receive the pagination requirements (underlyingly using OData) and only retrieve the necessary items from the Azure database.

There are many more functionalities that are possible when using a Kendo DataSource along with an Azure Mobile Services back-end. This will hopefully give you a good idea of how to get started and where to go from here.