Creatio Raises $200M at $1.2B valuation to lead the enterprise no-code market

Documentation

Creatio development guide
PDF
This documentation is valid for Creatio version 7.15.0. We recommend using the newest version of Creatio documentation.

Retrieving information with access right control. The EntitySchemaQuery class

Glossary Item Box

Introduction

The Terrasoft.Core.Entities.EntitySchemaQuery class is used to build queries for selecting records in Creatio database tables. As a result of creating and configuring the instance of this class, the SELECT SQL-expression query to the application database will be built. You can add the needed icons, filtering and restriction conditions to the query.

EntitySchemaQuery implements a mechanism for working with the repository (Creatio cache or user-defined arbitrary repository). When EntitySchemaQuery is performed, the data retrieved from the server database are placed in cache. The query cache can be an arbitrary repository that implements the Terrasoft.Core.Store.ICacheStore interface. By default, the cache of EntitySchemaQuery is the session-level Creatio cache (only the data from the current user session are available) with local storage.

For the EntitySchemaQuery queries, you can determine additional settings that specify the parameters for paged output of query results and parameters for building hierarchical query. The Terrasoft.Core.Entities.EntitySchemaQueryOptions class is designed for this purpose.

The result of EntitySchemaQuery is the Terrasoft.Nui.ServiceModel.DataContract.EntityCollection instance (the Terrasoft.Core.Entities.Entity class instance collection). Each Entity collection instance represents a string of data returned by the query.

The Terrasoft.Core.Entities.EntitySchemaQuery class

NOTE

Use the “.NET class libraries of platform core” documentation to access the full list of the EntitySchemaQuery class methods and properties, its parent classes and the implemented interfaces.

Constructors

  • public EntitySchemaQuery(EntitySchema rootSchema) – creates a class instance, where the EntitySchema passed instance is set as a root schema. The manager of the passed-in instance of the root schema is set as the schema manager.
  • public EntitySchemaQuery(EntitySchemaManager entitySchemaManager, string sourceSchemaName) – creates a class instance with the specified EntitySchemaManager and the root schema passed as an argument.
  • public EntitySchemaQuery(EntitySchemaQuery source) – creates a class instance that is a clone of the instance passed as an argument.

Properties

Table 1. Primary properties of the EntitySchemaQuery class

Property Type Description
Cache Terrasoft.Core.Store.ICacheStore The query cache
CacheItemName string Name of the cache item
CanReadUncommitedData bool Determines whether the query results will include the data for which the transaction is not completed
Caption Terrasoft.Common.LocalizableString Header
ChunkSize int The number of strings in one chunk
Columns: Terrasoft.Core.Entities.EntitySchemaQueryColumnCollection Collection of columns of the current entity schema query
DataValueTypeManager DataValueTypeManager Manager of the data type values
EntitySchemaManager Terrasoft.Core.Entities.EntitySchemaManager Entity schema manager
Filters Terrasoft.Core.Entities.EntitySchemaQueryFilterCollection Collection of filters of the current entity schema query
HideSecurityValue bool Determines whether the values of the encrypted columns will be hidden
IgnoreDisplayValues bool Determines whether the displayed column values will be used in the query
IsDistinct bool Indicates whether duplicates in the resulting data set should be removed
IsInherited bool Indicates whether the query is inherited
JoinRightState QueryJoinRightLevel Determines the conditions for applying permissions when using related tables if the schema is managed by records
Manager Terrasoft.Core.IManager Schema manager
ManagerItem Terrasoft.Core.IManagerItem Manager element
Name string Name
ParentCollection Terrasoft.Core.Entities.EntitySchemaQueryCollection A collection of queries to which the current request to the object schema belongs
ParentEntitySchema Terrasoft.Core.Entities.EntitySchema Parent schema of the query
PrimaryQueryColumn Terrasoft.Core.Entities.EntitySchemaQueryColumn The column created from the primary column of the root schema Initialized during the first access
QueryOptimize bool Allows using query optimization
RootSchema Terrasoft.Core.Entities.EntitySchema The root schema
RowCount int Number of rows that are returned by the query
SchemaAliasPrefix string The prefix used to create schema alias
SkipRowCount int Number of rows to skip when returning the query result
UseAdminRights bool The parameter that defines whether permissions will be taken into account when constructing a data acquisition request
UseLocalization bool Determines whether localizable data will be used
UseOffsetFetchPaging bool Determines whether the per-page returning of query result is available

