Adding columns to a query. The AddColumn() method
Glossary Item Box
Introduction
The EntitySchemaQuery query column is an EntitySchemaQueryColumn class instance.
You can specify main characteristics of a column instance in its properties, e.g., title, displayed value, the order and position of sorting, etc.
The AddColumn() method of the Terrasoft.Core.Entities.EntitySchemaQuery class returns the instance of a coulmn added to a query.
The name of the column relative to the root schema in the AddColumn() methods is formed according to the rules described in the Root schema. Building paths to columns
Table 1. - The list of AddColumn() method overloads
| Overload | Parameters | Description |
|---|---|---|
| AddColumn(string columnPath) |
columnPath — path to the schema column in relation to the root schema. |
Creates and inserts a column in the current entity schema query by the specified path to column in relation to the root schema. |
| AddColumn(string columnPath, AggregationTypeStrict aggregationType, out EntitySchemaQuery subQuery) |
columnPath — path to the schema column in relation to the root schema. aggregationType — type of aggregating function. The enumeration values of the Terrasoft.Common.AggregationTypeStrict aggregate function are passed as a parameter. subQuery - reference to the created subquery placed in the column. |
Creates and adds a column to the current query for the object schema in the form of a subquery that returns the result of the specified aggregate function, along the path to the schema column relative to the root schema (see example 2). |
| AddColumn(EntitySchemaQueryColumn queryColumn) | queryColumn - the EntitySchemaQueryColumn instance to be added to the column collection of the current query. | Adds passed column to the collection of columns of the current entity schema query (see example 3). |
| AddColumn(EntitySchemaQueryFunction function) | function - the instance of the EntitySchemaQueryFunction function. | Creates and inserts a column in the current entity schema query by the passed function (see example 4). |
| AddColumn(object parameterValue, DataValueType parameterDataValueType) | parameterValue - the value of the parameter. parameterDataValueType - the identifier of the parameter value type. | Creates and inserts a column of the “parameter” type with the specified value of a specific type to the schema object query (see example 5). |
| AddColumn(EntitySchemaQuery subQuery) | subQuery - the EntitySchemaQuery instance that is inserted as a column for the current query. | Creates and adds a transferred instance of the EntitySchemaQuery subquery to the object schema column (see example 6). |
Example of adding columns to a query.
Example 1
Adding a column to the query in the specified path relative to the root schema.
EntitySchemaManager esqManager = UserConnection.EntitySchemaManager; var esqResult = new EntitySchemaQuery(esqManager, "City"); // Adding a "Name" column in the resulting query. esqResult.AddColumn("Name");
Example 2
Adding a column as a subquery that returns the result of the aggregate COUNT function.
// Getting an instance of the object schema manager. EntitySchemaManager esqManager = UserConnection.EntitySchemaManager; // Creating an instance of EntitySchemaQuery with the "City" root schema. var esqResult = new EntitySchemaQuery(esqManager, "City"); // An instance of EntitySchemaQuery, in which the subquery will be placed, which returns the result of the aggregating function. EntitySchemaQuery subResultEsq; // Adding a column in the form of a subquery that returns the number of cities by country, stored // in the "Country" table. esqResult.AddColumn("[City:Country].Name", AggregationTypeStrict.Count, out subResultEsq); // Getting the text of the resulting query of the created EntitySchemaQuery instance. string sqlText = esqResult.GetSelectQuery(UserConnection).GetSqlText(); // The resulting sql-request text: // MS SQL: // SELECT // (SELECT // COUNT([SubCity].[Id]) [Count] // FROM // [dbo].[City] [SubCity] // WHERE // [SubCity].[CountryId] = [Country].[Id]) [SubCity] // FROM // [dbo].[Country] [Country]
Example 3
Adding an instance of the EntitySchemaQueryColumn class to the query.
EntitySchemaManager esqManager = UserConnection.EntitySchemaManager; var esqResult = new EntitySchemaQuery(esqManager, "City"); // Creating an instance of the "Name" column of the schema. var esqColumn = new EntitySchemaQueryColumn("Name"); // Adding the created column to EntitySchemaQuery. esqResult.AddColumn(esqColumn);
Example 4
Adding the ISNULL function as a column. The function returns the value of the [MobilePhone] column of the root query schema or the value of the [Phone] column in case the value of the [MobilePhone] column is null.
EntitySchemaManager esqManager = UserConnection.EntitySchemaManager; var esqResult = new EntitySchemaQuery(esqManager, "Contact"); esqResult.AddColumn("Name"); // Creating an instance of the IsNull function. var esqFunction = esqResult.CreateIsNullFunction("MobilePhone", "Phone"); // Adding the created function as a column to the resulting query. esqResult.AddColumn(esqFunction); string esqSqlText = esqResult.GetSelectQuery(UserConnection).GetSqlText(); // The resulting sql-request text: // MS SQL: // SELECT // [Contact].[Name] [Name], // ISNULL([Contact].[MobilePhone], [Contact].[Phone]) [IsNull] // FROM // [dbo].[Contact] [Contact]
Example 5
Adding a parameter column with a value equal to the identifier of the current user.
EntitySchemaManager esqManager = UserConnection.EntitySchemaManager; var esqResult = new EntitySchemaQuery(esqManager, "Contact"); // Getting an instance of a data type manager. DataValueTypeManager dvtManager = UserConnection.DataValueTypeManager; // Adding a parameter column with a value equal to the identifier of the current user, while passing // an instance of the <Guid> type as the data type of the parameter value. esqResult.AddColumn(UserConnection.CurrentUser.ContactId, dvtManager.GetInstanceByName("Guid"));
Example 6
Add an EntitySchemaQuery subquery as an instance column.
EntitySchemaManager esqManager = UserConnection.EntitySchemaManager; // Create an instance of EntitySchemaQuery with the "Contact" root schema. var esqSubQuery = new EntitySchemaQuery(esqManager, "Contact"); esqSubQuery.AddColumn("Id"); esqSubQuery.AddColumn("Name"); // Creating a new request with the "Account" root schema. var esqResult = new EntitySchemaQuery(esqManager, "Account"); esqResult.AddColumn("Name"); // Adding the esqSubQuery subquery as query column. esqResult.AddColumn(esqSubQuery); string esqSqlText = esqResult.GetSelectQuery(UserConnection).GetSqlText(); // The resulting sql-request text: // MS SQL: // SELECT // [Account].[Name] [Name], // ( // SELECT // [Contact].[Name] [Name] // FROM // [dbo].[Contact] [Contact]) [SubContact] // FROM // [dbo].[Account] [Account]
