Filtering in Code
- 13 minutes to read
- Filter Expressions
- Filter Expression Syntax
- Filtering Data in Code
- Filter Modes: Display Text vs Value
- Customize Filtering Dropdown Menus
- Automatic Filtering Row API
- Filtering Attributes Support
- Searching for Records and Rows
Filter Expressions
A filter expression is a formula (or a set of formulas) that specifies how data should be filtered. Each expression contains three parts:
- a data field whose values should be filtered;
- a filtering value that should be compared to records stored in the data field;
- an operator that compares data field values with a filtering value.
For example, the following expression selects all the “Count” data field values that are greater than 5 but less than 20:
[Count] Between (‘5’, ‘20’)
Filter Expression Syntax
Operator | Description | Example |
|---|---|---|
= | Equals. Selects data field values that equal the entered filtering value. | [OrderDate] = #2016-01-01# |
<> | Does not equal. Selects data field values that are not equal to the entered filtering value. | [OrderDate] <> #2016-01-01# |
> | Is greater than. Selects data field values that are greater than the entered filtering value. | [OrderDate] > #2016-01-01# |
>= | Is greater than or equal to. Selects data field values that are greater than the entered filtering value or equal to it. | [OrderDate] >= #2016-01-01# |
<= | Is less than or equal to. Selects data field values that are less than the entered filtering value or equal to it. | [OrderDate] >= #2016-01-01# |
< | Is less than. Selects data field values that are less than the entered filtering value. | [OrderDate] >= #2016-01-01# |
Between | Is between. Selects data field values that belong to the specific value interval. | [CustomerID] Between (‘1’, ‘100’) |
Not Between | Is not between. Selects data field values that lie outside the specific value interval. | Not [CustomerID] Between (‘1’, ‘100’) |
Contains | Contains. Selects data field values that contain the filtering value. | Contains([ShipCountry], ‘land’) |
Not Contains | Does not contain. Selects data field values that do not contain the filtering value. | Not Contains([ShipCountry], ‘land’) |
Starts with | Begins with. Selects data field values that start with the filtering value. | Starts with([ShipCountry], ‘G’) |
Ends with | Ends with. Selects data field values that ends with the filtering value. | Ends with([ShipCountry], ‘ia’) |
In | Is any of. Selects data field values that equal any of the entered filtering values. | [ShipCountry] In (‘Germany’, ‘Italy’, ‘USA’) |
Not In | Is none of. Selects data field values that do not equal any of the entered filtering values. | Not [ShipCountry] In (‘Germany’, ‘Italy’, ‘USA’) |
Like | Is like. Selects data field values that contain the filtering value. Accepts wildcards: ‘_’ to replace a single character, ‘%’ to replace any number of characters. | [OrderDate] Like ‘%2011’ same as Contains ([OrderDate], ‘2011’) |
Not Like | Is not like. Selects data field values that do not contain the filtering value. Accepts wildcards: ‘_’ to replace a single character, ‘%’ to replace any number of characters. | Not [OrderID] Like ‘103__’ same as Not [OrderID] Between (‘10300’, ‘10399’) |
Is Null | Is null. Selects null values. | [ShipRegion] Is Null |
Is Not Null | Is not null. Excludes null values. | [ShipRegion] Is Not Null |
- String values must be enclosed within single quote characters. If a single quote character needs to be included as a literal to a filter, it must be doubled (e.g., [ProductID] LIKE ‘Uncle Bob’’s%’);
Date-time values must be wrapped with the ‘#’ characters and represented using a culture-independent (invariant) format. The invariant culture is based on the English culture, but some of the idiosyncratic English formats have been replaced by more globally-accepted formats. Below are some of the culture-independent formats for representing date-time values.
MM/dd/yyyy — 07/30/2008
dd MMM yyyy — 30 JUL 2008
yyyy-MM-dd — 2008-07-30
yyyy-MM-ddTHH:mm:ss — 2008-07-30T22:59:59
yyyy-MM-dd HH:mm:ssZ — 2008-07-30 15:59:59Z
Filtering Data in Code
ColumnView.ActiveFilterString and ColumnView.ActiveFilter
Allow you to pass a filtering expression as a string in order to apply data filtering to the entire View. The following sample applies filtering by the “Order Date” and “Ship Region” fields.
gridView1.ActiveFilterString = "[OrderDate] Between (#01 JAN 2010#, #01 DEC 2010#) And [ShipRegion] Is not null";
//or
gridView1.ActiveFilter.Clear();
gridView1.ActiveFilter.NonColumnFilter = "[OrderDate] Between (#01 JAN 2010#, #01 DEC 2010#) And [ShipRegion] Is not null";
Allows you to filter data by specific columns. The FilterInfo property accepts objects of the ColumnFilterInfo type.
string filterString = "[UnitPrice] > 20 AND [UnitPrice] < 30";
gridView.Columns["UnitPrice"].FilterInfo = new ColumnFilterInfo(filterString);
A ColumnFilterInfo object can hold any valid filter expression, including an expression that filters another column’s data.
string filterString = "[ShipCountry] = 'Canada'";
gridView1.Columns["ShipCity"].FilterInfo = new ColumnFilterInfo(filterString);
ViewFilter.NonColumnFilter (accessed through the ColumnView.ActiveFilter property)
Utilize this property to apply a filter expression that is not associated with any specific grid column. As a result, end-users will not be able to clear or modify this filter by using column filter dropdown menus.
Filter Modes: Display Text vs Value
The figure below illustrates a classic filtering drop-down menu invoked for a DateTime “Order Date” column. This column has a custom ‘yyyy’ display format, which leaves only years visible. However, these dates are still unique and since the Data Grid filters data by values by default, the popup filtering menu displays an item for each date. To end-users, this looks like the same item being duplicated multiple times (left figure). Utilize the GridColumn.FilterMode property to switch this filter mode to DisplayText. In this mode, the Data Grid considers of only values visible to end-users and filtering popup menus are filled with non-repeating items (right figure).
An automatic filtering row has its own dedicated property for changing the filtering mode: WindowsFormsSettings.ColumnAutoFilterMode.
Customize Filtering Dropdown Menus
ColumnView.ShowFilterPopupExcel, ExcelFilterOptions
Allow you to customize Excel-style menus. The sample below demonstrates how to remove the “Is Null” and “Is Not Null” items from them.
//affects one specific Data Grid
void gridView1_ShowFilterPopupExcel(object sender, FilterPopupExcelEventArgs e) {
e.ShowNulls = false;
}
//affects all Data Grid controls within an application
DevExpress.Utils.Filtering.ExcelFilterOptions.Default.ShowNulls = false;
ColumnView.FilterPopupExcelData
Allows you to remove and modify items within Excel-styled filter dropdowns, as well as add your own custom items that will apply specific filtering conditions. The code snippet below adds custom filtering options for the “Modification” and “MPG City” columns.
void gridView_FilterPopupExcelData(object sender, Views.Grid.FilterPopupExcelDataEventArgs e) {
if(e.Column == bcModification) {
e.AddFilter("Automatic Transmission (6-speed)", "Contains([" + e.Column.FieldName + "], '6A')");
e.AddFilter("Automatic Transmission (8-speed)", "Contains([" + e.Column.FieldName + "], '8A')");
e.AddFilter("Manual Transmission (6-speed)", "Contains([" + e.Column.FieldName + "], '6M')");
e.AddFilter("Manual Transmission (7-speed)", "Contains([" + e.Column.FieldName + "], '7M')");
e.AddFilter("Variadic Transmission", "Contains([" + e.Column.FieldName + "], 'VA')");
e.AddFilter("Limited Edition", "Contains([" + e.Column.FieldName + "], 'Limited')");
}
if(e.Column == bcMPGCity) {
e.AddFilter("Fuel Economy (High)", "[" + e.Column.FieldName + "]<=15");
e.AddFilter("Fuel Economy (Medium)", "[" + e.Column.FieldName + "]>15 AND [" + e.Column.FieldName + "]<25");
e.AddFilter("Fuel Economy (Low)", "[" + e.Column.FieldName + "]>=25");
}
}
Regular dropdown menus display the “(Custom)” item. Clicking this item invokes a Filter Dialog that allows end-users to build filtering criteria from multiple simple expressions. The CustomFilterDialog event allows you to customize this dialog or completely change what clicking the “(Custom)” item does. The code sample below suppresses the Filter Dialog when end-users click “(Custom)” for the “Unit Price” column. Instead, clicking this item applies a custom filtering by records whose price is between $10 and $30.
private void gridView_CustomFilterDialog(object sender, DevExpress.XtraGrid.Views.Grid.CustomFilterDialogEventArgs e) {
if (e.Column.FieldName != "UnitPrice") return;
string filterSting = "[UnitPrice] > 10 AND [UnitPrice] < 30";
string displayText = "[UnitPrice] > $10.00 AND [UnitPrice] < $30.00";
ColumnFilterInfo columnFilter = new ColumnFilterInfo(filterSting, displayText);
e.FilterInfo = columnFilter;
e.Handled = true;
}
ColumnView.ShowFilterPopupListBox
Handle this event to modify regular dropdown list menus. The sample below demonstrates how to add the “Important” item to a filtering menu invoked for the “Priority” column. Applying this custom filter will select records with Medium and High priorities.
private void gridView_ShowFilterPopupListBox(object sender, FilterPopupListBoxEventArgs e) {
if (e.Column.FieldName != "Priority") return;
// Get the index of the first value item.
int index;
for (index = 0; index < e.ComboBox.Items.Count; index++) {
object item = e.ComboBox.Items[index];
if (item is FilterItem) {
object itemValue = ((FilterItem)item).Value;
if (itemValue is FilterItem || itemValue is ColumnFilterInfo) continue;
break;
}
}
// Create a filter criterion to select records with the "High" and "Medium" priorities.
string filterString = "([Priority] == 3 OR [Priority] == 2)";
string filterDisplayText = "[Priority] == High OR [Priority] == Medium";
ColumnFilterInfo filterInfo = new ColumnFilterInfo(filterString, filterDisplayText);
e.ComboBox.Items.Insert(index, new FilterItem("Important", filterInfo));
}
ColumnView.ShowFilterPopupCheckedListBox
Allows you to customize checked list box menus. The code below removes items that start with “Help” and disables items that start with “Data”.
private void gridView_ShowFilterPopupCheckedListBox(object sender, FilterPopupCheckedListBoxEventArgs e) {
if (e.Column.FieldName != "Name") return;
for (int i = 0; i < e.CheckedComboBox.Items.Count; i++) {
CheckedListBoxItem item = e.CheckedComboBox.Items[i];
string itemValue = (string)(item.Value as FilterItem).Value;
// Disable checked items that start with "Data".
if (itemValue.StartsWith("Data"))
e.CheckedComboBox.Items[i].Enabled = false;
// Remove checked items that start with "Help".
if (itemValue.StartsWith("Help")) {
e.CheckedComboBox.Items.Remove(item);
i--;
}
}
}
ColumnView.ShowFilterPopupDate
Allows you to customize calendar filtering menus. The code sample below adds a custom “Last Year” filtering item to calendar menus.
private void gridView1_ShowFilterPopupDate(object sender, FilterPopupDateEventArgs e) {
if(e.Column.FieldName != "Date") return;
e.List.Clear();
DateTime firstDayOfThisYear = new DateTime(DateTime.Today.Year, 1, 1);
DateTime firstDayOfLastYear = firstDayOfThisYear.AddYears(-1);
CriteriaOperator lastYear = new BinaryOperator(
e.Column.FieldName, firstDayOfLastYear, BinaryOperatorType.GreaterOrEqual);
CriteriaOperator thisYear = new BinaryOperator(
e.Column.FieldName, firstDayOfThisYear, BinaryOperatorType.Less);
CriteriaOperator crit = new GroupOperator(GroupOperatorType.And, lastYear, thisYear);
e.List.Add(new DevExpress.XtraEditors.FilterDateElement("Last Year", "", crit));
}
Automatic Filtering Row API
GridOptionsFilter.AllowAutoFilterConditionChange
Specifies whether or not the auto-filtering row’s condition selector is visible.
OptionsColumnFilter.AutoFilterCondition
Specifies the filter criteria operator for this column (“Equals”, “Like”, “Greater”, etc.).
OptionsColumnFilter.ImmediateUpdateAutoFilter
If this setting is enabled, the Data Grid filters its records immediately after end-users modify the filter condition. Otherwise, filtering will not be applied until a user presses the Enter key or moves focus to another cell.
GridView.ShowAutoFilterConditionsMenu
Call this method from code to invoke the automatic filtering row popup menu for the specific column.
GridView.SetAutoFilterValue, GridView.GetAutoFilterValue
Methods that allow you to retrieve the currently applied auto-filtering row condition and apply a new one from code.
GridView.ResetAutoFilterConditions
Clears all data filtering applied through the automatic filtering row.
Filtering Attributes Support
In code-first data sources, you can mark specific properties with DevExpress filtering attributes to change editors that appear inside Excel-style filtering menus. For example, in the code sample below the integer “ID” property would normally be recognized not as an identifier, but as a numeric property. Excel-style filters utilize two text boxes and a range trackbar to filter numeric properties. Declaring the FilterLookup attribute before this property allows you to change these default editors with a checked list box control.
using DevExpress.Utils.Filtering;
public class Customer {
[FilterLookup]
public Int32 ID { get; set; }
//. . .
}
Searching for Records
Allows you to manually start a search.
ColumnViewOptionsFind.FindFilterColumns
By default, this property is set to “*”. In this instance, searches are performed against all visible columns/card fields. To search against specific columns/fields, assign the corresponding field names to the FindFilterColumns property, delimiting them with the “;” character. This property also allows you to limit the search columns in order to prevent highlighting text in non-searchable columns.
ColumnView.LocateByValue, ColumnView.LocateByDisplayText
Utilize these methods to find rows that own cells with required values and\or display text.
//find rows by cell values
private void btn_LocateRow_ItemClick(object sender, ItemClickEventArgs e) {
int rowHandle = gridView.LocateByValue(gridView.FocusedRowHandle + 1, gridView.Columns["Category"], 1);
gridView.FocusedRowHandle = rowHandle;
}
//find rows by cell display text
private void btn_LocateRow_ItemClick(object sender, ItemClickEventArgs e) {
int rowHandle = gridView.LocateByDisplayText(gridView.FocusedRowHandle + 1, gridView.Columns["Category"], "Beverages");
gridView.FocusedRowHandle = rowHandle;
}