UseRecordDeactivation

 bool Determines whether data will be excluded from filtering

Methods

Table 2. Primary methods of the EntitySchemaQuery class

Methods Description
void AddAllSchemaColumns(bool skipSystemColumns) The object schema adds all the columns of the root schema in the column collection of the current query

EntitySchemaQueryColumn AddColumn(string columnPath, AggregationTypeStrict aggregationType, out EntitySchemaQuery subQuery)

void AddColumn(EntitySchemaQueryColumn queryColumn)

EntitySchemaQueryColumn AddColumn(string columnPath)

EntitySchemaQueryColumn AddColumn(EntitySchemaQueryFunction function)

EntitySchemaQueryColumn AddColumn(object parameterValue, DataValueType parameterDataValueType)

EntitySchemaQueryColumn AddColumn(EntitySchemaQuery subQuery)

Creates and inserts a column in the current entity schema query

Parameters:

  • columnPath – path to the schema column in relation to the root schema
  • aggregationType – the type of aggregating function. The enumeration type values of the Terrasoft.Common.AggregationTypeStrict aggregate function are passed as a parameter
  • subQuery – reference to the created subquery placed in the column
  • queryColumn – the EntitySchemaQueryColumn instance to be added to the column collection of the current query
  • Function – the EntitySchemaQueryFunction function instance
  • parameterValue – the value of the parameter added to the query as a column
  • parameterDataValueType – the type of parameter value added to the query as a column
void ClearCache() Clears the cache of the current query
static void ClearDefCache(string cacheItemName) Removes the item with the specified cacheItemName name from the cache of the query
object Clone() Creates a clone of the current EntitySchemaQuery instance
EntitySchemaQueryExpression CreateAggregationEntitySchemaExpression(string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType) Returns expression of the aggregated function with the specified aggregation type from the Terrasoft.Common.AggregationTypeStrict enumeration for the column, located at the leftExprColumnPath specified path

static EntitySchemaQueryExpression CreateParameterExpression(object parameterValue)

static EntitySchemaQueryExpression CreateParameterExpression(object parameterValue, DataValueType valueType)

static EntitySchemaQueryExpression CreateParameterExpression(object parameterValue, string displayValue, DataValueType valueType)

Creates an expression for the query parameter

Parameters:

  • parameterValue – the type of the parameter
  • valueType – the type of the parameter value
  • displayValue – the parameter displayed value

static IEnumerable CreateParameterExpressions(DataValueType valueType, params object[] parameterValues)

static IEnumerable CreateParameterExpressions(DataValueType valueType, IEnumerable<object> parameterValues)

 Creates an expression collection for the query parameters with a certain DataValueType type of data

static EntitySchemaQueryExpression CreateSchemaColumnExpression(EntitySchemaQuery parentQuery, EntitySchema rootSchema, string columnPath, bool useCoalesceFunctionForMultiLookup, bool useDisplayValue)

static EntitySchemaQueryExpression CreateSchemaColumnExpression(EntitySchema rootSchema, string columnPath, bool useCoalesceFunctionForMultiLookup)

EntitySchemaQueryExpression CreateSchemaColumnExpression(string columnPath, bool useCoalesceFunctionForMultiLookup)

Returns the expression of the entity schema column

Parameters:

  • parentQuery – the entity schema query for which the column expression is created
  • rootSchema – the root schema
  • columnPath – path to the schema column in relation to the root schema
  • useCoalesceFunctionForMultiLookup –

    indicates whether to use the COALESCE function for the column of the lookup type. Optional parameter, set to true by default

  • useDisplayValue – indicates whether to use the displayed value for the column. Optional parameter, set to false by default

IEnumerable CreateSchemaColumnExpressions(params string[] columnPaths)

IEnumerable CreateSchemaColumnExpressions(IEnumerable columnPaths, bool useCoalesceFunctionForMultiLookup)

 Returns the collection of column expressions of the entity schema query by the specified columnPaths. collection of paths to columns.
IEnumerable CreateSchemaColumnExpressionsWithoutCoalesce(params string[] columnPaths) Returns the collection of column expressions of the entity schema query by the specified array of paths to columns. If it is a column of the multilookup type, the COALESCE function does not apply to its values

