How to perform server side filtering on Kendo UI Grid

Question

I am trying to implement server side filtering for a Kendo UI grid (client only). I am not sure how to pass the filter operator and value entered on the filter box. I was able to implement server paging and want the filtering to work alongside the server paging, i.e. show page 2 of 5 items of filtered rows. I saw some example of binding the request to "DataSourceRequest" object but we do not have licence for the server side Kendo UI and have to achieve it using the client side changes only.

Here is my jQuery code:

var page = 1;
var pageSize = 5;
var title = "test";

var selectWork = function (e) {
    alert("selected");
};

$("#selectWorkGrid").empty();
$("#selectWorkGrid").kendoGrid({
    dataSource:
    {
        transport: {
            read: {
                url: "http://example.com/" + "work/SearchWorkJ?worktitle=" + title,
                dataType: "json",
                contentType: "application/json",
                data: {
                    page: page,
                    pageSize: pageSize
                }
            },
            serverFiltering: true,                    
            parameterMap: function (data, type) {
                if (type == "read") {
                    return {
                        page: data.page,
                        pageSize: data.pageSize
                    }
                }
            }
        },
        schema: {
            model: {
                id: "workId",
                fields: {
                    workId: { type: "number" },
                    workTitle: { type: "string" },
                    writers: { type: "string" },
                    durationInMmSs: { type: "string" }
                }
            },
            data: "data",
            total: "total"
        },
        pageSize: pageSize,
        serverPaging: true,
        serverFiltering: true
    },
    sortable: true,
    resizable: true,
    columnMenu: false,
    filterable: {
        mode: "row",
        extra: false,
        operators: {
            string: {
                startswith: "Starts with",
                eq: "Is equal to",
                neq: "Is not equal to"
            }
        }
    },
    noRecords: {
        template: "No results available."
    },
    pageable: {
        numeric: false,
        refresh: true,
        buttonCount: 15
    },
    scrollable: false,
    columns: [
        {
            field: "workTitle",
            title: "Title",
            template: "#=workTitle#"
        },
        {
            field: "writers",
            title: "Writers",
            filterable: false,
            template: "${writers == null ? '':writers}",
            width: 300
        },
        {
            field: "durationInMmSs",
            title: "Duration",
            filterable: false,
            headerAttributes: { style: "text-align:right;" },
            attributes: { style: "text-align:right;" },
            width: 80
        },
        { command: { text: "Select", click: selectWork }, title: "", width: 60 }
    ]
});

Controller action returning json:

public ContentResult SearchWorkJ(string workTitle, int page = 0, int pageSize = 0)
{
    var worksJson = "";
    var works = WorkService.SearchWork(workTitle, page, pageSize);
    if (works != null)
    {
        // Set total to upto current record + 1 so that next button works in kendo
        int totalCount = page * pageSize + 1;
        var sortedWorks = new List<WorkViewModel>();
        sortedWorks.AddRange(works.Select(w => new WorkViewModel
        {
            WorkId = w.WorkId,
            WorkTitle = w.WorkTitle,
            Writers = w.Writers,
            DurationInMmSs = w.Duration
        }).OrderBy(w => w.WorkTitle));
        worksJson = JsonConvert.SerializeObject(new { total = totalCount, data = sortedWorks });
    }
    return new ContentResult { Content = worksJson, ContentType = "application/json" };
}

Solution

If you look at this

https://dojo.telerik.com/EhUNUwOr

<div id="my-grid"></div>
  <script>
    $('#my-grid').kendoGrid({
        dataSource: {
            serverFiltering: true,
          serverSorting: true,
          serverPaging: true,
          pageSize: 5,
          transport: {
            read: function(options) {

              $.ajax({
                url: '/yourapi',
                contentType: 'application/json',
                dataType: 'json',
                type: 'POST',
                data: JSON.stringify(options.data),
                success: function(result) {
                    options.success(result); 
                }
              })

            }
          },
          schema: {
            id: 'Id',
            data: 'Data',
            total: 'Total',
            errors: 'Errors',
            fields: [
              { field: 'Id', type: 'number' },
              { field: 'FirstName', type: 'string' },
              { field: 'LastName', type: 'string' }
            ]
          },
          filter: {
            filters: [{ field: 'FirstName', operator: 'eq', value: 'David' }] 
          }
        },
    });
  </script>  

This will send

{"take":5,"skip":0,"page":1,"pageSize":5,"filter":{"filters":[{"field":"FirstName","operator":"eq","value":"David"}]}}

to your server / api

now if you have a model that shares this structure you can respond in the following format

{
   "Data" : <your array of models>,
   "Total" : the number of models that fits your filter regardless of the filter, this helps kendo grid knowing how many pages there is for the pager.,
   "Errors" : is mostely used for create and update so just return null
}

---

From here its a bonus to the answer above.

I noticed you are using CSharp so you have two options to apply create dynamic queries from Queryable.

use a library I open sourced https://github.com/PoweredSoft/DynamicLinq which is available on Nuget https://www.nuget.org/packages/PoweredSoft.DynamicLinq/

There is an example you can look at on git hub. You'll have to adapt the code around but it should get you started.

https://github.com/PoweredSoft/DynamicLinq#how-it-can-be-used-in-a-web-api

[HttpGet][Route("FindClients")]
public IHttpActionResult FindClients(string filterField = null, string filterValue = null, 
string sortProperty = "Id", int? page = null, int pageSize = 50)
{
    var ctx = new MyDbContext();
    var query = ctx.Clients.AsQueryable();

    if (!string.IsNullOrEmpty(filterField) && !string.IsNullOrEmpty(filterValue))
    query = query.Query(t => t.Contains(filterField, filterValue)).OrderBy(sortProperty);

    //  count.
    var clientCount = query.Count();
    int? pages = null;

    if (page.HasValue && pageSize > 0)
    {
    if (clientCount == 0)
        pages = 0;
    else
        pages = clientCount / pageSize + (clientCount % pageSize != 0 ? 1 : 0);
    }

    if (page.HasValue)
    query = query.Skip((page.Value-1) * pageSize).Take(pageSize);

    var clients = query.ToList();

    return Ok(new
    {
    total = clientCount,
    pages = pages,
    data = clients
    });
}

An alternative is using

https://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library

Comments

[David Lebee]

No problem enjoy :)