Filter and sort

7 min

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 filter pattern, which evaluates to a Boolean value and is tested for each element in the collection. Elements that fail the 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.

Create 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)

Use logical blocks

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

Example: Return 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, included in BF, 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, create a class that implements the ITemplateSource interface. Also, implement the GetValue method.

Use template value in filters

Example: Using template value in filter expression

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

Sort

Use the SortingElement class to define sorting element.

Example: Sorting output collection by IsProject columns, ascending

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