Reading multilingual data with EntitySchemaQuery
Glossary Item Box
Introduction
Application has supported multilingual data since version 7.8.3. That means the list data is displayed based on the preferred user language (“culture”). Please refer to the “Working with data structure” article for more information on data localization.
Reading multilingual data with EntitySchemaQuery
EntitySchemaQuery (ESQ) is a base mechanism for reading the Creatio database data. ESQ supports multilingual data by default.
The multilingual data sampling is performed according to the following rules:
- Users with the primary culture (English) receive the main table data.
- Users with additional culture receive the localization table data. If the localization table contains no data for the user’s culture, the main table data is returned.
Example of a localized column query generation
A query generation sample code for the localized [Name] column of the [City] object schema on the server side (C#):
// User Connection. var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"]; // Forming a query. var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "City"); // Adding columns to a query. esqResult.AddColumn("Name"); // Executing a database query and retrieving the entire resulting object collection. var entities = esqResult.GetEntityCollection(userConnection); // Retrieving the query text. var s = esqResult.GetSelectQuery(userConnection).GetSqlText(); // Returning the result. return s;
This code can be added to the custom configuration service method, for example.
If a default culture is selected in the user profile, the following SQL query will be generated:
SELECT [City].[Name] [Name] FROM [dbo].[City] [City] WITH(NOLOCK)
If any culture other than the primary culture is selected in the user profile, the generated SQL query will take into account the localized values for the seleted culture.
SELECT ISNULL([SysCityLcz].[Name], [City].[Name])[Name] FROM [dbo].[City] [City] WITH(NOLOCK) LEFT OUTER JOIN [dbo].[SysCityLcz] [SysCityLcz] WITH(NOLOCK) ON ([SysCityLcz].[RecordId] = [City].[Id] AND [SysCityLcz].[SysCultureId] = @P1)
The @P1 parameter takes the record identifier value (Id) of the selected culture from the SysCulture table.
Disabling the data localization mechanism
To disable the data localization selection mechanism (even if the query is executed on behalf of a user with one of the additional cultures), you must set the ESQ instance to false for the UseLocalization property.
// User Connection. var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"]; // Forming a query. var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "City"); // Adding a column to a query. esqResult.AddColumn("Name"); // Disabling the data localization mechanism. esqResult.UseLocalization = false; // Executing a database query and retrieving the entire resulting object collection. var entities = esqResult.GetEntityCollection(userConnection); // Retrieving the query text. var s = esqResult.GetSelectQuery(userConnection).GetSqlText(); // Returning the reult. return s;
Regardless of which culture is selected in the user's profile, the following SQL query will be generated:
SELECT [City].[Name] [Name] FROM [dbo].[City] [City] WITH(NOLOCK)
Custom culture data selection
ESQ enables you to select culture data different from the current user culture and the default culture. To select the custom culture data, call the SetLocalizationCultureId(Guid cultureId) method in the ESQ instance before data retrieval, and pass the id of the culture with the necessary data to it.
// User Connection. var userConnection = (UserConnection)HttpContext.Current.Session["UserConnection"]; // Retriveing the id of the necessary culture (e.g. italian). var sysCulture = new SysCulture(userConnection); if (!sysCulture.FetchPrimaryInfoFromDB("Name", "it-IT")) { // Error: The record is not found. return "The culture is not found"; } Guid italianCultureId = sysCulture.Id; // Forming a query. var esqResult = new EntitySchemaQuery(userConnection.EntitySchemaManager, "City"); // Adding a column to a query. esqResult.AddColumn("Name"); // Installing the necessary localization. esqResult.SetLocalizationCultureId(italianCultureId); // Executing a database query and retrieving the entire resulting object collection. var entities = esqResult.GetEntityCollection(userConnection); // Retrieving the query text. var s = esqResult.GetSelectQuery(userConnection).GetSqlText(); // Returning the reult. return s;
As the result, the following SQL inquiry is generated:
SELECT ISNULL([SysCityLcz].[Name], [City].[Name])[Name] FROM [dbo].[City] [City] WITH(NOLOCK) LEFT OUTER JOIN [dbo].[SysCityLcz] [SysCityLcz] WITH(NOLOCK) ON ([SysCityLcz].[RecordId] = [City].[Id] AND [SysCityLcz].[SysCultureId] = @P1)
The @P1 parameter takes the record identifier value (id) stored in the italianCultureId variable.
