Data access through ORM

Level: advanced

The back-end core components provide the following database access options:

• access using the ORM model
• direct access

This article covers database queries that use the ORM model.

ORM (Object-relational mapping) is a technology that lets you use object-oriented programming languages to manage data retrieved from the database. The purpose of ORM is to bind objects implemented in the code to the database table records.

The ORM data model implements data management via the following classes:

• The Terrasoft.Core.Entities.EntitySchemaQuery class builds queries to retrieve the database table records. The queries apply the access permissions of the current user.
• The Terrasoft.Core.Entities.Entity class accesses entities that represent records in the database table.

We recommend using the ORM model to access data, though the direct access to the database is also implemented in the back-end core components. Learn more about running direct database queries: Access data directly.

Configure a column path relative to the root schema

Base a selection query that uses EntitySchemaQuery on the root schema. The root schema is the database table relative to which to build paths to the query columns, including the columns of joinable tables. To use a table column in a query, set the path to the column correctly.

Follow the principle of connection by lookup fields when building the column paths. Build the name of a column to add to the query as a chain of interconnected links. Each link must represent the context of the schema connected to the previous schema by a lookup column.

The template for configuring the path to a column in the N schema: 1SchemaContext.[...].NSchemaContext.LookupSchema'sColumnName.

Specify the column path using the direct connections

The template for configuring the column path using the direct connections: LookupColumnName.LookupSchema'sColumnSchemaName.

Use direct connections if the connection lookup column is located in the main schema and links to the joinable schema. For example, a [City] root schema contains the [Country] lookup column. The column is connected to the [Country] lookup schema by the [Id] column.

The path to the column that contains the name of the country connected to the city, built using the direct connections: Country.Name. Where:

• Country is the name of the lookup column in the [City] root schema. Links to the [Country] schema.
• Name is the name of the column in the [Country] lookup schema.

Specify the column path using the reverse connections

Unlike direct connections, reverse connections require the lookup field to join to be located in the joinable entity, not the main entity.

The template for configuring the column path using the reverse connections: [JoinableSchemaName:NameOfTheColumnToLinkTheJoinableSchema:NameOfTheColumnToLinkTheCurrentSchema].JoinableSchema'sColumnName.

The path to the name column of the account whose [City] field contains the [City] record selected in the query, built using the reverse connections: [Account:City:Id].Name. Where:

• Account is the name of the joinable schema.
• City is the name of the [Account] schema's column to connect the joinable schema.
• Id is the name of the [City] schema's lookup column to connect the current schema.
• Name is the value of the [Account] schema's lookup column.

If the schema's lookup column to connect the current schema is [Id], you do not have to specify it: [JoinableSchemaName:NameOfTheColumnToConnectTheJoinableSchema].JoinableSchema'sColumnName. For example, [Account:City].Name.

Retrieve data from the database

Use the following classes to retrieve data from the database:

• Terrasoft.Core.DB.Select retrieves data by accessing the database directly. Learn more: Access data directly.
• Terrasoft.Core.Entities.EntitySchemaQuery retrieves database data using the ORM model.

The purpose of the Terrasoft.Core.Entities.EntitySchemaQuery class is to build selection queries for database table records. By default, you can retrieve up to 20 000 records in a query. This value can be modified in the MaxEntityRowCount setting in the ...\Terrasoft.WebApp\Web.config file.

Important

However, we do not recommend modifying the MaxEntityRowCount setting. Doing so may lead to performance issues.

After you create and configure the class instance, Creatio will build a SELECT query to the application database. You can add columns, filters, and restriction conditions to the query. You can also set the parameters to output the query results in pagination mode. If needed, use the Terrasoft.Core.Entities.EntitySchemaQueryOptions class to set the parameters of a hierarchical query. Pass the same EntitySchemaQueryOptions instance as the GetEntityCollection() method parameter of the corresponding query to retrieve the results of various queries.

The Terrasoft.Core.Entities.EntitySchemaQuery class major features:

• The resulting query applies the access permissions of the current user.
• You can manage the current user's access permissions to the tables that JOIN SQL operator joined to the query.
• The class lets you manage data of a cache repository or any repository you specify.

Creatio will cache the data retrieved from the database after running the query. Any repository that implements the Terrasoft.Core.Store.ICacheStore interface can serve as the query cache. By default, Creatio uses the session level cache with local storage. Define the query cache in the Cache property of the EntitySchemaQuery class instance. Set the key to access the query cache in the CacheItemName property. Learn more about Creatio repository levels: Storage types.