static EntitySchemaQueryExpression CreateSchemaColumnQueryExpression(string columnPath, EntitySchema rootSchema, EntitySchemaColumn schemaColumn, bool useDisplayValue)

static EntitySchemaQueryExpression CreateSchemaColumnQueryExpression(string columnPath, EntitySchema rootSchema, bool useDisplayValue)

 Returns the expression of the entity schema query by the specified path to column, root schema and schema column instance. You can define which column value type to use in the expression – either the stored value or the displayed value
EntitySchemaQueryExpression CreateSubEntitySchemaExpression(string leftExprColumnPath) Returns the expression of entity schema subquery for the column located at the specified leftExprColumnPath path
EntitySchemaAggregationQueryFunction CreateAggregationFunction(AggregationTypeStrict aggregationType, string columnPath) Returns the EntitySchemaAggregationQueryFunction aggregation function instance with the specified type of aggregation from the Terrasoft.Common.AggregationTypeStrict enumeration for the column at the specified columnPath path in relation to the root schema
EntitySchemaCaseNotNullQueryFunction CreateCaseNotNullFunction(params EntitySchemaCaseNotNullQueryFunctionWhenItem[] whenItems) Returns the instance of the CASE EntitySchemaCaseNotNullQueryFunction function for the specified EntitySchemaCaseNotNullQueryFunctionWhenItem array of condition expressions
EntitySchemaCaseNotNullQueryFunctionWhenItem CreateCaseNotNullQueryFunctionWhenItem(string whenColumnPath, object thenParameterValue)

Returns an expression instance for the sql construct of the WHEN <Expression_1> IS NOT NULL THEN <Expression_2> view, where:

  • whenColumnPath – path to the column that contains the expression of the WHEN clause
  • thenParameterValue – path to the column that contains the expression of the THEN clause
EntitySchemaCastQueryFunction CreateCastFunction(string columnPath, DBDataValueType castType) Returns an instance of the CAST EntitySchemaCastQueryFunction function for the column expression located at the specified columnPath path relative to the root schema and the specified DBDataValueType target data type

EntitySchemaCoalesceQueryFunction CreateCoalesceFunction(params string[] columnPaths)

static EntitySchemaCoalesceQueryFunction CreateCoalesceFunction(EntitySchemaQuery parentQuery, EntitySchema rootSchema, params string[] columnPaths)

static EntitySchemaCoalesceQueryFunction CreateCoalesceFunction(EntitySchema rootSchema, params string[] columnPaths)

Returns the function instance returning the first expression that is other than null from the list of arguments for the specified columns

Parameters:

  • columnPaths – array of paths to columns in relation to the root schema
  • parentQuery – query to the entity schema, for which the function instance is created
  • rootSchema – the root schema
EntitySchemaConcatQueryFunction CreateConcatFunction(params EntitySchemaQueryExpression[] expressions) Returns a function instance for generating a string that is the result of combining the string values of function arguments for the specified EntitySchemaQueryExpression array of expressions
EntitySchemaDatePartQueryFunction CreateDatePartFunction(EntitySchemaDatePartQueryFunctionInterval interval, string columnPath) Returns the DATEPART instance of the EntitySchemaDatePartQueryFunction function that determines the date interval specified by the EntitySchemaDatePartQueryFunctionInterval enumeration (month, day, hour, year, week day...) for the value of column located at the specified path in relation to the root schema
EntitySchemaDatePartQueryFunction CreateDayFunction(string columnPath) Returns an instance of the EntitySchemaDatePartQueryFunction function that determines the [Day] date range for a column value located at the specified path in relation to the root schema
EntitySchemaDatePartQueryFunction CreateHourFunction(string columnPath) Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the [Hour] date for a column value located at the specified path in relation to the root schema
EntitySchemaDatePartQueryFunction CreateHourMinuteFunction(string columnPath) Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the [Minute] date for a column value located at the specified path in relation to the root schema
EntitySchemaDatePartQueryFunction CreateMonthFunction(string columnPath) Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the [Month] date for a column value located at the specified path in relation to the root schema

