Filtering in ASP.NET MVC Grid

28 Jan 202224 minutes to read

Filtering helps to view the particular or related records from dataSource which meets a given filtering criteria. To enable filter, set the AllowFiltering property astrue.

The Grid supports three types of filter, they are the following.

  1. Filter Bar
  2. Menu
  3. Excel

Four types of filter menu is available in all filter types, they are the following.

  1. String
  2. Numeric
  3. Date
  4. Boolean

The corresponding filter menu is opened based on the column type.

NOTE

  1. Need to specify the Type of column, when first record data value is empty or null otherwise the filter menu is not opened.
  2. The default filter type is Filter bar, when the AllowFiltering is enabled and FilterType is not set.

The following code example describes the previous behavior.

@(Html.EJ().Grid<Object>("FlatGrid")
            .Datasource((IEnumerable<object>)ViewBag.DataSource)
            .AllowPaging()
            .AllowFiltering()
            .Columns(col =>
            {
                col.Field("OrderID").HeaderText("Order ID").Add();
                col.Field("EmployeeID").HeaderText("Employee ID").Add();
                col.Field("CustomerID").HeaderText("Customer ID").Add();
                col.Field("ShipCountry").HeaderText("Ship Country").Add();
                col.Field("Freight").HeaderText("Freight").Add();
            }))
namespace MVCSampleBrowser.Controllers
          {
            public class GridController : Controller
              { 
                public ActionResult GridFeatures()
                 {
                   var DataSource = new NorthwindDataContext().OrdersViews.ToList();
                   ViewBag.DataSource = DataSource;
                   return View();
                 }
             }
        }

The following output is displayed as a result of the previous code example.

ASP.NET MVC Grid

You can enable menu filter by setting FilterType as Menu in FilterSettings

There is an option to show or hide the additional filter options in the menu by setting the ShowPredicate as true or false in FilterSettings respectively.

NOTE

For the FilterType property you can assign either string value (“Menu”) or enum value (Syncfusion.JavaScript.FilterType.Menu).

A specified range of values can be filtered by using the between operator for the column type number, date and datetime.

The following code example describes the previous behavior.

@(Html.EJ().Grid<Object>("FlatGrid")
            .Datasource((IEnumerable<object>)ViewBag.DataSource)
            .AllowPaging()
            .AllowFiltering()
            .FilterSettings(filter => { filter.FilterType(FilterType.Menu); })
            .Columns(col =>
              {
                col.Field("OrderID").HeaderText("Order ID").Add();
                col.Field("EmployeeID").HeaderText("Employee ID").Add();
                col.Field("CustomerID").HeaderText("Customer ID").Add();
                col.Field("ShipCountry").HeaderText("Ship Country").Add();
                col.Field("Freight").HeaderText("Freight").Add();
             }))
namespace MVCSampleBrowser.Controllers
        {
            public class GridController : Controller
              { 
                public ActionResult GridFeatures()
                 {
                    var DataSource = new NorthwindDataContext().OrdersViews.ToList();
                    ViewBag.DataSource = DataSource;
                    return View();
                 }
             }
        }

The following output is displayed as a result of the previous code example.

ASP.NET MVC Grid Menu filter

Numeric Filter

ASP.NET MVC Grid Numeric Filter

String Filter

ASP.NET MVC Grid String Filter

Date Filter

ASP.NET MVC Grid Date Filter

Boolean Filter

Excel-like filter

You can enable excel menu by setting FilterType as Excel in the FilterSettings . The excel menu contains an option such as sorting, clear filter, submenu for advanced filtering.

The following code example describes the previous behavior.

@(Html.EJ().Grid<Object>("FlatGrid")
            .Datasource((IEnumerable<object>)ViewBag.DataSource)
            .AllowPaging()
            .AllowFiltering()
            .FilterSettings(filter => { filter.FilterType(FilterType.Excel); })
            .Columns(col =>
            {
                col.Field("OrderID").HeaderText("Order ID").Add();
                col.Field("EmployeeID").HeaderText("Employee ID").Add();
                col.Field("CustomerID").HeaderText("Customer ID").Add();
                col.Field("ShipCountry").HeaderText("Ship Country").Add();
                col.Field("Freight").HeaderText("Freight").Add();
            }))