As result of the query, is the Terrasoft.Nui.ServiceModel.DataContract.EntityCollection instance or the Terrasoft.Core.Entities.Entity class instance collection. Each Entity collection instance represents a string of data returned by the query.

Manage joined tables

You can specify the schema join type of a query using the EntitySchemaQuery class. Use the JOIN operator to add the joinable schema column to the query.

The template for configuring the join type of the joinable schema column: SpecialCharacterOfTheJoinTypeNameOfTheColumnToConnectTheJoinableSchema.

View the join types description in the table below.

Join type	Special character of the join type	Use example
INNER JOIN	=	=Country.Name
LEFT OUTER JOIN	>	>Country.Name
RIGHT OUTER JOIN	<	<Country.Name
FULL OUTER JOIN	<>	<>Country.Name
CROSS JOIN	*	*Country.Name

By default, Creatio uses the LEFT OUTER JOIN type.

See the example that adds columns to a query using various join types below.

Add columns to a query

/* Create a query instance based on the City root schema. */
var esqResult = new EntitySchemaQuery(
    UserConnection.EntitySchemaManager,
    "City"
);

/* Add the Country schema of the LEFT OUTER JOIN type to the query. */
esqResult.AddColumn("Country.Name");

/* Add the Country schema of the INNER JOIN type to the query. */
esqResult.AddColumn("=Country.Name");

/* Add the Country schema of the LEFT OUTER JOIN type and the Contact schema of the RIGHT OUTER JOIN type to the query. */
esqResult.AddColumn(">Country.<CreatedBy.Name");

As a result, Creatio will generate the SQL query.

SQL query

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])

If the query contains the root schema and joinable schemas that use record permissions, you will be able to apply the access permissions of the current user. The Terrasoft.Core.DB.QueryJoinRightLevel enumeration lists the ways to apply the record access permissions to the joinable schemas.

The ways to apply the access permissions to the joinable schema queries are as follows:

• Apply always.
• Apply only if the joinable schema query uses columns other than the primary column and primary display column. Usually, these are [Id] and [Name] columns.
• Do not apply.

The JoinRightState query property determines the order to apply the access permissions. The Joined objects administering (QueryJoinRightLevel code) system setting determines the default value of the property. If the setting is empty, Creatio will apply the access permissions if the joinable schema query uses columns other than the primary column and the primary display column.

Manage the query filters

Filter is sets of conditions applied when displaying the query data. In SQL terms, a filter is a separate predicate (condition) of the WHERE operator.

Filter structure

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

To create a simple filter in EntitySchemaQuery, use the CreateFilter() method that returns the instance of the EntitySchemaQueryFilter type. EntitySchemaQuery implements a series of overloads for this method. This lets you create filters with different incoming parameters. EntitySchemaQuery implements methods that create special kinds of filters.

The EntitySchemaQuery instance contains the Filters property that represents the filter collection of the query (the EntitySchemaQueryFilterCollection class instance). The EntitySchemaQueryFilterCollection class instance is a typed collection of the IEntitySchemaQueryFilterItem elements.

Follow this procedure to add a filter to the query:

• Create a filter instance for the query (CreateFilter() methods, methods that create special kinds of filters).
• Add the filter instance to the query filter collection (the Add() collection method).

By default, Creatio uses the logical AND operation to combine the filters added to the Filters collection. To implement the logical OR operation, use the LogicalOperation property of the Filters collection. This property takes the values of the LogicalOperationStrict enumeration and lets you specify the logical operation to combine the filters.

You can manage the filters that build the resulting dataset in the EntitySchemaQuery queries. Each element of the Filters collection includes the IsEnabled property that determines whether the element takes part in building the resulting query (true/false). Creatio defines the similar IsEnabled property for the Filters collection. If you set this property to false, the query filtering will be disabled, yet the query filters collection will remain unchanged. Thus, you can create a query filters collection and use various combinations without changing the collection itself.

Manage the database entity

Terrasoft.Core.Entities.Entity is a class that implements the database entity management.

The purpose of the Terrasoft.Core.Entities.Entity class is to access the object that represents a database table record. You can also use the class for CRUD operations on the corresponding records.

---

See also

Access data directly

Data storage and cache