DataService. Using macros
Glossary Item Box
General provisions
During execution of DataService operations data often needs to be filtered for a certain period of time. Macros simplify such tasks and help to avoid creating unnecessary custom methods. The macros are implemented in a form of special classes that are designed for calculating typical values in query expressions, such as calculating the start and end date of the current quarter. Macros can be used only if the query expression type is a function. For more information about macro expression types, please see the DataService. Data filtering article.
Types of macros
When creating queries to DataService, both parameterized (ie requiring an argument) and non-parameterized macros can be used. Macro types that must be used in the macro expressions are defined in the EntitySchemaQueryMacrosType enumeration in the Terrasoft.Core.Entities name space. Enumeration values of macro types and their descriptions are available in table 1.
Table 1. Values of the EntitySchemaQueryMacrosType enumeration and their descriptions
Macro | Value | Description |
---|---|---|
CurrentHalfYear | 16 | Current half-year (January-June or July-December). |
CurrentHour | 21 | Current hour. |
CurrentMonth | 10 | Current month. |
CurrentQuarter | 13 | Current quarter. |
CurrentUser | 1 | Current user. |
CurrentUserContact | 2 | Contact record of the current user. |
CurrentWeek | 7 | Current week. |
CurrentYear | 19 | Current year. |
DayOfMonth | 28 | Day of month. Requires parameterization. |
DayOfWeek | 29 | Week day. Requires parameterization. |
Hour | 30 | Hour. Requires parameterization. |
HourMinute | 31 | Time. Requires parameterization. |
Month | 32 | Month. Requires parameterization. |
NextHalfYear | 17 | Next half-year (January-June or July-December). |
NextHour | 22 | Next hour. |
NextMonth | 11 | Next month. |
NextNDays | 24 | Next N days. Requires parameterization. |
NextNHours | 26 | Next N hours. Requires parameterization. |
NextQuarter | 14 | Next quarter. |
NextWeek | 8 | Next week. |
NextYear | 23 | Next year. |
None | 0 | Type of macro not defined. |
PreviousHalfYear | 15 | Previous half-year (January-June or July-December). |
PreviousHour | 20 | Previous hour. |
PreviousMonth | 9 | Previous month. |
PreviousNDays | 25 | Previous N days. Requires parameterization. |
PreviousNHours | 27 | Previous N hours. Requires parameterization. |
PreviousQuarter | 12 | Previous quarter. |
PreviousWeek | 6 | Previous week. |
PreviousYear | 18 | Previous year. |
Today | 4 | Today. |
Tomorrow | 5 | Tomorrow. |
Year | 33 | Year. Requires parameterization. |
Yesterday | 3 | Yesterday. |
Example of using macros
Case description
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.
Case realization
The complete source code for implementation of this case is available here.
Case implementation algorithm
1. Create and set up a C# application project that reads records
To execute this step, execute the record reading case covered in the "DataService. Reading records" article.
The result of implementing an instance of query class for reading records:
// 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 BaseExpression class. In it, the integer parameter with value "1992" is defined.
The complete source code for implementation of this case is available here.