MVC Kendo Grid Foreign Table Ordering

Some time ago, I was working on a project which used Kendo MVC Wrappers, they are pretty handy beacause they save you a lot of work with ajax call, formating, ordering, paging, among others, but many times I found myself struggling deep inside Telerik documentation trying to find out how to make something a little bit different from Telerik's Demos

A few weeks ago, I needed to make a table grid who included a field from a foreign table that I accessed through a navigation property. I had to present to user a list of app comments with the following fields:

• CommentId
• Comment
• Application (This is taken from a other)

@(Html.Kendo().Grid()
 .Name("grid")
 .Pageable()
 .Sortable()
 .Filterable()
  .DataSource(dataSource => dataSource
  .Ajax()
  .Read(read => read
   .Action("GetAll", "Comment")
  )
 ).Columns(col =>
 {
  col.Bound(p => p.CommentId).Title("ID").Width(60);
  col.Bound(p => p.Text).Title("Comment");
  //foreign table field
  col.Bound(p => p.AppName).Title("App");
 })
 .Filterable(x => x.Enabled(false))
)

The Kendo Framework provide useful helpers and extension to make working with grids trivial, an example of this is this controller action it which it automatically handles paging, sorting, filtering, etc. All this features come just out of the box, but they don't work well when it comes to navigation properties. You will get the following error:

So, you have to ways to choose, option 1:

Use a database view as data source so the navegation property is obtained as it was an other entity field

 


Option 2:

We could modify the sorting info that is passed to kendo helper extension.

In a normal action you usually have somethin like this

public ActionResult GetAll([DataSourceRequest]DataSourceRequest request)
{
 //get entities in IQuerable
 var allEntity = ModelDefault.GetAll();
 var result = allEntity.ToDataSourceResult(request, data => data.ToDTO());
 return Json(result, JsonRequestBehavior.AllowGet);
}

All paging, filtering and sorting work is done by kendo extension y this instruction

var result = allEntity.ToDataSourceResult(request, data => data.ToDTO());

CommentDTO class:

public partial class CommentDTO
{
 //more code

 public Int CommentId { get; set; }

 public String Text { get; set; }

 public String AppName { get; set; } //holds foreing table app name

 //more code
}

and DTO tranformation with this extension method

static partial void ToDTOs(this Comment entity)
{
 //some code...

 dto.AppName = entity.App != null ? entity.App.Name : string.Empty;

 //some code...
}

so, lets help kendo with this...

Kendo by default will send to the controller action the field name as the sorting information in the Sorts property, so we can use this to check if any SortDescriptor has Member property equals to "AppName" (because the field we used in the grid was dto.AppName) and modify it in a way it can be used to sorts over the foreign table field

 

Now the user will be able to sort over the app name without making as to create a database view just to support it

This is the final code...I hope it helps you

public ActionResult GetAll([DataSourceRequest]DataSourceRequest request)
{
 //get entities in IQuerable
 var allEntity = ModelDefault.GetAll();

 //ordering logic
 var sort = request.Sorts.FirstOrDefault(x => x.Member == "AppName");
 if (sort != null)
 {
  sort.Member = "App.Name";
 }

 var result = allEntity.ToDataSourceResult(request, data => data.ToDTO());
 return Json(result, JsonRequestBehavior.AllowGet);
}

Please tell me if this helped you or if you know other ways to achive the same... ;)