EntitySchemaQuery class

Level: advanced

EntitySchemaQuery class

Terrasoft.Core.Entities namespace.

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 columns, filters, and restriction conditions to the query.

note

Use the .NET classes reference to access the full list of the methods, properties, parent classes, and implemented interfaces of the EntitySchemaQuery class.

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

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

Title.

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 the query result is available.

UseRecordDeactivation bool

Determines whether data will be excluded from filtering.

Methods

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 the aggregate 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	An EntitySchemaQueryColumn instance to be added to the column collection of the current query.
function	An 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 value of the parameter.
valueType	The type of the parameter value.
displayValue	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	The path to column in relation to the root schema.
useCoalesceFunctionForMultiLookup	Indicates whether to use the COALESCE function for the column of the lookup type. An optional parameter, set to true by default.
useDisplayValue	Indicates whether to use the displayed value for the column. An optional parameter, set to false by default.

Enumerable CreateSchemaColumnExpressions(params string[] columnPaths)
IEnumerable CreateSchemaColumnExpressions(IEnumerable columnPaths, bool useCoalesceFunctionForMultiLookup)

Returns a 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 is not used for 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 an instance of the CASE EntitySchemaCaseNotNullQueryFunction function for the specified EntitySchemaCaseNotNullQueryFunctionWhenItem array of condition expressions.

EntitySchemaCaseNotNullQueryFunctionWhenItem CreateCaseNotNullQueryFunctionWhenItem(string whenColumnPath, object thenParameterValue)

Returns an instance of an expression for the sql construct of the WHEN <Expression_1> IS NOT NULL THEN <Expression_2> form.

Parameters

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	An array of paths to columns in relation to the root schema.
parentQuery	A query to 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 a 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 relative to the root schema is specified as the argument.

EntitySchemaCurrenddateQueryFunction CreateCurrenddateFunction()

Returns an instance of the EntitySchemaCurrentDateQueryFunction function that defines the current date.

EntitySchemaCurrenddateTimeQueryFunction CreateCurrenddateTimeFunction()

Returns an 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, AggregationTypeStrlict 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	A comparison type from the Terrasoft.Core.Entities.FilterComparisonType enumeration.
leftExpressionColumnPath	A path to the column that contains the filter's left side expression.
leftExpression	An expression in the filter's left side.
leftExprAggregationType	The type of the aggregate function.
leftExprSubQuery	A parameter that returns a subquery for the expression on the left side of the filter (if it is not null) or a subquery for the first expression on the right side of the filter (if the left-hand side of the filter is null).
rightExpressionColumnPaths	An array of paths that contain the filter's right side expressions.
rightExpression	The expression in the filter's right side.
rightExpressionValue	An 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	The value processed by the macro in the filter's right side.
rightExprParameterValue	The value of the parameter to which the filter's right side aggregate function is applied.
macrosType	A type of macro from the Terrasoft.Core.Entities.EntitySchemaQueryMacrosType enumeration.
daysCount	The value to which the filter's right side macro is applied. An 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	The parent query for which the filter is being created.
rootSchema	The root schema.
comparisonType	A comparison type from the Terrasoft.Core.Entities.FilterComparisonType enumeration.
useDisplayValue	Whether the column value type used in the filter:true – for the displayed value; false – for the stored value.
leftExpressionColumnPath	A path to the column that contains the filter's left side expression.
rightExpressionParameterValues	The 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	The user connection.
summaryColumns	The collection of query columns for which totals are selected.
columnNames	A collection of the column names.

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	The user connection.
summaryColumns	The collection of query columns for which totals are selected.
columnNames	A collection of the column names.

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.