namespace MVCSampleBrowser.Controllers
        {
            public class GridController : Controller
              { 
                public ActionResult GridFeatures()
                 {
                   var DataSource = new NorthwindDataContext().OrdersViews.ToList();
                   ViewBag.DataSource = DataSource;
                   return View();
                 }
             }
        }

The following output is displayed as a result of the previous code example.

ASP.NET MVC Grid Excel-like filter

Filtering between values

By using the custom filter feature of the Excel filter, we can filter between values of the column. The following screenshot depicts the usage of “Between” option of the custom filter dialog.

Caption to image
Caption to image

Checkbox list generation:

By default, the checkbox list is generated from the distinct values of the filter column from dataSource which gives an option to search and select the required items.

Also, on checkbox list generation, if the number of distinct values are greater than 1000, then the excel filter will display only first 1000 values and show “Not all items shown” label to ensure the best performance on rendering and searching. However this limit has been customized according to your requirement by setting the MaxFilterChoices with required limit in integer.

NOTE

  1. Using excel filter events you can change the dataSource of the checkbox list.
  2. Query of checkbox list can also be changed using excel filter events.

The following code example describes the previous behavior.

@(Html.EJ().Grid<Object>("FlatGrid")
            .Datasource((IEnumerable<object>)ViewBag.DataSource)
            .AllowPaging()
            .AllowFiltering()
            .FilterSettings(filter => { filter.FilterType(FilterType.Excel).MaxFilterChoices(4); })
            .Columns(col =>
             {
                col.Field("OrderID").HeaderText("Order ID").Add();
                col.Field("EmployeeID").HeaderText("Employee ID").Add();
                col.Field("CustomerID").HeaderText("Customer ID").Add();
                col.Field("ShipCountry").HeaderText("Ship Country").Add();
                col.Field("Freight").HeaderText("Freight").Add();
             }))
namespace MVCSampleBrowser.Controllers
        {
            public class GridController : Controller
              { 
                public ActionResult GridFeatures()
                 {
                    var DataSource = new NorthwindDataContext().OrdersViews.ToList();
                    ViewBag.DataSource = DataSource;
                    return View();
                 }
             }
        }

The following output is displayed as a result of the previous code example.

ASP.NET MVC Grid Checkbox list generation

Add current selection to filter checkbox:

When filtering is done multiple times on the same column then the previously filtered values on the column will be cleared. So, to retain the old values Add current selection to filter checkbox can be used which is displayed when data is searched in the search bar.

The following image describes the previous mentioned behavior.

ASP.NET MVC Grid filter checkbox

Case sensitivity

To perform filter operation with case sensitive in excel styled filter menu mode by setting EnableCaseSensitivity as true.

The following code example describes the previous behavior.

@(Html.EJ().Grid<Object>("FlatGrid")
            .Datasource((IEnumerable<object>)ViewBag.DataSource)
            .AllowPaging()
            .AllowFiltering()
            .FilterSettings(filter => { filter.FilterType(FilterType.Excel).EnableCaseSensitivity(true);  })
            .Columns(col =>
             {
                col.Field("OrderID").HeaderText("Order ID").Add();
                col.Field("EmployeeID").HeaderText("Employee ID").Add();
                col.Field("CustomerID").HeaderText("Customer ID").Add();
                col.Field("ShipCountry").HeaderText("Ship Country").Add();
                col.Field("Freight").HeaderText("Freight").Add();
             }))
namespace MVCSampleBrowser.Controllers
         {
            public class GridController : Controller
              { 
                public ActionResult GridFeatures()
                 {
                    var DataSource = new NorthwindDataContext().OrdersViews.ToList();
                    ViewBag.DataSource = DataSource;
                    return View();
                 }
             }
        }

The following output is displayed as a result of the previous code example.

ASP.NET MVC Grid Case sensitivity

Filter bar

The ‘Filter bar’ row is located next to column header of grid. You can filter the records with different expressions depending upon the column type. To show the filter bar row, set the FilterType as FilterBar.

List of filter bar expressions:

You can enter the below filter expressions manually in the filter bar.

