General information

Purpose of the EntitySchemaQuery. Difference between the “Select” and “EntitySchemaQuery”

EntitySchemaQuery – is a high level class whose purpose is to build data select queries to database. To execute the database queries in bpm’online the Select class is also used. There is a number of principal differences between the Select and EntitySchemaQuery classes.

The Select class is the standard SQL instruction SELECT, which enables selecting one or more lines or columns from one or more tables in the database. The query is built “as is”: columns are added to the query, the data sources are specified, filters and conditions are applied. The results of the query execution are returned as an IDataReader instance. Below is the example of using the Select class for retrieving a set of contacts (Example 1).

Using directives should be added to the project for successful compilation of parameters below: using System; using System.Collections.Generic; using System.Collections.ObjectModel; using System.Data; using Terrasoft.Common; using Terrasoft.Core; using Terrasoft.Core.DB; using Terrasoft.Core.Entities;

Example 1

// Creation of query instance, adding of columns and data source to query.
Select selectQuery = new Select(UserConnection)
                    .Column("Id")
                    .Column("Name")
                    .From("Contact");
// Execution of query to database and receipt of resulting data set.
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection())
{
    using (IDataReader reader = selectQuery.ExecuteReader(dbExecutor))
    {
        while (reader.Read())
        {
            // Processing of query results.
        }
    }
} 

EntitySchemaQuery class is a functionality that is similar to Select and extended with additional functions on access right control and bpm'online repository (cache) handling. EntitySchemaQuery is a functionally extended add-in of Select class. All additional properties and parameters of EntitySchemaQuery are projected onto Select instance that returns resultant data array.

GetSelectQuery() method returns Select instance, associated with definite query EntitySchemaQuery (Example 2).

Example 2

// Creation of query example EntitySchemaQuery.
EntitySchemaQuery esq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "SomeSchema");
esq.AddColumn("SomeColumn");

// Receipt of Select instance, associated with created query EntitySchemaQuery.
Select selectEsq = esq.GetSelectQuery(UserConnection); 

Result of execution of query EntitySchemaQuery is a collection of bpm'online entities. i.e. collection of instances of the Entity class (EntityCollection instance). Each Entity instance in a collection is a string of data array, returned by query. You can get query results in the following way (example 3):

• Get entire resultant data array by calling method GetEntityCollection.
• Get definite string of data array for set primary key by calling GetEntity.

Example 3

// Creation of query in City schema, adding of Name column to query.
var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
esqResult.AddColumn("Name");

// Execution of query to database and getting of all resultant data collection.
var entities = esqResult.GetEntityCollection(UserConnection);

// Execution of query to database and getting object with set identifier.
var entity = esqResult.GetEntity(UserConnection, new Guid("100B6B13-E8BB-DF11-B00F-001D60E938C6"));

Main features of EntitySchemaQuery are as follows:

1) Access right support

Data extraction query of EntitySchemaQuery is built in a way that considers rights of current user. In other words, only data that are accessed by current user in accordance with its rights will get into resultant array. This is ensured through application of additional filters (conditions) upon formation of resultant query to database. You can adjust conditions of application of rights to linked tables, being available in query (that joint query by JOIN sentence), additionally for EntitySchemaQuery. These conditions are determined by the value of JoinRightState property of instance of EntitySchemaQuery. Conditions for application of rights to linked query tables are described in details below.

2) Caching mechanism