EntitySchemaDatePartQueryFunction CreateWeekdayFunction(string columnPath)

Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the [Week day] date for a column value located at the specified path in relation to the root schema
EntitySchemaDatePartQueryFunction CreateWeekFunction(string columnPath) Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the [Week] date for a column value located at the specified path in relation to the root schema
EntitySchemaDatePartQueryFunction CreateYearFunction(string columnPath) Returns an instance of the EntitySchemaDatePartQueryFunction function that returns a part of the [Year] date for a column value located at the specified path in relation to the root schema
EntitySchemaIsNullQueryFunction CreateIsNullFunction(string checkColumnPath, string replacementColumnPath) Returns the instance of the EntitySchemaIsNullQueryFunction function for columns with values to check and substitute located at specified paths in relation to the root schema

EntitySchemaLengthQueryFunction CreateLengthFunction(string columnPath)

EntitySchemaLengthQueryFunction CreateLengthFunction(params EntitySchemaQueryExpression[] expressions)

 Creating an instance of the LEN function (the function for returning the length of expression) for the column expression at the specified path in relation to the root schema or for the specified expression array

EntitySchemaTrimQueryFunction CreateTrimFunction(string columnPath)

EntitySchemaTrimQueryFunction CreateTrimFunction(params EntitySchemaQueryExpression[] expressions)

Creating an instance of the TRIM function (the function for removing all leading and trailing whitespace from expression) for the column expression at the specified path in relation to the root schema or for the specified expression array

EntitySchemaUpperQueryFunction CreateUpperFunction(string columnPath)

 Returns an instance of the EntitySchemaUpperQueryFunction function that converts all the argument expression characters into upper case. The column expression located at the specified path in relation to the root schema is specified as the argument
EntitySchemaCurrentDateQueryFunction CreateCurrentDateFunction() Returns the instance of the EntitySchemaCurrentDateQueryFunction function that defines the current date
EntitySchemaCurrentDateTimeQueryFunction CreateCurrentDateTimeFunction() Returns the instance of the EntitySchemaCurrentDateTimeQueryFunction function that returns the current date and time
EntitySchemaCurrentTimeQueryFunction CreateCurrentTimeFunction() Returns the instance of the EntitySchemaCurrentTimeQueryFunction function that defines the current time
EntitySchemaCurrentUserAccountQueryFunction CreateCurrentUserAccountFunction() Returns the instance of the EntitySchemaCurrentUserAccountQueryFunction function that defines the account Id of the current user
EntitySchemaCurrentUserContactQueryFunction CreateCurrentUserContactFunction() Returns the instance of the EntitySchemaCurrentUserContactQueryFunction function that defines the contact Id of the current user
EntitySchemaCurrentUserQueryFunction CreateCurrentUserFunction() Returns the instance of the EntitySchemaCurrentUserQueryFunction function that defines the current user
EntitySchemaQueryFilter CreateExistsFilter(string rightExpressionColumnPath) Creates the [Exists by the specified condition] type comparison filter and sets the column expression located at the rightExpressionColumnPath specified path as the testing value

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, params string[] rightExpressionColumnPaths)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryExpression rightExpression)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryFunction rightExpressionValue)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, EntitySchemaQueryExpression leftExpression, EntitySchemaQueryMacrosType macrosType, int rightValue)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, EntitySchemaQueryExpression leftExpression, EntitySchemaQueryMacrosType macrosType, DateTime rightValue)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, EntitySchemaQueryExpression leftExpression, EntitySchemaQueryMacrosType macrosType, DayOfWeek rightValue)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryMacrosType macrosType, int rightValue)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryMacrosType macrosType, DateTime rightValue)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryMacrosType macrosType, DayOfWeek rightValue)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQuery rightExpressionValue)

EntitySchemaQueryFilter CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, int rightExprParameterValue)

EntitySchemaQueryFilter CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, double rightExprParameterValue)

EntitySchemaQueryFilter CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, DateTime rightExprParameterValue)

EntitySchemaQueryFilter CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, string rightExprParameterValue)

EntitySchemaQueryFilter CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, object rightExprParameterValue, out EntitySchemaQuery leftExprSubQuery)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, EntitySchemaQueryMacrosType macrosType, int daysCount)

IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, EntitySchemaQueryMacrosType macrosType, out EntitySchemaQuery leftExprSubQuery, int daysCount)

Creates a query filter for selecting records according to specified conditions