Expression Example Description Column Type
= = value Equal Numeric
!= != value NotEqual
> > value GreaterThan
< < value LessThan
>= >= value GreaterThanOrEqual
<= <= value LessThanOrEqual
N/A N/A Always `StartsWith` operator will be used for the string filter String
N/A N/A Always `Equal` operator will be used for the Date filter Date
N/A N/A Always `Equal` operator will be used for the Boolean filter Boolean

The following code example describes the previous behavior.

@(Html.EJ().Grid<Object>("FlatGrid")
            .Datasource((IEnumerable<object>)ViewBag.DataSource)
            .AllowPaging()
            .AllowFiltering()
            .FilterSettings(filter => { filter.FilterType(FilterType.FilterBar); })
            .Columns(col =>
            {
                col.Field("OrderID").HeaderText("Order ID").Add();
                col.Field("EmployeeID").HeaderText("Employee ID").Add();
                col.Field("CustomerID").HeaderText("Customer ID").Add();
                col.Field("ShipCountry").HeaderText("Ship Country").Add();
                col.Field("Freight").HeaderText("Freight").Add();
            }))
namespace MVCSampleBrowser.Controllers
        {
            public class GridController : Controller
              { 
                public ActionResult GridFeatures()
                 {
                    var DataSource = new NorthwindDataContext().OrdersViews.ToList();
                    ViewBag.DataSource = DataSource;
                    return View();
                 }
             }
        }

The following output is displayed as a result of the previous code example.

ASP.NET MVC Grid Filter bar

Filter bar modes:

This specifies the grid to start the filter action while typing in the filter bar or after pressing the enter key based on the FilterBarMode.There are two types of FilterBarMode, they are the following.

  1. OnEnter
  2. Immediate

NOTE

For the FilterBarMode property you can assign either string value (OnEnter) or enum value (Syncfusion.JavaScript.FilterBarMode.OnEnter).

Filter bar message:

The filter bar message is supported only for the FilterType as ‘FilterBar’. The filtered data with column name is displayed in the grid pager itself. By default the ShowFilterBarStatus is ‘true’.

The following code example describes the previous behavior.

@(Html.EJ().Grid<Object>("FlatGrid")
            .Datasource((IEnumerable<object>)ViewBag.DataSource)
            .AllowPaging()
            .AllowFiltering()
            .FilterSettings(filter => { filter.ShowFilterBarStatus(true); })
            .Columns(col =>
             {
                col.Field("OrderID").HeaderText("Order ID").Add();
                col.Field("EmployeeID").HeaderText("Employee ID").Add();
                col.Field("CustomerID").HeaderText("Customer ID").Add();
                col.Field("ShipCountry").HeaderText("Ship Country").Add();
                col.Field("Freight").HeaderText("Freight").Add();
            }))
namespace MVCSampleBrowser.Controllers
          {
            public class GridController : Controller
              { 
                public ActionResult GridFeatures()
                 {
                   var DataSource = new NorthwindDataContext().OrdersViews.ToList();
                   ViewBag.DataSource = DataSource;
                   return View();
                 }
              }
           }

The following output is displayed as a result of the previous code example.

ASP.NET MVC Grid Filter bar message

Filter Operators

The grid controls uses filter operators from the DataManager, which are used at the time of filtering.

List of column type and filter operators.

Column Type Filter Operators
Number FilterOperatorType.GreaterThan
FilterOperatorType.GreaterThanOrEqual
FilterOperatorType.LessThan
FilterOperatorType.LessThanOrEqual
FilterOperatorType.Equal
FilterOperatorType.NotEqual
String FilterOperatorType.StartsWith
FilterOperatorType.EndsWith
FilterOperatorType.Contains
FilterOperatorType.Equal
FilterOperatorType.NotEqual
Boolean FilterOperatorType.Equal
FilterOperatorType.NotEqual
Date FilterOperatorType.GreaterThan
FilterOperatorType.GreaterThanOrEqual
FilterOperatorType.LessThan
FilterOperatorType.LessThanOrEqual
FilterOperatorType.Equal
FilterOperatorType.NotEqual

FilterBar template