Mechanism for handling of repository (bpm'online cache or random repository, determined by the user) is implemented in EntitySchemaQuery). Cache handling optimizes operation effectiveness through access to cached query results without an additional query to the database. Upon execution of query EntitySchemaQuery the data, received from server database, are placed in cache that is determined by Cache property with key that is set by CacheItemName. Bpm'online cache of session level (data are available only in session of current user) with local data storage is the cache of EntitySchemaQuery by default. In general, a random repository, implementing ICacheStore can be used as query cache. Example of handling of query cache EntitySchemaQuery is represented below (Example 4).

Example 4

// Creation of query to City schema, adding Name column to query.
var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
esqResult.AddColumn("Name");

// Determination of the key, under which query execution results will be stored in cache. 
// BPMonlin cache of session level with local data cashing ( since Cache property of the object is not determined) 
// is used as cache.
esqResult.CacheItemName = "EsqResultItem";

// Collection, to which query execution results will be added.
var esqCityNames = new Collection<string>();

//Collection, to which cached query execution results will be added.
var cachedEsqCityNames = new Collection<string>();

// Execution of cache to database and getting resultant collections of objects. 
// Query results will be placed in cache after completion of this operation.
var entities = esqResult.GetEntityCollection(UserConnection);

//Processing of query execution results and filling of esqCityNames collection.
foreach (var entity in entities)
{
    esqCityNames.Add(entity.GetTypedColumnValue<string>("Name"));
}

// Getting of link to esqResult query cache on the basis of CacheItemName key in the form data table in memory.
var esqCacheStore = esqResult.Cache[esqResult.CacheItemName] as DataTable;

// Filling of CachedEsqCityNames collection with values from query cache.
if (esqCacheStore != null)
{
    foreach (DataRow row in esqCacheStore.Rows)
    {
        cachedEsqCityNames.Add(row[0].ToString());
    }
} 

3) Additional query settings

You can set additional settings that determine parameters for page-by-page output of query execution results and also parameters of building of hierarchical query for EntitySchemaQuery. EntitySchemaQueryOptions is designed for these purposes. Properties of this class determine the following:

• HierarchicalColumnName – column name, used for building of hierarchical query;
• HierarchicalColumnValue – initial value of hierarchical column, on the basis of which hierarchy will be built;
• HierarchicalMaxDepth – maximum nesting level of hierarchical query;
• PageableConditionValues – value of page-by-page output conditions;
• PageableDirection – direction of page-by-page output conditions;
• PageableRowCount – umber of page records of resultant data array, returned by query.

One and the same EntitySchemaQueryOptions instance can be used for getting of different query execution results by transferring it to GetEntityCollection() of corresponding query (example 5) as parameter.

Example 5

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

// Creation of query with Country root schema.
var esqCountries = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Country");
esqCountries.AddColumn("Name");

// Creation of setting instance for returning the first 5 settings through query.
var esqOptions = new EntitySchemaQueryOptions()
{
    PageableDirection = PageableSelectDirection.First,
    PageableRowCount = 5,
    PageableConditionValues = new Dictionary<string, object>()
};

// Getting Cities collection that will contain the first 5 cities of resultant data array.
var cities = esqCities.GetEntityCollection(UserConnection, esqOptions);

// Getting of countries collection that will contain the first 5 countries o f resultant data array.
var countries = esqCountries.GetEntityCollection(UserConnection, esqOptions); 

Definition of root schema. Creation of paths to columns against root schema. Examples.

The starting point of mechanisms for creating of EntitySchemaQuery is a root schema.

Root schema is a schema (table in database), whose paths are created in all columns in query, including columns of added tables.

Upon creation of paths, the feedback principle is applied to columns. The name of a random column, added to a query, can be built in the form of a chain of interconnected links. Each link represents the "context" of definite schema that is connected to a previous one on an external key (figure 1).

Figure 1. Interconnections of schemas on keys

In a common case, the format for building of a user column name built from N schema can be represented in the following form:

[Context of schema 1 ].[...].[Context of schema N].[Name of _ column]

In order to add column from a random table to a query on feedbacks, build the path to this column correctly. Different variants for adding columns to query and examples of name formation for column in each variant are described below. A city schema is used as a root schema for all examples, listed below.

1) Root schema column

In this case, the column name is built as [Column name in root schema].

• Example: column with city name
• Column name: Name
• EntitySchemaQuery creation example that returns values of this column:

// Creation of EntitySchemaQuery query instance with "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding to the column query with city name.
esqQuery.AddColumn("Name");

// Getting text if resultant sql-query.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText(); 

• Resultant sql-query (MS SQL):

SELECT
[City].[Name] [Name]
FROM
[dbo].[City] [City]

2) Schema column, to which lookup column of current schema refers

The column name is built on the basis of [Lookup column name].[Name of column of schema, to which lookup refers].

Joining the operator (Left Outer Join by default) will join the Country schema in a resulting query to the City root schema. Joining a condition (On condition of JOIN operator) is formed by the following principle :

[Name of joinable schema].[Id] = [Name of root schema].[Name of column that refers to joinable schema +Id]

• Example: column with name of the country, to which the city belongs]
• Column name: Country.Name
• EntitySchemaQuery creation example that returns values of this column:

//Creation of EntitySchemaQuery query instance with "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding of column with country name, to which the city belongs, to query.
esqQuery.AddColumn("Country.Name");

// Getting text of resultant sql-query.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText();  

• Resultant sql-query (MS SQL):

SELECT
[Country].[Name] [Country.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])

In a common case, you can continue to build the feedback chain.

• Example: contact name that has added country of definite city
• Column name: Country.CreatedBy.Name
• EntitySchemaQuery creation example that returns values of this column:

// Creation of the example of EntitySchemaQuery query with "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding column with contact name, which has added country of definite city, to query.
esqQuery.AddColumn("Country.CreatedBy.Name");

// Getting text of resultant sql-query.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText();   

• Resultant sql-query (MS SQL):

SELECT
[CreatedBy].[Name] [CreatedBy.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])
LEFT OUTER JOIN [dbo].[Contact] [CreatedBy] ON ([CreatedBy].[Id] = [Country].[CreatedById])

3) Schema column on random external key

Column name is built in accordance with the following principle [Name_of_joinable_schema:Name_of_column_for_linking_of_joinable_schema:Name of _column_ for_connection_of_current_schema].

If the ID column is used as a linking column in the current schema, it can be omitted, i.e. the column name will have the following view:

[Name_of_joinable_schema:Name_of_column_for_linking_of_joinable_schema] .

• Example: column with the name of the contact that has an added city
• Column name: [Contact:Id:CreatedBy].Name
• Example of creation of EntitySchemaQuery of returning value of this column:

//Creation of instance of EntitySchemaQuery query with "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding column with name of the contact that has added city, to query.
esqQuery.AddColumn("[Contact:Id:CreatedBy].Name");

// Getting text of resultant sql-query.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText(); 

• Resultant sql-query (MS SQL):

SELECT
[Contact].[Name] [Contact.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Contact] [Contact] ON ([Contact].[Id] = [City].[CreatedById])

In general, you can build a column name on the basis of feedback chains of random length. The example below shows an alternative variant of name building for a column with the name of the contact that has added a country of a definite city (see clause 2).

• Example: column with name of the contact that has added country of definite city
• Column name: Country.[Contact: Id:CreatedBy].Name
• Example for creation of EntitySchemaQuery that retuns values of this column:

// Creation of EntitySchemaQuery instance with "City" root schema.
var esqQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Adding column with contact name, which has added country of definite city, to query.
esqQuery.AddColumn("Country.[Contact:Id:CreatedBy].Name");

// Getting text of resultant sql-query.
string esqSqlText = esqQuery.GetSelectQuery(UserConnection).GetSqlText();

• Resultant sql-query (MS SQL):

SELECT
[Contact].[Name] [Contact.Name]
FROM
[dbo].[City] [City]
LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])
LEFT OUTER JOIN [dbo].[Contact] [Contact] ON ([Contact].[Id] = [Country].[CreatedById])

Adding columns to query

