ComponentOne FlexPivot for WinForms
DataEngine Overview / Using C1DataEngine / Filter and Range
In This Topic
    Filter and Range
    In This Topic

    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.

    Filter(_filter) Range (_range)
    _filter is more general as it can represent any condition. However, it has performance cost because it scans the entire set of input rows and checks the condition on every row. _range is a special case of filter in which DataEngine does not need to scan all rows, and hence performs faster.

    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.

    Syntax
    Copy Code
    _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.

    Syntax
    Copy Code
    _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. 

    Syntax
    Copy Code
    _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.

    Syntax
    Copy Code
    _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.

    Syntax
    Copy Code
    _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.

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