Parameters:

  • comparisonType – comparison type from the Terrasoft.Core.Entities.FilterComparisonType enumeration
  • leftExpressionColumnPath – path to the column that contains the left side expression of the filter
  • leftExpression – expression on the left side of the filter
  • leftExprAggregationType – the type of aggregating function
  • leftExprSubQuery – parameter that returns a subquery for the left side expression of the filter (if it is not equal to null) or a subquery for the first expression on the right side of the filter (if the left-side expression of the filter is equal to null)
  • rightExpressionColumnPaths – array of column paths that contain the right side expressions of the filter
  • rightExpression – expression on the right side of the filter
  • rightExpressionValue – instance of the expression function on the right side of the filter (the EntitySchemaQueryFunction parameter type) or a subquery expression on the right side of the filter (the EntitySchemaQuery parameter type)
  • rightValue – value processed by the macro in the right side of te filter
  • rightExprParameterValue – parameter value, to which the aggregate function on the right side of the filter is applied
  • macrosType – a type of macro from the Terrasoft.Core.Entities.EntitySchemaQueryMacrosType enumeration
  • daysCount – value to which the macro from the right side of the filter is applied. Optional parameter, default value is 0

IEntitySchemaQueryFilterItem CreateFilterWithParameters(FilterComparisonType comparisonType, bool useDisplayValue, string leftExpressionColumnPath, params object[] rightExpressionParameterValues)

IEntitySchemaQueryFilterItem CreateFilterWithParameters(FilterComparisonType comparisonType, string leftExpressionColumnPath, params object[] rightExpressionParameterValues)

IEntitySchemaQueryFilterItem CreateFilterWithParameters(FilterComparisonType comparisonType, string leftExpressionColumnPath, IEnumerable<object> rightExpressionParameterValues, bool useDisplayValue)

static IEntitySchemaQueryFilterItem CreateFilterWithParameters(EntitySchemaQuery parentQuery, EntitySchema rootSchema, FilterComparisonType comparisonType, bool useDisplayValue, string leftExpressionColumnPath, params object[] rightExpressionParameterValues)

static IEntitySchemaQueryFilterItem CreateFilterWithParameters(EntitySchema rootSchema, FilterComparisonType comparisonType, bool useDisplayValue, string leftExpressionColumnPath, params object[] rightExpressionParameterValues)

Creates a parameter filter for selecting records according to specified conditions

Parameters:

  • parentQuery – parent query for which the filter is being created
  • rootSchema – the root schema
  • comparisonType – comparison type from the Terrasoft.Core.Entities.FilterComparisonType enumeration
  • useDisplayValue – defines the column value type that is used in the filter: true – for the displayed value; false – for the stored value
  • leftExpressionColumnPath – path to the column that contains the left side expression of the filter
  • rightExpressionParameterValues – collection of parameter expressions on the right side of the filter
IEntitySchemaQueryFilterItem CreateIsNotNullFilter(string leftExpressionColumnPath) Creates the comparison filter of the [Is not “null” in database] type and, as the tested value, sets the column expression located at the path specified in the leftExpressionColumnPath parameter
IEntitySchemaQueryFilterItem CreateIsNullFilter(string leftExpressionColumnPath) Creates the comparison filter of the [Is “null” in database] type and, as the testing condition, sets the column expression located at the path specified in the leftExpressionColumnPath parameter
EntitySchemaQueryFilter CreateNotExistsFilter(string rightExpressionColumnPath) Creates the [Does not exist by the specified condition] type comparison filter and sets the column expression located at the rightExpressionColumnPath specified path as the tested value
DataTable GetDataTable(UserConnection userConnection) Returns the result of the current query execution to the object schema as a data table in memory using UserConnection
static int GetDayOfWeekNumber(UserConnection userConnection, DayOfWeek dayOfWeek) Returns the sequence number of the week day for the System.DayOfWeek entity taking into account local settings
Entity GetEntity(UserConnection userConnection, object primaryColumnValue) Returns an Entity instance by the primaryColumnValue primary key using UserConnection

EntityCollection GetEntityCollection(UserConnection userConnection, EntitySchemaQueryOptions options)

EntityCollection GetEntityCollection(UserConnection userConnection)

 Returns the Entity instance collection representing the results of executing the current query using UserConnection and the specified additional EntitySchemaQueryOptions query settings
EntitySchema GetSchema() Returns the EntitySchema entity schema instance of the current EntitySchemaQuery instance

Select GetSelectQuery(UserConnection userConnection)

Select GetSelectQuery(UserConnection userConnection, EntitySchemaQueryOptions options)

 Returns a data selection query instance using UserConnection and the specified additional settings of the EntitySchemaQueryOptions query

