Filter records in the section using DataService

Level: advanced

Example

Create a console application that will read the following data from the Contacts section using the DataService service:

• Id;
• Full name;
• Number of activities is an aggregating column that shows the number of activities of this contact.

It is necessary to filter the data so that only those contacts whose number of activities is in the range of 1 to 3, and the Full name column value starting with "H" are read.

Example implementation algorithm

1. Create and configure a C# console application project that reads records

To perform this step, you must perform the example of reading records in a third-party application.

The result of the query class implementation instance to read the records with the columns in an abbreviated form:

SelectQuery()

// Query class instance.
var selectQuery = new SelectQuery ()
{
    // Root schema name.
    RootSchemaName = "Contact",
    // Adding columns to query.
    Columns = new SelectQueryColumns ()
    {
        // Column collection.
        Items = new Dictionary <string, SelectQueryColumn> ()
        {
            // Column [Full name].
            {
                // Key.
                "Name",
                // Value.
                new SelectQueryColumn ()
                {
                    // An expression that specifies the column type.
                    Expression = new ColumnExpression ()
                    {
                        // Expression type - schema column.
                        ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                        // Path to the column.
                        ColumnPath = "Name"
                    }
                }
            },
            // Column [Number of activities].
            {
                "ActivitiesCount",
                new SelectQueryColumn ()
                {
                    Expression = new ColumnExpression ()
                    {
                        // Expression - subquery.
                        ExpressionType = EntitySchemaQueryExpressionType.SubQuery,
                        // Path to the column relative to the root schema.
                        ColumnPath = "[Activity: Contact] .Id",
                        // Function type - aggregating.
                        FunctionType = FunctionType.Aggregation,
                        // Aggregation type - number.
                        AggregationType = AggregationType.Count
                    }
                }
            }
        }
    }
};

2. Add filter implementation

In order to filter data, you must create an instance of the Filters collection class instance, fill in the necessary properties, and then pass the link to this instance to the Filters property of the query class instance that you created in the previous step.

Filter collection class implementation example

// Query filters.
var selectFilters = new Filters ()
{
    // Filter Type - group.
    FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.FilterGroup,
    // Filter collection.
    Items = new Dictionary <string, Filter>
    {
// Filter Implementation.
    }
};
// Adding filter to query.
selectQuery.Filters = selectFilters;
// Query class instance serialization to read data from the JSON string.
var json = new JavaScriptSerializer () Serialize (selectQuery).;

The Items property must contain the key-value type collection. The key is a string containing the filter name, and the value is an instance of the Filter class that contains a direct implementation of the filter.

To implement a filter that selects only those contacts that have a number of activities within a range of 1 to 3, you must add the following instance to the collection of filters:

Example of the instance to the collection of filters

// Filtration by activity.
{
    // Key.
    "FilterActivities",
    // Value.
    new Filter
    {
        // Filter type - range filter.
        FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.Between,
        // Comparison type - range.
        ComparisonType = FilterComparisonType.Between,
        // An expression to be tested.
        LeftExpression = new BaseExpression ()
        {
            // Expression type - subquery.
            ExpressionType = EntitySchemaQueryExpressionType.SubQuery,
            // Path to the column relative to the root schema.
            ColumnPath = "[Activity: Contact] .Id",
            // Function type - aggregating.
            FunctionType = FunctionType.Aggregation,
            // Aggregation type - number.
            AggregationType = AggregationType.Count
        }
        // Filter range final expression.
        RightGreaterExpression = new BaseExpression ()
        {
            // Expression type - parameter.
            ExpressionType = EntitySchemaQueryExpressionType.Parameter,
            // Expression parameter.
            Parameter = new Parameter ()
            {
                // Parameter data type - integer.
                DataValueType = DataValueType.Integer,
                // Parameter value.
                Value = 3
            }
        }
        // Filter range initial expression.
        RightLessExpression = new BaseExpression ()
        {
            ExpressionType = EntitySchemaQueryExpressionType.Parameter,
            Parameter = new Parameter ()
            {
                DataValueType = DataValueType.Integer,
                Value = 1
            }
        }
    }
}

Add the following instance to the filter collection to filter contact records where the Full name column value begins with "H":

Example of the instance to the filter collection

// Filtering by name.
{
    // Key.
    "FilterName",
    // Value.
    new Filter
    {
        // Filter type - comparison filter.
        FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,
        // Comparison type - starts with an expression.
        ComparisonType = FilterComparisonType.StartWith,
        // Expression to be tested.
        LeftExpression = new BaseExpression ()
        {
            // Expression type - schema column.
            ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
            // Path to the column.
            ColumnPath = "Name"
        }
        // Filtration expression.
        RightExpression = new BaseExpression ()
        {
            // Expression type - parameter.
            ExpressionType = EntitySchemaQueryExpressionType.Parameter,
             // Expression parameter.
            Parameter = new Parameter ()
            {
                // Parameter data type - text.
                DataValueType = DataValueType.Text,
                // Parameter value.
                Value = "CH"
            }
        }
    }
}

---

Resources

GitHub (example implementation)