Date Range Filtering in Kendo Grid Using WEB API and Entity Framework

This article shows how to do date range filtering in a Kendo grid using WEB API and Entity Framework.

Introduction

Here I will explain how to implement the date range filtering in a Kendo grid using ASP.NET Web API and Entity Framework.

Description

The date range filtering in the Kendo grid can be done in one of the following two ways.

  1.  We can create a separate function to perform the Filtering.
  2.  We can use the filtering property of the Kendo Grid.
Create a separate function to perform data range filtering

Step 1

Create a Web API project as in the following:

  • Open Visual Studio 2012 and create a new project.
  • Select "File" -> "New" -> "Project...".
  • Select Web in the installed templates and click ASP.NET Web Application.
  • Provide the name for the project and click OK as in Figures 1 and 2.
 

Figure 1:
New Project
 
  
 
Figure 2: Web API

I am using the Entity Framework with Database First approach, so the Entity Framework builds default model classes and context classes.

My database schema is as in Figure 3.

 
 
Figure 3: Database Schema

My table structure is as in Figure 4.

 
 
Figure 4: Database
 
Step 2

Create a Web API 2 Controller class.

It's time to do the scaffolding.

Right-click on the Controller folder then select Add, click Controller and create a Web API 2 controller class as in Figures 5 and 6.
 


Figure 5: Controller
 
  
 
Figure 6: Add Controller

You will get some pre-defined HTTP GET, POST, PUT and DELETE requests/responses in the Order Histories Controller. Modify the code based on your application requirements. For this example I didn't modify the code.

Now the REST services have been created, it's time to consume the services.

Before implementing the service in the design, just cross-check it using Fiddler or Postman as shown in Figure 7.

 
 
Figure 7: Before implementing

Step 3

Create one HTML page in the project, in my case I named it DateFilter.html.

Here is my code in DateFilter.html.

  1. <!DOCTYPE html>  
  2. <html xmlns="http://www.w3.org/1999/xhtml">  
  3. <head>  
  4.     <title></title>  
  5.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.common.min.css">  
  6.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.rtl.min.css">  
  7.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.default.min.css">  
  8.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.mobile.all.min.css">  
  9.     <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>  
  10.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/angular.min.js"></script>  
  11.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/jszip.min.js"></script>  
  12.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/kendo.all.min.js"></script></head>  
  13. <body>  
  14.     <div>From: <input id="from" /> To: <input id="to" />   
  15.     <br />  
  16.     <br />  
  17.     <button id="filter" class="k-button">Filter</button></div>  
  18.     <br />  
  19.     <br />  
  20.     <div id="grid"></div> 
  21.     <script>  
  22.         var grid = $("#grid").kendoGrid({  
  23.             dataSource:{  
  24.                 type: "json",  
  25.                 transport: {  
  26.                     read: "api/OrderHistories"  
  27.                 },  
  28.                 pageSize: 10,  
  29.                     schema: {  
  30.                         model: {  
  31.                             fields: {  
  32.                                 OrderId: { type: 'number' },  
  33.                                 OrderItem: { type: 'string' },  
  34.                                 OrderDate: { type: 'date' }  
  35.                             }  
  36.                         }  
  37.                     }  
  38.             },  
  39.             pageable: true,  
  40.             columns:  
  41.             [  
  42.                 { field: "OrderID", width: 100, title: "Order ID", filterable: false },  
  43.                 { field: "OrderItem", width: 100, title: "Order Item", filterable: false },  
  44.                 { field: "OrderDate", width: 100, format: "{0:d}", title: "Order Date" }  
  45.             ]  
  46.         }).data("kendoGrid");  
  47.   
  48.         $("#from, #to").kendoDatePicker({  
  49.         });  
  50.   
  51.         $("#filter").on("click", function () {  
  52.             var from = $("#from").data("kendoDatePicker").value()  
  53.             var to = $("#to").data("kendoDatePicker").value()  
  54.             alert(from)  
  55.             var filter = [  
  56.                 { field: "OrderDate", operator: "gte", value: from },  
  57.                 { field: "OrderDate", operator: "lte", value: to }  
  58.             ];  
  59.             grid.dataSource.filter(filter);  
  60.         });  
  61.     </script>  
  62. </body>  
  63. </html>  

Result in the browser

Before filtering

 
 
Figure 8: Before Filtering
 
After Filtering 
 
 
 
Figure 9: After Filtering
 
Using Filtering property in the Kendo Grid

Just by setting the filterable property to true in the Kendo grid we can perform the date range filtering in the Kendo grid.

Here is my code in DateFilter.html:

  1. <!DOCTYPE html>  
  2. <html xmlns="http://www.w3.org/1999/xhtml">  
  3. <head>  
  4.     <title></title>  
  5.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.common.min.css">  
  6.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.rtl.min.css">  
  7.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.default.min.css">  
  8.     <link rel="stylesheet" href="http://kendo.cdn.telerik.com/2015.2.805/styles/kendo.mobile.all.min.css">  
  9.     <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>  
  10.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/angular.min.js"></script>  
  11.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/jszip.min.js"></script>  
  12.     <script src="http://kendo.cdn.telerik.com/2015.2.805/js/kendo.all.min.js"></script></head>  
  13. <body> 
  14.     <div id="grid"></div>  
  15.     <script>
  16.         var grid = $("#grid").kendoGrid({  
  17.             dataSource:{  
  18.                 type: "json",  
  19.                 transport: {  
  20.                     read: "api/OrderHistories"  
  21.                 },  
  22.                 pageSize: 10,  
  23.                     schema: {  
  24.                         model: {  
  25.                             fields: {  
  26.                                 OrderId: { type: 'number' },  
  27.                                 OrderItem: { type: 'string' },  
  28.                                 OrderDate: { type: 'date' }  
  29.                             }  
  30.                         }  
  31.                     }  
  32.             },  
  33.             pageable: true,  
  34.           filterable: true,  
  35.             columns:  
  36.             [  
  37.                 { field: "OrderID", width: 100, title: "Order ID", filterable: false },  
  38.                 { field: "OrderItem", width: 100, title: "Order Item", filterable: false },  
  39.                 { field: "OrderDate", width: 100, format: "{0:d}", title: "Order Date" }  
  40.             ]  
  41.         }).data("kendoGrid");   
  42.     </script>  
  43. </body>  
  44. </html>  

Result in browser

Before Filtering

 
Figure 10: Before Filtering
 
After Filtering 
 
 
  
Figure 11: After Filtering
 
Conclusion
 
From this article we learned how to do date range filtering in the Kendo grid by creating the separate function and by using the filtering property of the Kendo Grid.
 
I hope you have enjoyed this article, Thank you.