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 will be omitted from the result collection. The sorting is analogous to the SQL ORDER BY clause. The sorting element contains field name and direction.

All classes referred to here are available in the Mediachase.BusinessFoundation.Data namespace.

Creating simple filters

Example: Returns all elements where the key is 1

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

Example: Returns all elements where key contains keyword

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

Example: Returns all 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 all elements where Extrension 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} would be converted to the current date.

Call AddSource of the TemplateResolver class passing template source name and template source class, to register a new template source.

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

ntervalFilterElement 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);