Server side filtering on Kendo MVC Grid by date range

Question

I have a column in kendo grid CreatedDate of type DateTime. I want to filter the grid's data by server side filtering based on CreatedDate by startdate and enddate.

I have tried various methods for his, but unfortunately nothing worked. I have another search scenario also where I'm able to filter data on basis of data from dropdownlist.

Here is my code for filtering by value of dropdownlist

if (request.Filters.Any())
{
    foreach (FilterDescriptor filterDescriptor in request.Filters)
    {
        if (filterDescriptor.Operator == FilterOperator.IsEqualTo)
        {
            obj = obj.Where(m => m.Type == filterDescriptor.Value.ToString());
        }
    }
}  

This is my code in a partial view for date range

<span><h3>Date Range:</h3></span>
<br />
<div class="col3 fLeft">
    <span>From:</span>
        @(Html.Kendo().DatePicker()
            .Name("dpDateFrom")
            .Value(DateTime.Today.AddDays(-1))
            .Format("dd/MM/yyyy")
            .Enable(true)
        )
</div>
<div class="col7 fRight">
    <span>To:</span>
    @(Html.Kendo().DatePicker()
        .Name("dpDateTo")
        .Value(DateTime.Today)
        .Format("dd/MM/yyyy")
        .Enable(true)
    )
</div>
<div>
    <button class="normal-btn" id="btnFilter" onclick="FilterGridByDate();">Search</button><br />
</div>

I have applied this javascript code on click of search button

function FilterGridByDate() {
    $From = $("#dpDateFrom").val();
    $To = $("#dpDateTo").val();
    $filtergridbydate = new Array();
    if ($From) {
        $filtergridbydate.push({ field: "CreatedDate", operator: "gte", value: $From });
    }
    if ($To) {
        $filtergridbydate.push({ field: "CreatedDate", operator: "lte", value: $To });
    }
    var Grid = $("#grid").data("kendoGrid");
    Grid.dataSource.filter($filtergridbydate);
}

How should I process it in controller?

Answer 1

Easiest way would probably be something like this.

The grid

Configure your grid with a default datasource, loading some default values. For example, last week's.

@(Html.Kendo().Grid<MyModel>()
    .Name("grid")
    .DataSource(ds => ds
        .Ajax()
        .Read("MyAction", "Home", new { startDate = DateTime.Now.AddDays(-7), endDate = DateTime.Now }))
)

Controller

Controller just needs to be able to handle the dates, then query your database using the two dates.

public JsonResult MyAction([DataSourceRequest]DataSourceRequest request, DateTime startDate, DateTime endDate) {
    var result = BL.ExecuteSomeQuery(startDate, endDate);
    return Json(result.ToDataSourceResult(request));
}

JavaScript

This function is called when you click the button. It needs to update the datasource parameters using read();, this will call your controller and update the grid.

function FilterGridByDate(){
    var from = $("#dpDateFrom").val();
    var to = $("#dpDateTo").val();
    var grid = $("#grid").data("kendoGrid");
    grid.dataSource.read({ startDate: from, endDate: to });
}

Answer 2

Try it:

Your view:

@(Html.Kendo().Grid<Namespace.Model>()
            .Name("grid")
            .DataSource(dataSource =>
            {
                dataSource.Ajax().PageSize(20)
                .Read(read => read.Action("ActionName", "ControllerName").Data("dateFilters"));
            })

see the Data("dateFilters")? Now you javascript:

function dateFilters() {
    return {
         dpDateFrom: $("#dpDateFrom").val(),
         dpDateTo: $("#dpDateTo").val(),
    }
}

function FilterGridByDate() {
    $("#grid").data("kendoGrid").dataSource.read();
}

and the action in your Controller

public ActionResult ActionName([DataSourceRequest]DataSourceRequest request, DateTime dpDateFrom, DateTime dpDateTo) 
{
    //use the two datetimes here
    return query.Where(x => x.StartDate >= dpDateFrom && x.EndDate <= dpDateTo).ToDataSourceResult(request);
}