Sorting grids bound to a WebAPI Controller

[Sean]

I'm trying to get sorting working on a grid that is bound to a WebAPI controller, but I am having trouble getting sorting to work right.  I have defined a grid using Kendo's MVC extensions as follows:

        @(Html.Kendo().Grid<DailyTotalsViewModel>()
              .Name("Grid")
              .Columns(cols => {
                    cols.Bound(p => p.Date).Width(60);
                    cols.Bound(p => p.Quantity)
                        .HtmlAttributes(new {style = "text-align: right"})
                        .Width(60);
                    cols.Bound(p => p.Note)
                        .Width(100);
              })
              .Pageable()
              .Sortable()
              .Scrollable()                        
              .DataSource(dataSource => dataSource.Ajax()                      
                    .Read(read =>  read.Type(HttpVerbs.Get).Url({service url here}))
                    .ServerOperation(true)
                    .Sort(s => s.Add(x => x.Date))
                    .PageSize(5)
              )
        )

And have been able to get that grid to load data from the following WebAPI method:
public class GridApiController : ApiController
{
[HttpGet]
public DataSourceResult GetDailyTotalGridRows(int? page, int? pageSize)
        {
{Retrieve DataSourceResult Here}
}
}

How do I get the sort information included in the request?  I've tried the following signatures:

public DataSourceResult GetDailyTotalGridRows(int? page, int? pageSize, SortDescriptor[] sort) 
public DataSourceResult GetDailyTotalGridRows(DataSourceRequest request)  
public DataSourceResult GetDailyTotalGridRows([DataSourceRequest] DataSourceRequest request)   

I have also tried using HttpPost instead of HttpGet, but haven't been able to get SortDescriptor data to my controller method.  

[Atanas Korchev]

Hi Sean,

 Kendo UI Complete for ASP.NET MVC does not currently support WebAPI controllers. Please refer to the ajax binding help topic for instructions how to bind the grid to a regular action method. Perhaps using the DataSourceRequest type as an argument of your action would help.

Regards,
Atanas Korchev
the Telerik team

[Sean]

I tried using the DataSourceRequest as the action's argument, however the request then throws an exception stating that there is no Media Type Formatter available for the DataSourceRequest type.  I could write a custom MediaTypeFormatter that would read DataSourceRequest arguments, but I was hoping there would already be one written.  I have been able to work around this issue by adding a Data function to the read transport.  Here is what my cshtml now looks like:
         @(Html.Kendo().Grid<DailyTotalsViewModel>()
              .Name("Grid")
              .Columns(cols => {
                  cols.Bound(p => p.Date).Width(60);
                    cols.Bound(p => p.CategoryId).Width(114);
                    cols.Bound(p => p.ExpenseTypeId).Width(64);
                    cols.Bound(p => p.Locations).Width(54);
                    cols.Bound(p => p.Quantity)
                        .HtmlAttributes(new {style = "text-align: right"})
                        .Width(60);
                    cols.Bound(p => p.Amount)
                        .HtmlAttributes(new {style = "text-align: right"})
                        .Width(60);
                    cols.Bound(p => p.Note)
                        .TemplateIsNullableString(x => x.Note)
                        .Width(100);
                    cols.Command(x => {
                        x.Edit();
                        x.Destroy();
                    });
              })
              .Editable(x => 
                    x.Enabled(true)
                     .Mode(GridEditMode.InLine)
                     .DisplayDeleteConfirmation(true))
              .Pageable().Scrollable().Sortable()
              .DataSource(dataSource => dataSource.Ajax()  
                    .Model(model => 
                        model.Id(x => x.ExpenseId)
                    )
                    .Read(x => x.Type(HttpVerbs.Get).Url(Href("~/api/Vouchers/593/DailyTotals")).Data("prepareDataQuery"))
                    .Create(x => x.Type(HttpVerbs.Post).Url(Href("~/api/Vouchers/593/DailyTotals")))
                    .Update(x => x.Type(HttpVerbs.Post).Url(Href("~/api/Vouchers/593/DailyTotals")))
                    .Destroy(x => x.Type(HttpVerbs.Delete).Url(Href("~/api/Vouchers/593/DailyTotals")))
                    .ServerOperation(true)
                    .Sort(s => s.Add(x => x.Date))
                    .PageSize(5)
              )
        )

