Working with EntitySchemaQuery filters

Introduction

Filters are a set of conditions that are applied when displaying query data. In SQL terms, a filter is a separate predicate (condition) of the WHERE operator.

Similarly to a query condition, any filter has its own structure (Fig. 1).

Fig. 1. EntitySchemaQuery filter tructure

Filter = {[AggregationType] {<LeftExpression> | <LeftExpressionColumnPath>}
<ComparisonType>
{{<RightExpression> | {<RightExpressionColumnPath>,...}} | {<Macros>, [MacrosValue]}}
}

Main components of the EntitySchemaQuery filter:

• AggregationType – type of the aggregating function that is applied to the expression on the left side of the condition. Optional part of the filter. Set by the FilterAggregationType enumeration value.
• LeftExpression – the expression on the left side of the filter. Set by the EntitySchemaQueryExpression instance type.
• LeftExpressionColumnPath – path to the column that contains the filter's left side expression. Set by a string value.
• ComparisonType – type of expression comparison in a filter. Set by the FilterComparisonType enumeration value.
• RightExpression – expression in the filter's right side. Set by the EntitySchemaQueryExpression instance type.
• RightExpressionColumnPath – path to the column that contains the filter's right side expression. Set by a string value.
• Macros – a macro that returns an expression for the right side of the filter. Set by the EntitySchemaQueryMacrosType enumeration value.
• MacrosValue – a value, which is passed as a parameter to the Macros macro. Optional paremeter. Set by value instances of different types depending on the macro type being called.

Creating and applying filters in EntitySchemaQuery

To create a simple filter (Fig. 1) EntitySchemaQuery uses the CreateFilter() method, which returns the EntitySchemaQueryFilter. Instance type. For this method, a number of overloads are implemented in EntitySchemaQuery, which allows you to create filters with different initial parameters.

Among simple filters, there are methods in EntitySchemaQuery that are used to create special filters (table 1).

Table 1. EntitySchemaQuery methods for creating special filters

An instance of EntitySchemaQuery has a Filters property, which is a collection of filters for this query (an instance of the EntitySchemaQueryFilterCollection class, which, in turn, is the classic typed collection of IEntitySchemaQueryFilterItem items). To add a filter to the query:

• create a filter instance for the query (CreateFilter() methods, methods for creating special filters)
• add the created filter instance to the query filer collection (the Add() collection method)

All filters added to the Filters collection are combined with the logical AND operation by default. The LogicalOperation property of the Filters collection enables the user to specify the logical operation for combining filters. The property obtains its values from the LogicalOperationStrict enumeration.

In the EntitySchemaQuery queries, the ability to control the filters involved in the construction of the resulting data set is implemented. Each element in the Filters collection has the IsEnabled property which determines whether this element participates in the resulting query building process (true/false). IsEnabled is a similar property which is determined for the entire Filters collection. By setting this property to false, you can completely disable filtering for the query, while the collection of query filters will remain unchanged. Thus, initially creating a collection of query filters, you can later use various combinations to filter this query without making changes to the collection itself.

The rules of forming column paths in the EntitySchemaQuery filters are described in the following article – Root schema. Building paths to columns.

Example 1

An example of managing filters in a query.

// Creating a query instance with the "City" root schema.
var esqCities = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
esqCities.AddColumn("Name");

// Creating an instance of the first filter.
var esqFirstFilter = esqCities.CreateFilterWithParameters(FilterComparisonType.Equal, "Name", "New York");

// Creating an instance of the second filter.
var esqSecondFilter = esqCities.CreateFilterWithParameters(FilterComparisonType.Equal, "Name", "London");

// Filters in the query filter collection will be combined with the logical OR operator.
esqCities.Filters.LogicalOperation = LogicalOperationStrict.Or;

// Adding created filters to the query collection.
esqCities.Filters.Add(esqFirstFilter);
esqCities.Filters.Add(esqSecondFilter);

// This collection will include objects - query results, filtered by two filters.
var entities = esqCities.GetEntityCollection(UserConnection);

// For the second filter, it is indicated that it will not participate in the construction of the resulting query.
// At the same time, this filter is not removed from the query filter collection.
esqSecondFilter.IsEnabled = false;

// Updates the Select instance associated with the query according to the current filter set.    
esqCities.ResetSelectQuery();

// This collection will include objects - query results, filtered only by the first filter.
var entities1 = esqCities.GetEntityCollection(UserConnection); 

Example 2

This example demonstrates how to select activity results for a specific category from the [ActivityCategory] root schema.

// Creating a query instance with the ActivityCategoryResultEntry root schema.
var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "ActivityCategoryResultEntry");

// Adding a column with activity results to the query.
esqResult.AddColumn("ActivityResult");

// Determining the identifier of the activity, which will be used to select results.
var requiredActivityCategoryId = new Guid("42C74C49-58E6-DF11-971B-001D60E938C6");

// Create a filter instance to select results for a specific activity category.
var filter = esqResult.CreateFilterWithParameters(FilterComparisonType.Equal,
                    "ActivityCategory.Id",
                    requiredActivityCategoryId);

// Adding a filter to the query filter collection.
esqResult.Filters.Add(filter);

// Resulting sql-query text (MS SQL):

// SELECT
//        [ActivityCategoryResultEntry].[ActivityResultId] [ActivityResultId],
//        [ActivityResult].[Name] [ActivityResult.Name]
//    FROM
//        [dbo].[ActivityCategoryResultEntry] [ActivityCategoryResultEntry]
//        LEFT OUTER JOIN [dbo].[ActivityResult] [ActivityResult] 
//                ON ([ActivityResult].[Id] = [ActivityCategoryResultEntry].[ActivityResultId])
//    WHERE
//        EXISTS (
//                SELECT
//                    [ActivityCategory].[Id] [Id]
//                FROM
//                    [dbo].[ActivityCategory] [ActivityCategory]
//                WHERE
//                    [ActivityCategoryResultEntry].[ActivityCategoryId] = [ActivityCategory].[Id]
//                AND [ActivityCategory].[Id] = '{42C74C49-58E6-DF11-971B-001D60E938C6}')