You can restrict your query calculation to some subset of the input rows using a filter condition. Or maybe the entire purpose of your query is to select a subset of rows from a base table or another query result based on some filter condition or a range of values of a column or several columns. All this is achieved by filter conditions. There are two kinds of conditions, namely _range and _filter.

Range

The following query illustrates how to formulate range condition.

Syntax

Dim query4 As dynamic = workspace.query("range", New With { _
        Key ._base = "*", _
        Key ._range = od.UnitPrice.Eq(10) _
})

dynamic query4 = workspace.query("range", new
    {
        _base = "*",
        _range = od.UnitPrice.Eq(10)
    });

The above query selects OrderDetails rows with unit price = 10. This process is very efficient as the query traverses to the desired segment of rows using an index built internally by DataEngine and outputs just those rows with unit price = 10 without checking any other rows. In addition, several condition operations can also be applied to a column. The above query uses is Eq that is "equals". Other common operations are Gte, Lte (greater than or equals, less than or equals), Gt, Lt (greater than, less than), etc. Conditions applied to a column can be combined as follows.

_range = od.UnitPrice.Gte(10).Lte(20)

This kind of 'between' condition is still appropriate for _range, performance optimization is still possible because row scan can be constrained to scanning the from-to segment of rows. You can also specify ranges with conditions imposed on two or more columns as illustrated below.

_range = od.Discount.Eq(0) + od.UnitPrice.Gte(10).Lte(20)

Filter

Some conditions cannot be specified as _range. For example, A "not equal" or Ne condition cannot be used in a range. So, to use Ne condition, you need to apply _filter as illustrated below. 

_filter = od.Discount.Ne(0)

DataEngine conditions can also be combined with 'or', not only with 'and'. However, 'or' can only be used in a filter as illustrated below.

_filter = od.UnitPrice.Lt(10).Or().Gt(20)

There is also an And() operation that allows you to combine 'or' and 'and' in a single filter as illustrated below.

_filter = od.UnitPrice.Lt(10).Or().Gt(20).And().Lt(30)

The above query means either less than 10 or between 20 and 30.

You can have any number of Or() and And() connectives, and impose conditions on multiple columns combining them with + as illustrated below.

_filter = od.Discount.Ne(0) + od.UnitPrice.Lt(10).Or().Gt(20).Or().Gt(30)