1. Общее описание
EntitySchemaQuery (в дальнейшем ESQ) представляет собой высокоуровневый класс, позволяющий собирать сложные запросы, используя только понятия путей к колонкам объекта таблицы (в дальнейшем схемы) относительно корневой таблицы (RootSchema).
2. Конструкторы
public EntitySchemaQuery()
– конструктор по умолчанию. Используется исключительно для внутренних целей и не применим в конфигурации.
public EntitySchemaQuery(EntitySchema rootSchema)
– конструктор принимающий экземпляр схемы, которая будет использоваться как корневая RootSchema.
Где: rootSchema - экземпляр схемы.
public EntitySchemaQuery(EntitySchemaManager entitySchemaManager, string sourceSchemaName)
- конструктор принимающий экземпляр менеджера схем и имя схемы. Осуществляется поиск схемы по имени в менеджере и заполнение свойства RootSchema.
Где: entitySchemaManager - экземпляр менеджера схем. sourceSchemaName – имя схемы в менеджере.
public EntitySchemaQuery(EntitySchemaQuery source)
– конструктор копирования. Объект создается на основании экземпляра переданного объекта.
Где: source – экземпляр объекта-источника копирования.
3. Свойства
public EntitySchema RootSchema
– корневая схема ESQ. Все пути к колонкам схем, в том числе и других, задаются относительно этой корневой схемы. Заполняется при создании объекта.
Изменение: только чтение
Значение по умолчанию: null
public EntitySchemaQueryColumn PrimaryQueryColumn
– колонка ESQ созданная по первичной колонке корневой схемы. Заполняется при первом обращении.
Изменение: только чтение
Значение по умолчанию: экземпляр созданный по первичной колонке корневой схемы
public EntitySchemaQueryColumnCollection Columns
– коллекция добавленных ESQ колонок участвующих в конечном запросе.
Изменение: только чтение с возможность наполнения коллекции
Значение по умолчанию: содержит колонку из свойства PrimaryQueryColumn
public EntitySchemaQueryFilterCollection Filters
– коллекция добавленных ESQ фильтров участвующих в конечном запросе.
Изменение: только чтение с возможность наполнения коллекции.
Значение по умолчанию: пустая коллекция public QueryJoinRightLevel JoinRightState – параметр определяющий условие наложение прав если схема администрируется по записям при использовании связанных таблиц.
Изменение: чтение и запись
Значение по умолчанию: вычитывается из системной настройки «QueryJoinRightLevel». Если настройка не задана то значение равно EnabledForAdditionalColumns;
Доступные значения: QueryJoinRightLevel.EnabledAlways – всегда QueryJoinRightLevel.EnabledForAdditionalColumns – накладывать только если из связанной схемы в запросе используются колонки отличные первичной(PrimaryColumn) и первичной для отображения( QueryJoinRightLevel.Disabled – никогда не накладывать
public bool IsDistinct
– убирать или нет дубли в результирующем наборе данных
Изменение: чтение и запись
Значение по умолчанию: false
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, " ESQCityNA"); esq.IsDistinct = true; esq.AddColumn("Id"); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText:
SELECT DISTINCT "Id" FROM "ESQCityNA" "ESQCityNA"
4. Методы
a. Методы управления колоноками
public void AddColumn(EntitySchemaQueryColumn queryColumn)
– добавляет созданную ранее ESQ колонку в коллекцию колонок
Где: queryColumn – экземпляр колонки.
Пример:
var esq = new EntitySchemaQuery(); var column = new EntitySchemaQueryColumn(); esq.AddColumn(column);
public EntitySchemaQueryColumn AddColumn(string columnPath)
– создает и добавляет ESQ колонку по пути к колонке схемы относительно корневой RootSchema схемы(объекта)
Где: columnPath – путь к колонке схемы относительно корневой схемы.
Возвращает: экземпляр созданной колонки.
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, "City"); esq.AddColumn("Name"); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText запроса:
SELECT "Name" FROM "City"
public EntitySchemaQueryColumn AddColumn(EntitySchemaQueryFunction function)
– создает и добавляет ESQ колонку по переданной функции.
Где: function – экземпляр ESQ функции.
Возвращает: экземпляр созданной колонки.
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, "City"); var func = esq.CreateIsNullFunction("Name", "Code"); esq.AddColumn(func); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText запроса:
SELECT NVL("City"."Name", "City"."Code") "IsNull" FROM "City" "City"
public EntitySchemaQueryColumn AddColumn(string columnPath, AggregationTypeStrict aggregationType, out EntitySchemaQuery subQuery)
– создает и добавляет ESQ колонку в виде подзапроса, возвращающего результат указанной агрегирующей функции, по пути к колонке схемы относительно корневой RootSchema схемы(объекта).
Где: columnPath – путь к колонке схемы относительно корневой схемы. aggregationType – значение перечисления типов агрегирующих функций
public enum AggregationTypeStrict { Count, Sum, Avg, Min, Max }
subQuery – ссылка на созданный подзапрос, помещенный в колонку
Возвращает: экземпляр созданной колонки.
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, "ESQCountryNA"); EntitySchemaQuery subEsq = null; esq.AddColumn("[ESQCityNA:Country].Name", AggregationTypeStrict.Count, out subEsq); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText запроса:
SELECT ( SELECT COUNT("SubESQCityNA"."Id") "Count" FROM "ESQCityNA" "SubESQCityNA" WHERE "SubESQCityNA"."CountryId" = "ESQCountryNA"."Id") "SubESQCityNA" FROM "ESQCountryNA" "ESQCountryNA"
public void RemoveColumn(string columnName)
– удаляет ESQ колонку из коллекции колонок по имени
Где: columnName – имя удаляемой колонки
public void AddAllSchemaColumns()
– создает набор ESQ колонок по всем колонкам корневой таблицы
public EntitySchemaQueryColumn AddColumn(object parameterValue, DataValueType parameterDataValueType)
– создает и добавляет колонку-параметр которая будет выбирать значение переданного параметра.
Где: parameterValue – значение параметра parameterDataValueType – тип параметра Возвращает: экземпляр созданной колонки.
public EntitySchemaQueryColumn AddColumn(EntitySchemaQuery subQuery)
– создает и добавляет колонку-подзапрос на основании переданного подзапроса
Где: subQuery – подзапрос
Возвращает: экземпляр созданной колонки. b. Методы добавления фильтров
public IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, params string[] rightExpressionColumnPaths)
– создает и возвращает фильтр по двум колонкам.
Где: comparisonType – тип сравнения leftExpressionColumnPath – путь к колонке в левой части фильтра rightExpressionColumnPaths – пути к колонке в правой части фильтра
Возвращает: экземпляр созданного фильтра
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, "ESQCityNA"); esq.AddColumn("Id"); var filter = esq.CreateFilter(FilterComparisonType.Equal, "ModifiedOn", "CreatedOn"); esq.Filters.Add(filter); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText запроса:
SELECT "ESQCityNA"."Id" "Id" FROM "ESQCityNA" "ESQCityNA" WHERE "ESQCityNA"."ModifiedOn" = "ESQCityNA"."CreatedOn"
public IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryFunction rightExpressionValue)
– создает фильтр по колонке и функции
Где: comparisonType – тип сравнения leftExpressionColumnPath – путь к колонке в левой части фильтра rightExpressionValue – экземпляр функции в правой части фильтра
Возвращает: экземпляр созданного фильтра
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, "ESQCityNA"); esq.AddColumn("Id"); var func = esq.CreateIsNullFunction("ModifiedOn", "CreatedOn"); var filter = esq.CreateFilter(FilterComparisonType.Equal, "ModifiedOn", func); esq.Filters.Add(filter); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText запроса:
SELECT "ESQCityNA"."Id" "Id" FROM "ESQCityNA" "ESQCityNA" WHERE "ESQCityNA"."ModifiedOn" = NVL("ESQCityNA"."ModifiedOn", "ESQCityNA"."CreatedOn")
public IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQueryMacrosType macrosType)
– создает фильтр по колонке и значению макроса.
Где: comparisonType – тип сравнения leftExpressionColumnPath – путь к колонке в левой части фильтра macrosType – значение перечисления макроса определяющее функциональность фильтра
Значения:
public enum EntitySchemaQueryMacrosType { None, CurrentUser, CurrentUserContact, Yesterday, Today, Tomorrow, PreviousWeek, CurrentWeek, NextWeek, PreviousMonth, CurrentMonth, NextMonth, PreviousQuarter, CurrentQuarter, NextQuarter, PreviousHalfYear, CurrentHalfYear, NextHalfYear, PreviousYear, CurrentYear, NextYear }
Возвращает: экземпляр созданного фильтра
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, "ESQCityNA"); esq.AddColumn("Id"); var filter = esq.CreateFilter(FilterComparisonType.Equal, "ModifiedOn", EntitySchemaQueryMacrosType.CurrentMonth); esq.Filters.Add(filter); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText запроса:
SELECT "ESQCityNA"."Id" "Id" FROM "ESQCityNA" "ESQCityNA" WHERE ("ESQCityNA"."ModifiedOn" >= :P1 AND "ESQCityNA"."ModifiedOn" :P2)
public IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExpressionColumnPath, EntitySchemaQuery rightExpressionValue)
– создает фильтр по колонке и подзапросу
Где: comparisonType – тип сравнения leftExpressionColumnPath – путь к колонке в левой части фильтра rightExpressionValue – подзапрос в правой части фильтра
Возвращает: экземпляр созданного фильтра
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, "ESQCityNA"); esq.AddColumn("Id"); var subEsq = new EntitySchemaQuery(EntitySchemaManager, "ESQCityNA"); subEsq.AddColumn(subEsq.CreateAggregationFunction(AggregationTypeStrict.Max, "ModifiedOn")); var filter = esq.CreateFilter(FilterComparisonType.Equal, "ModifiedOn", subEsq); esq.Filters.Add(filter); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText запроса:
SELECT "ESQCityNA"."Id" "Id" FROM "ESQCityNA" "ESQCityNA" WHERE "ESQCityNA"."ModifiedOn" = (SELECT MAX("ESQCityNA"."ModifiedOn") "Max" FROM "ESQCityNA" "ESQCityNA")
public IEntitySchemaQueryFilterItem CreateFilter(FilterComparisonType comparisonType, string leftExprColumnPath, AggregationTypeStrict leftExprAggregationType, object rightExprParameterValue, out EntitySchemaQuery leftExprSubQuery)
– создает фильтр с агрегирующим подзапросом по колонке в левой и значением в правой части фильтра
Где: comparisonType – тип сравнения leftExprColumnPath – путь к колонке в левой части фильтра leftExprAggregationType – тип агрегирующей функции rightExpressionValue – значение в правой части фильтра leftExprSubQuery – возвращаемый экземпляр созданного подзапроса
Возвращает: экземпляр созданного фильтра
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, "ESQCountryNA"); esq.AddColumn("Id"); EntitySchemaQuery subEsq = null; var filter = esq.CreateFilter(FilterComparisonType.GreaterOrEqual, "[ESQCityNA:Country].Name", AggregationTypeStrict.Count, 5, out subEsq); esq.Filters.Add(filter); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText запроса:
SELECT "ESQCountryNA"."Id" "Id" FROM "ESQCountryNA" "ESQCountryNA" WHERE (SELECT COUNT("SubESQCityNA"."Id") "Count" FROM "ESQCityNA" "SubESQCityNA" WHERE "SubESQCityNA"."CountryId" = "ESQCountryNA"."Id") >= 5
public IEntitySchemaQueryFilterItem CreateFilterWithParameters(FilterComparisonType comparisonType, bool useDisplayValue, string leftExpressionColumnPath, params object[] rightExpressionParameterValues)
– создает фильтр по колонке в левой части и параметром или несколькими параметрами в правой части
Где: comparisonType – тип сравнения useDisplayValue – определяет для колонок типа справочник использовать ли в фильтре хранимое или отображаемое значение leftExprColumnPath – путь к колонке в левой части фильтра rightExpressionParameterValues – значения в правой части фильтра
Возвращает: экземпляр созданного фильтра
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, "ESQCountryNA"); esq.AddColumn("Id"); var filter = esq.CreateFilterWithParameters(FilterComparisonType.Equal, "CreatedOn", new DateTime()); esq.Filters.Add(filter); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText запроса:
SELECT "ESQCountryNA"."Id" "Id" FROM "ESQCountryNA" "ESQCountryNA" WHERE "ESQCountryNA"."CreatedOn" = :P1
public IEntitySchemaQueryFilterItem CreateIsNullFilter(string leftExpressionColumnPath)
– создает фильтр проверки колонки на пустое значение
Где: leftExpressionColumnPath – путь к колонке по которой строится фильтр
Возвращает: экземпляр созданного фильтра
Пример:
var esq = new EntitySchemaQuery(EntitySchemaManager, "ESQCountryNA"); esq.AddColumn("Id"); var filter = esq.CreateIsNullFilter("CreatedOn"); esq.Filters.Add(filter); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
SqlText запроса:
var esq = new EntitySchemaQuery(EntitySchemaManager, "ESQCountryNA"); esq.AddColumn("Id"); var filter = esq.CreateIsNullFilter("CreatedOn"); esq.Filters.Add(filter); string sqlText = esq.GetSelectQuery(CurrentConnection).GetSqlText();
Описано хорошо.
Только какое реальное применение у данной разработки в связке с Террасофтом?
Здравствуйте, Сергей.
Возможно, будет полезен такой пример:
// Создаем вложенный подзапрос. var subQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Contact"); subQuery.AddColumn("Name"); // Создаем коллекцию фильтров вложенного подзапроса, которые будут объединяться условием OR. var subQueryFilters = new EntitySchemaQueryFilterCollection(subQuery, LogicalOperationStrict.Or); // "Подколлекция" фильтров для первого блока условий <условие1> AND <условие2> var subQueryFilter1 = new EntitySchemaQueryFilterCollection(subQuery, LogicalOperationStrict.And); subQueryFilter1.Add(subQuery.CreateFilterWithParameters(FilterComparisonType.Equal, "CreatedOn", 0)); subQueryFilter1.Add(subQuery.CreateFilterWithParameters(FilterComparisonType.Greater, "CreatedOn", 0)); subQueryFilters.Add(subQueryFilter1); // "Подколлекция" фильтров для второго блока условий <условие3> AND <условие4> var subQueryFilter2 = new EntitySchemaQueryFilterCollection(subQuery, LogicalOperationStrict.And); subQueryFilter2.Add(subQuery.CreateFilterWithParameters(FilterComparisonType.Equal, "CreatedOn", 4)); subQueryFilter2.Add(subQuery.CreateFilterWithParameters(FilterComparisonType.LessOrEqual, "CreatedOn", 20)); subQueryFilters.Add(subQueryFilter2); // Добавляем соданные фильтры в подзапрос. subQuery.Filters.Add(subQueryFilters); // Создаем основной запрос. var mainQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Contact"); mainQuery.AddColumn("Name"); // Фильтр для условия основного запроса. var mainFilter = new EntitySchemaQueryFilter(FilterComparisonType.NotExists); mainFilter.LeftExpression = mainQuery.CreateSchemaColumnExpression("Name"); mainFilter.RightExpressions.Add(new EntitySchemaQueryExpression(subQuery)); mainQuery.Filters.Add(mainFilter);
В итоге имеем такой запрос (для MS SQL):
SELECT [Contact].[Name] [Name] FROM [dbo].[Contact] [Contact] WHERE NOT EXISTS ( SELECT [Contact].[Name] [Name] FROM [dbo].[Contact] [Contact] WHERE (([Contact].[CreatedOn] = 0 AND [Contact].[CreatedOn] > 0) OR ([Contact].[CreatedOn] = 4 AND [Contact].[CreatedOn] <= 20)))
Как сделать NotExists фильтр, если для вложенного запроса так же есть условия?
Пример нужного запроса представлен ниже:
SELECT [Document].[Id] [Id] FROM [dbo].[Document] [Document] WITH(NOLOCK) WHERE AND [Document].[SxCompletionDate] <= GETDATE() AND NOT EXISTS ( SELECT [SxActivityDocument].[Id] [Id] FROM [dbo].[SxActivityDocument] [SxActivityDocument] WITH(NOLOCK) LEFT OUTER JOIN [dbo].[Activity] [SxActivity] WITH(NOLOCK) ON ([SxActivity].[Id] = [SxActivityDocument].[SxActivityId]) WHERE [Document].[Id] = [SxActivityDocument].[SxDocumentId] AND [SxActivity].[ActivityCategoryId] <> '<Id Категории активности>')
Решение моей задачи в http://www.community.terrasoft.ua/forum/topic/10088#comment-56851
Здравствуйте! Подскажите, есть ли аналогичный способ получения sql-текста для запроса, построенного на клиенте на языке JScript?
Здравствуйте, Подскажите как переделать запрос в ESQ
SELECT op.title as OpportunityTitle, ct.name as OwnerName, ct.email as OwnerEmail, ac.name as AccountName FROM [BPMonline780SalesEnterpriseSoftkeyRUS_TU03].[dbo].[Opportunity] as op join [BPMonline780SalesEnterpriseSoftkeyRUS_TU03].[dbo].[Contact] as ct on op.ownerid = ct.id join [BPMonline780SalesEnterpriseSoftkeyRUS_TU03].[dbo].[Account] as ac on op.AccountId = ac.id where op.StageId != 'A9AAFDFE-2242-4F42-8CD5-2AE3B9556D79' and op.StageId != '736F54FD-E240-46F8-8C7C-9066C30AFF59' and op.StageId != '60D5310C-5BE6-DF11-971B-001D60E938C6'
Можно ли в ESQ сделать фильтр по текстовой колонке без учета регистра?