Usually enabling the AllowFiltering, will create default textbox in grid FilterBar. So, Using the [FilterBarTemplate] property of Columns we can render any other controls like AutoComplete, DropDownList etc in filterbar to filter the grid data for the particular column.
It has three functions. They are the following:

  1. create - It is used to create the control at time of initialize.
  2. read - It is used to read the filter value selected.
  3. write - It is used to render the control and assign the value selected for filtering.

The following code example describes the previous behavior.

@(Html.EJ().Grid<EditableOrder>("Filtering")
        .Datasource((IEnumerable<object>)ViewBag.datasource)
        .AllowFiltering()
        .AllowPaging()
        .Columns(col =>
        {
            col.Field("OrderID").HeaderText("Order ID").TextAlign(TextAlign.Right).Width(75).Add();
            col.Field("CustomerID").HeaderText("CustomerID").FilterBarTemplate(filterbar => filterbar.Create("autoComplete_create").Write("autoComplete_write").Read("autoComplete_read")).Width(90).Add();
            col.Field("EmployeeID").HeaderText("EmployeeID").FilterBarTemplate(filterbar => filterbar.Write("dropdown_write").Read("dropdown_read")).TextAlign(TextAlign.Right).Width(75).Add();
            col.Field("Freight").HeaderText("Freight").FilterBarTemplate(filterbar => filterbar.Write("numeric_write").Read("numeric_read")).TextAlign(TextAlign.Right).Width(75).Format("{0:C}").Add();
            col.Field("ShipCountry").HeaderText("Ship Country").Width(90).Add();
            col.Field("Verified").HeaderText("Verified").TextAlign(TextAlign.Center).FilterBarTemplate(filterbar => filterbar.Write("check_write").Read("check_read")).Width(80).Add();
        })
    )
namespace MVCSampleBrowser.Controllers
  {
    public partial class GridController : Controller
    {
        public ActionResult Filtering()
        {
            var DataSource = OrderRepository.GetAllRecords().ToList();
            ViewBag.datasource = DataSource;
            return View();
        }

     }
  }
<script type="text/javascript">
		function autoComplete_create(args) {
            return "<input>"
        }
        function autoComplete_write(args) {
			var gridObj = $("#Filtering").ejGrid("instance");
            var data = ej.DataManager(gridObj.model.dataSource).executeLocal(new ej.Query().select("CustomerID"));
            args.element.ejAutocomplete({ width: "100%", dataSource: data, enableDistinct: true, focusOut: ej.proxy(args.column.filterBarTemplate.read, this, args) });
        }
        function autoComplete_read(args) {
            this.filterColumn(args.column.field, "equal", args.element.val(), "and", true)
        }
        function dropdown_write(args) {
            var data = [{ text: "clear", value: "clear" }, { text: "1", value: 1 }, { text: "2", value: 2 }, { text: "3", value: 3 }, { text: "4", value: 4 },
                                                            { text: "5", value: 5 }, { text: "6", value: 6 }, { text: "7", value: 7 }, { text: "8", value: 8 }, { text: "9", value: 9 }
            ]
            args.element.ejDropDownList({ width: "100%", dataSource: data, change: ej.proxy(args.column.filterBarTemplate.read, this, args) })
        }
        function dropdown_read(args) {
            if (args.element.val() == "clear") {
                this.clearFiltering(args.column.field);
                args.element.val("")
            }
            this.filterColumn(args.column.field, "equal", args.element.val(), "and", true)
        }
        function numeric_write(args) {
            args.element.ejNumericTextbox({ width: "100%",decimalPlaces: 2, focusOut: ej.proxy(args.column.filterBarTemplate.read, this, args) });
        }
        function numeric_read(args) {
            this.filterColumn(args.column.field, "equal", args.element.val(), "and", true)
        }
        function check_write(args) {
            args.element.ejCheckBox({ change: ej.proxy(args.column.filterBarTemplate.read, this, args) });
        }
        function check_read(args) {
            this.filterColumn(args.column.field, "equal", args.element.parent().attr('aria-checked'), "and", true)
        }
    </script>

The following output is displayed as a result of the previous code example.

ASP.NET MVC Grid Filter Operators

After Filtering