Filter records in the section using a macro and DataService

Level: advanced

Example

Create a console application that uses DataService to read records from the Contacts section with the following columns:

• Id;
• Full name;
• Birth date.

The data must be filtered, so that only contacts who were born in 1992 are shown.

Example implementation algorithm

1. Create and set up a C# application project that reads records

To execute this step, read the records.

The result of implementing an instance of query class for reading records:

SelectQuery()

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

2. Add a filter implementation with macros

To filter the data, create an instance of the Filters collection class, fill out the properties with corresponding values, and then pass the instance link to the Filters property of the query class created on the previous step.

An example of filter collection class implementation

// Query filters.
var selectFilters = new Filters()
{
    // Filter type – group.
    FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.FilterGroup,
    // Filter collection.
    Items = new Dictionary<string, Filter>
    {
        
        // Filter by year of birth.
        {
            // Key.
            "FilterYear",
            // Value.
            new Filter
            {
                // Filter type – comparison.
                FilterType = Terrasoft.Nui.ServiceModel.DataContract.FilterType.CompareFilter,
                // Comparison type – equal.
                ComparisonType = FilterComparisonType.Equal,
                // Expression to check.
                LeftExpression = new BaseExpression()
                {
                    // Expression type – schema column.
                    ExpressionType = EntitySchemaQueryExpressionType.SchemaColumn,
                    // Path to schema.
                    ColumnPath = "BirthDate"
                },
                // Expression with which the checked value is compared.
                RightExpression = new BaseExpression
                {
                    // Expression type – function.
                    ExpressionType = EntitySchemaQueryExpressionType.Function,
                    // Function type – macro.
                    FunctionType = FunctionType.Macros,
                    // Macro type – year.
                    MacrosType = EntitySchemaQueryMacrosType.Year,
                    // Function argument.
                    FunctionArgument = new BaseExpression
                    {
                        // Type of expression that determines the argument – parameter.
                        ExpressionType = EntitySchemaQueryExpressionType.Parameter,
                        // Parameter initialization.
                        Parameter = new Parameter
                        {
                            // Parameter type – integer.
                            DataValueType = DataValueType.Integer,
                            // Parameter value.
                            Value = "1992"
                        }
                    }
                }
            }
        }
    }
};
// Adding filters to query.
selectQuery.Filters = selectFilters;
// Serialization of select query class instance in a JSON string.
var json = new JavaScriptSerializer().Serialize(selectQuery);

The collection contains a single filter with the FilterYear key. Because only those records that have their year of birth equal to 1992 must be selected from the collection, the type of filter is set as a comparison filter. The type of comparison is set as an equality of values. As a verified expression, set the Date of birth column. Specify the macro function as the expression to compare with.

In this case using a macro is optimal because the birth date is stored in the database in YYYY-MM-DD format. The macro automatically determines the year value, so the developer does not need to write additional program code.

Because the EntitySchemaQueryMacrosType.Year macro is parametric, the FunctionArgument property must be initialized and assigned a link to an instance of the BaseExpressionclass. In it, the integer parameter with value "1992" is defined.

---

Resources

Example implementation (GitHub)