Filtering and sorting

Business Foundation (BF) has a unified object model for filtering and sorting rows and entity objects. The filter is analogous to the SQL WHERE clause. The filter contains a pattern within it called the filter pattern. The filter pattern evaluates to a Boolean value and is tested for each element in the collection. Any elements in the collection failing the filter pattern test are omitted from the result collection. The sorting is analogous to the SQL ORDER BY clause. The sorting element contains field name and direction.

Note: By default, the top block is AND.

Classes in this topic are available in the Mediachase.BusinessFoundation.Data namespace.

Creating simple filters

Example: Returns elements where the key is 1.

FilterElement filter = FilterElement.EqualElement("Key", "1");

Example: Returns elements where key contains keyword.

FilterElement filter = new FilterElement("Key", FilterElementType.Contains, keyword)

Example: Returns elements where StartDate is between two days.

FilterElement filter = new IntervalFilterElement("StartDate", from, to)

Using logical blocks

You can create logical blocks using the OrBlockFilterElement and AndBlockFilterElement classes.

Example: Returns elements where Extension is txt or empty string.

OrBlockFilterElement orBlock = new OrBlockFilterElement(
            FilterElement.EqualElement("Extension", "txt"),
            FilterElement.EqualElement("Extension", string.Empty));

Templates

BF supports template values in the filter and sorting expressions, using the template directives (mentioned below). Also, you can use the ResolveAll and Resolve methods to resolve custom template strings in your code.

Template Directive:

{_TemplateSource_:_TemplateValue_}

For example, the string : {DateTime:Today} is converted to the current date.

To register a new template source, call AddSource of the TemplateResolver class passing template source name and template source class.

DateTimeTemplateSource is included in BF. The DateTimeTemplateSource can convert template value strings to real date time.

Supported template values: Today, TodayStart, Yesterday, YesterdayStart, ThisWeek, ThisWeekStart, ThisMonth, ThisMonthStart, LastMonth, LastMonthStart, ThisYear, ThisYearStart, LastYear, LastYearStart, TodayEnd, YesterdayEnd, ThisWeekEnd, LastWeekEnd, ThisMonthEnd, ThisYearEnd, LastMonthEnd, ThisYearEnd, LastYearEnd.

To create a custom template source, you create a class that implements the ITemplateSource interface. You should also implement the GetValue method.

Using template value in filters

Example: Using the template value in the filter expression.

IntervalFilterElement filter = new IntervalFilterElement("StartDate",
            string.Format("{{DateTime:{0}Start}}", "Today"),
            string.Format("{{DateTime:{0}End}}", "Today"));
            filter.ValueIsTemplate = true;

Sorting

You can use the SortingElement class to define sorting element.

Example: Sorting output collection by the IsProject columns, ascending.

SortingElement sorting = new SortingElement("IsProject", SortingElementType.Asc);