EntitySchemaQueryColumnCollection GetSummaryColumns()

EntitySchemaQueryColumnCollection GetSummaryColumns(IEnumerable<string> columnNames)

 Returns the collection of expressions of the query columns for which total values are calculated

Entity GetSummaryEntity(UserConnection userConnection, EntitySchemaQueryColumnCollection summaryColumns)

Entity GetSummaryEntity(UserConnection userConnection)

Entity GetSummaryEntity(UserConnection userConnection, IEnumerable<string> columnNames)

Entity GetSummaryEntity(UserConnection userConnection, params string[] columnNames)

Returns an Entity instance for the result returned by the total value selection query

Parameters:

  • userConnection – system user connection
  • summaryColumns – collection of the query columns, whose totals are selected
  • columnNames – column name collection

Select GetSummarySelectQuery(UserConnection userConnection, EntitySchemaQueryColumnCollection summaryColumns)

Select GetSummarySelectQuery(UserConnection userConnection)

Select GetSummarySelectQuery(UserConnection userConnection, IEnumerable<string> columnNames)

Select GetSummarySelectQuery(UserConnection userConnection, params string[] columnNames)

Builds a query for the selection of total values for a specified column collection of the EntitySchemaQuery current instance

Parameters:

  • userConnection – system user connection
  • summaryColumns – collection of the query columns, whose totals are selected
  • columnNames – column name collection
T GetTypedColumnValue(Entity entity, string columnName) Returns the typed column value with columnName from the Entity passed instance

void LoadDataTableData(UserConnection userConnection, DataTable dataTable)

void LoadDataTableData(UserConnection userConnection, DataTable dataTable, EntitySchemaQueryOptions options)

 Loads the result of executing the current query to the entity schema into the System.Data.DataTable entity using UserConnection and the EntitySchemaQueryOptions specified additional query settings
void RemoveColumn(string columnName) Removes the columnName column from the current query column collection
void ResetSchema() Clears the schema of the EntitySchemaQuery current instance
void ResetSelectQuery() Clears the select query for the current entity schema query
void SetLocalizationCultureId(System.Guid cultureId) Sets the identifier of the local culture

EntitySchemaQuery specifics

1) Support of access permissions

The EntitySchemaQuery data selection query is built taking into consideration the current user’s permissions. The resulting set will only include the data within the range of the current user permissions. In addition, you can manage the conditions of providing permissions to the connected tables for EntitySchemaQuery available in the query (connected to the query by the JOIN clause). These conditions are defined by the JoinRightState property value of the EntitySchemaQuery instance.

2) Cache mechanism

EntitySchemaQuery implements a mechanism for working with the repository (Creatio cache or user-defined arbitrary repository). When the EntitySchemaQuery query is performed, the data retrieved from the server database are placed in cache. The query cache can be an arbitrary repository that implements the ICacheStore interface. By default, the cache of the EntitySchemaQuery query is the session-level Creatio cache (only the data from the current user session are available) with local storage. The query cache is defined by the Cache property of the EntitySchemaQuery instance. The cache access permission key is set via the CacheItemName property (example 4).

3) Additional settings of the query

For the EntitySchemaQuery queries, you can determine additional settings that specify the parameters for paged output of query results and parameters for building hierarchical query. For this, use the EntitySchemaQueryOptions class.

The EntitySchemaQueryOptions class properties:

  • HierarchicalColumnName – name of the column used for building the hierarchical request
  • HierarchicalColumnValue – initial value of the hierarchical column that will be used to build the hierarchy
  • HierarchicalMaxDepth – maximum level of nesting the hierarchical request
  • PageableConditionValues – values of the paged output conditions
  • PageableDirection – direction of the paged output
  • PageableRowCount – number of page records of the resulting data set returned by the query

The same EntitySchemaQueryOptions instance can be used for receiving results of executing different queries by passing it as a parameter to the GetEntityCollection() method of the corresponding query (example 5).

Use cases

You can download the package with the configuration web serice implementing the below described cases using the following link.

Example 1

Creating the EntitySchemaQuery instance