The column of the EntitySchemaQuery query is an instance of EntitySchemaQueryColumn class. You can indicate the main characteristics of a column instance in its properties: titles, view values, checkboxes, sorting order and position etc. A full list of properties and methods of EntitySchemaQueryColumn class is shown in the corresponding section of the SDK class description.

The AddColumn() method ) that returns an instance of a column, added to a query, is designed for adding columns to a query. The column name related to the root schema is formed in the AddColumn() method in accordance with above-mentioned rules. This method has some overloads. This allows adding columns with different parameters (table 1) to a query.

Table 1. The list of overloads of the AddColumn() method

Specific of use of jointed tables

JOIN types of jointed tables

If a column from a schema that differs from the root schema is added, this schema will be added to the query by the JOIN operator. In this case, LEFT OUTER join type is applied by default. EntitySchemaQueryindicates the type of joining of this schema to the query upon adding the column of a non-root schema. For this purpose you should indicate the column name in the following form:

[Special join symbol][Column name ]

EntitySchemaQuery supports the following types of joins, to which symbols, shown in table 2, correspond.

Table 2. JOIN types of jointed schemas

Examples of adds to column query with use of different schema joining types are shown below.

Example 6

// Creation of instance of query with City root schema.
var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// Country schema with join type LEFT OUTER JOIN will be added to query.
esqResult.AddColumn("Country.Name");

// Country schema with join type INNER JOIN will be added to query.
esqResult.AddColumn("=Country.Name");

// Two schemas will be added to query:
// 1) Country schema with join type LEFT OUTER JOIN ;
// 2) Contact schema with join type RIGHT OUTER JOIN.
esqResult.AddColumn(">Country.<CreatedBy.Name");

// Text of resultant sql-query (MS SQL):

// SELECT
//            [Country].[Name] [Country.Name],
//            [Country1].[Name] [Country1.Name],
//            [CreatedBy].[Name] [CreatedBy.Name]
// FROM
//            [dbo].[City] [City]
//            LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])
//            INNER JOIN [dbo].[Country] [Country1] ON ([Country1].[Id] = [City].[CountryId])
//            LEFT OUTER JOIN [dbo].[Country] [Country2] ON ([Country2].[Id] = [City].[CountryId])
//            RIGHT OUTER JOIN [dbo].[Contact] [CreatedBy] ON ([CreatedBy].[Id] = [Country2].[CreatedById])  

Application of access rights to jointed schemas

If the root schema of a query is managed by records and there are joinable schemas in the query, access rights of a current user can be applied to these records. Table 3 shows all possible variants of the application of access rights to joinable schemas. These values correspond to enumeration members Terrasoft.Core.DB.QueryJoinRightLevel.

Table 3. Variants of application of access rights to joinable query schemas

The procedure of changing access rights is determined the value of the JoinRightState property of query. The default value of this property is determined by the system setting QueryJoinRightLevel that accepts values in accordance with table 3. If the value of this system setting is not set, its default value is taken to be equal to EnabledForAdditionalColumns.

EntitySchemaQuery filters handling

Notion of EntitySchemaQuery filter and its structure

A filter is a set of conditions, applied to views of a data query. According to SQL terms, a filter is a separate predicate (condition) of the WHERE operator.

Any filter, as a query condition, has its structure (figure 2).

Figure 2. Structure of EntitySchemQuery filter

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

Main components of EntitySchemaQuery filter:

• AggregationType is a type of aggregation function that is expressed in the left side of the condition. It is optional component of the filter. It is set by enumeration values FilterAggregationType.
• LeftExpression is an expression in the left part of the filter. It is set by type instance EntitySchemaQueryExpression.
• LeftExpressionColumnPath is a path to column that contains expression of the left part of the filter. It is set by the string value.
• ComparisionType is a type of comparison of expressions in the filter. It is assigned by enumeration value FilterComparisonType.
• RightExpression is an expression in the right part of the filter. It is set by type instance EntitySchemaQueryExpression.
• RightExpressionColumnPath is a path to column that contains expression of the right part of the filter. It is set by the string value.
• Macros is a macros that returns expression for the right part of the filter. It is assigned enumeration value EntitySchemaQueryMacrosType.
• MacrosValue is a value that is assigned to Macros as parameter. It is an optional parameter. It is assigned values of different types depending on the type of called macros.