And then I added this function to the page's javascript:


    function prepareGridQuery(query) {
        function sortClass(sortData) {
            var self = this;
            if (!isUoN(sortData) && sortData.length > 0) {
                self.srt = '';
                $.each(sortData, function (idx, src) {
                    if (isUoN(src.member))
                        self.srt += (src.field + ' ' + src.dir + ',');
                    else
                        self.srt += (src.member + ' ' + src.order + ',');
                });
                self.srt = self.srt.substring(0, self.srt.length - 1);
            } else
                self.srt = '';
            // toString should return srt
            self.toString = function () { return self.srt; };
        }
function parseGridSort(input) {
        var obj = new sortClass(input);
        var ret = obj.toString();
        delete obj;
        return ret;
} 
        if (query.sort != null && query.sort != undefined) {
            query.srt = parseGridSort(query.sort);
            delete query.sort;
        }
        return query;
    }

This allows me to pass the sort information to the server as a string, which I then use to create SortDescriptors in my WebAPI method.  This is not an ideal solution, but does provide a way for me to support sorting with a WebAPI controller for the time being.  Are there plans to fully support binding to WebAPI controllers in the future?

[Atanas Korchev]

Hello Sean,

 We do plan to add support for WebAPI. Unfortunately it currently lacks a very important feature - $inlinecount. Without it there is no way to determine the total number of records which is required to create a numeric pager. 

 I suggest you cast your vote for the related user voice item.

Regards,
Atanas Korchev
the Telerik team

[Sean]

While I understand how Microsoft adding support for ODATA operators to the WebAPI could make it easier to implement that service, this is not something that must happen before Kendo grids could bind to WebAPI data.  All that is required for the Kendo grid to create a numeric pager is for the object returned by the WebAPI to include the total number of records in its response to the Read service.  Currently, I achieve that by wrapping the data my controller returns with the following object:

    [DataContract]
    public class GridQueryResponse<TModel>
    {
        public GridQueryResponse(IEnumerable<TModel> records, int pageNumber, int totalRecords)
        {
            if (records == null) throw new ArgumentNullException("records");
            if (pageNumber < 1) throw new ArgumentOutOfRangeException("pageNumber", pageNumber, Res.PageNumberMustBeOne);
            Data = records.ToArray();
            PageNumber = pageNumber;
            Total = totalRecords;
        }
        /// <summary>Initializes a new instance of the <see cref="GridQueryResponse&lt;TModel&gt;"/> class.</summary>
        public GridQueryResponse()
        { }
        /// <summary>Gets the page represented by this <see cref="GridQueryResponse{e}"/>.</summary>
        /// <value>An <see cref="Int32"/> value.</value>
        [DataMember]
        public int PageNumber
        { get; set; }
        /// <summary>Gets the total number of records that match the request.  This value will be used by the grid to determine the total number of available pages.</summary>
        /// <value>An <see cref="Int32"/> value.</value>
        [DataMember]
        public int Total
        { get; set; }
        /// <summary>Gets the grid's data.</summary>
        /// <value>An <see cref="IEnumerable{T}">enumerable set</see> of values.</value>
        [DataMember]
        public TModel[] Data 
        { get; set; }
    }

Since Page and PageSize are passed to the controller in the Query String arguments, we have everything we need to perform pagination.  Once you have your IQueryable (and have applied the filters described by the filter descriptors...I use Dynamic Linq to achieve this but that's a whole separate thread), you can call Count() and save the result as the Total, then use Page and PageSize to apply pagination.  Here's a simplified example that is using a WebAPI controller but passing sort and filter data via a string (per my earlier post):

[HttpGet, Authorize(Roles = Roles.Vouchers.LAE_User)]
public GridQueryResponse<DailyTotalsViewModel> GetDailyTotalGridRows(int? page, int? pageSize, string srt, string fltr)
{
IQueryable<DailyTotalsViewModel> query = ReadFromEntityFramework();
// (Parsing Filter Descriptor and applying to query omitted for brevity)
page = page ?? 1;
pageSize = pageSize ?? 10;
var total = query.Count();
// (Parsing Sort Descriptor and applying to query omitted for brevity) 
query.Skip((page.Value - 1) * pageSize.Value);
query.Take(pageSize.Value);
return new GridQueryResponse<DailyTotalsViewModel>(query, page.Value, total);
}

[Jeff]

If you're using RC of MVC 4 Web API, you can get this working by prefixing your complex types with the [FromUri] attribute.

Here's a quick example of some pseudo code:

public ViewModel GetAll(int skip = 0, int take = 10, int page = 1, int pageSize = 10, [FromUri]List<SortInfo> sort)
{
   //Load your data and return your model. Pseudo code:
   ViewModel vm = new ViewModel();
   int totalRecords = 0;
   vm.Data = Repository.GetAll(page, pageSize, sort, out totalRecords);
   vm.TotalRecords = totalRecords;
   return vm;
}

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