public string CreateESQ()
{
    var result = "";
    // Receiving an instance of the entity schema manager.          
    EntitySchemaManager esqManager = SystemUserConnection.EntitySchemaManager;
    // Receiving an instance of the schema that will be set as the root schema for the created instance.
    // EntitySchemaQuery.
    var rootEntitySchema = esqManager.GetInstanceByName("City") as EntitySchema;
    // Creating the instance of EntitySchemaQuery, having the
    // rootEntitySchema set as the root schema.
    var esqResult = new EntitySchemaQuery(rootEntitySchema);
    // Adding columns that will be selected in the resulting query.
    esqResult.AddColumn("Id");
    esqResult.AddColumn("Name");
    // Receiving the Select instance, associated with the created EntitySchemaQuery.
    Select selectEsq = esqResult.GetSelectQuery(SystemUserConnection);
    // Receiving the resulting query text of the created EntitySchemaQuery instance.
    result = selectEsq.GetSqlText();
    return result;
}

Example 2

Creating a clone of the EntitySchemaQuery instance

public string CreateESQClone()
{
    var result = "";
    EntitySchemaManager esqManager = SystemUserConnection.EntitySchemaManager;
    var esqSource = new EntitySchemaQuery(esqManager, "Contact");
    esqSource.AddColumn("Id");
    esqSource.AddColumn("Name");
    // Creating the EntitySchemaQuery instance, which is the clone of the esqSource instance.
    var esqClone = new EntitySchemaQuery(esqSource);
    result = esqClone.GetSelectQuery(SystemUserConnection).GetSqlText();
    return result;
}

Example 3

Receiving the result of query execution

public string GetEntitiesExample()
{
    var result = "";
    // Creating a query to the City schema, adding the Name column to the query.
    var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
    var colName = esqResult.AddColumn("Name");

    // Executing the request to the database and receiving all the resulting entity collection.
    var entities = esqResult.GetEntityCollection(UserConnection);
    for (int i=0; i < entities.Length; i++) {
        result += entities[i].GetColumnValue(colName.Name).ToString();
        result += "\n";
    }
    
    // Executing the request to the database and receiving the object with the specified identifier.
    var entity = esqResult.GetEntity(UserConnection, new Guid("100B6B13-E8BB-DF11-B00F-001D60E938C6"));
    result += "\n";
    result += entity.GetColumnValue(colName.Name).ToString();
    return result;
}

Example 4

Example of working with the EntitySchemaQuery cache

public Collection<string> UsingCacheExample()
{
    // Creating a query to the City schema, adding the Name column to the query.
    var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
    esqResult.AddColumn("Name");

    // Determining the key protecting the storage of the results of query execution in cache.
    // The cache is the session level Creatio cache with local storage (since
    // the Cache propery of an entity cannot be overridden).
    esqResult.CacheItemName = "EsqResultItem";

    // Collection that stores the results of the query.
    var esqCityNames = new Collection<string>();

    // Collection that will store the cached results of executing the query.
    var cachedEsqCityNames = new Collection<string>();

    // Executing the query to database and receiving the resulting object collection.
    // After the operation is executed, the query results will be placed in cache.
    var entities = esqResult.GetEntityCollection(UserConnection);

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

    // Receiving the link to the esqResult query cache by the CacheItemName key as a database table in memory.
    var esqCacheStore = esqResult.Cache[esqResult.CacheItemName] as DataTable;

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

Example 5

Using additional settings of the query

public Collection<string> ESQOptionsExample()
{
    // Creating a query instance with the City root schema.
    var esqCities = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");
    esqCities.AddColumn("Name");

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

    // Creating a setup instance for returning the first 5 stings by the query.
    var esqOptions = new EntitySchemaQueryOptions()
    {
        PageableDirection = PageableSelectDirection.First,
        PageableRowCount = 5,
        PageableConditionValues = new Dictionary<string, object>()
    };

    // Receiving the city collection that will contain the first 5 cities of the resulting set of data.
    var cities = esqCities.GetEntityCollection(UserConnection, esqOptions);

    // Receiving the country collection that will contain the first 5 countries of the resulting set of data.
    var countries = esqCountries.GetEntityCollection(UserConnection, esqOptions);
        var esqStringCollection = new Collection<string>();
    foreach (var entity in cities)
    {
        esqStringCollection.Add(entity.GetTypedColumnValue<string>("Name"));
    }
    foreach (var entity in countries)
    {
        esqStringCollection.Add(entity.GetTypedColumnValue<string>("Name"));
    }
    return esqStringCollection;
}

© Creatio 2002-2020.

Did you find this information useful?

How can we improve it?