Creation and application of filters in EntitySchemaQuery

The CreateFilter() method that returns instance of type EntitySchemaQueryFilter is used for creation of simple filter (figure 2) in EntitySchemaQuery . Some reloads are implemented for this method in the EntitySchemaQuery. This allows creation of filters with different initial parameters. Full list of reloads of the CreateFilter() method with examples of their implementation is available in bpm'online SDK.

In spite of simple filters methods for creation of special type filters are implemented in EntitySchemaQuery (table 4).

Table 4. EntitySchemaQuery methods for creation of special type filters

EntitySchemaQuery has Filters property ) that is represented by collection of filters of given query (instance of EntitySchemaQueryFilterCollection class that, in its turn, is a classical typified collection of IEntitySchemaQueryFilterItem) items. In order to add filter to query it is necessary to take the following actions:

• create instance of filter for given query (CreateFilter() methods, methods for creation of special type filters);
• add created filter instance to query filter collection (Add() method of collection).

All filters, added to Filters collection, are interconnected through AND logic operation by default. LogicalOperation property of Filters collection allows user to indicate logic operation, which should be used for joining filters. The property takes values from the list LogicalOperationStrict.

The possibility for control of filters, used in building of resultant data array, is implemented in EntitySchemaQuery queries. Each item of Filters collection has IsEnabled property that determines whether this item takes part in building of a resultant query (true means that it take part and false means that it doesn't take part). A similar property IsEnabled is also determined for the entire collection of Filters. Set this property to “false” to deactivate filtration for a query. In this case, a collection of query filters will remain unchanged. Therefore, if a query filter collection is created initially, you can use different combinations for filtering of this query in the future while not introducing changes directly into a collection. An example of controlling filters in a query is shown below (example 7).

Example 7

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

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

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

// Filters will be joined in filter collection by OR logic operator. 
esqCities.Filters.LogicalOperation = LogicalOperationStrict.Or;

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

// Objects, i.e. query results, filtered by two filters, will be included into this collection.
var entities = esqCities.GetEntityCollection(UserConnection);

// It is specified for the second filter that it will be used in building of resultant query.
// In this case the filter is not deleted for query filter collection.
esqSecondFilter.IsEnabled = false;

// It updates Select instance, associated with query in accordance with actual set of filters.    
esqCities.ResetSelectQuery();

// The objects, namely query results , filtered only by the first filter, will be included into this collection.
var entities1 = esqCities.GetEntityCollection(UserConnection); 

Column paths in EntitySchemaQuery filters are formed in accordance with common rules for building paths to columns against root schema (as described above).

The example below shows how to select activities results for a definite category from the ActivityCategory root schema based on feedback. For the purpose of demonstration of use cases of feedbacks for columns in the columns of filters, the activity identifier column, which is built on feedbacks through ActivityCategoryResultEntry schema, will be used for selection of a an activity, instead of the Id column of the root schema.

Example 8

// Creation of query instance with ActivityCategory root schema.
var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "ActivityCategory");

// Adding the activity result column to the request on feedbacks.
esqResult.AddColumn("[ActivityCategoryResultEntry:ActivityCategory].ActivityResult");

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

// Creation of the filter example for selection of the results for definite activity category.
var filter = esqResult.CreateFilterWithParameters(FilterComparisonType.Equal,
                    "[ActivityCategoryResultEntry:ActivityCategory].ActivityCategory.Id",
                    requiredActivityCategoryId);

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

// Text of resultant sql-query (MS SQL):

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