Как делать подзапросы в EntitySchemaQuery и фильтровать по ним

Игорь Дьяченко
8 августа 2023 11:19

В стандартном блоке чтения данных есть способ фильтровать данных по наличию записей на детали, я хочу сделать тоже самое, но в entityschema query

как такое можно реализовать?

Сейчас мой метод выглядит так, но он не работает, как его следует изменить?

[OperationContract]
        [WebInvoke(Method = "POST", RequestFormat = WebMessageFormat.Json,
            BodyStyle = WebMessageBodyStyle.WrappedRequest, ResponseFormat = WebMessageFormat.Json)]
        public string GetContactsBySubFilterDetail() {
            EntitySchemaQuery esq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Contact");
			esq.AddColumn("Name");
			EntitySchemaQuery subEsq = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "ContactAddress");
			subEsq.AddColumn(subEsq.CreateAggregationFunction(AggregationTypeStrict.Count, "Id"));
			subEsq.Filters.Add(subEsq.CreateFilterWithParameters(FilterComparisonType.Equal, "Contact", "[Contact:Id:Id]"));

			EntitySchemaQueryColumn subQueryColumn = esq.AddColumn(subEsq);
			esq.Filters.Add(esq.CreateFilterWithParameters(FilterComparisonType.GreaterOrEqual, subQueryColumn.Name, 1));

			return esq.GetEntityCollection(UserConnection).ToJson();
        }

 

Нравится

1 комментарий
Oscar Dylan
9 августа 2023 13:07

Добрый день,

 

На сколько я понял из кода, что Вы предоставили, то нужно выбрать имена всех контактов, у которых есть больше одного адреса. В теории это можно сделать через ESQ, но, увы, у меня не было примера такого подзапроса. Но: если переводить этот код в SQL запрос, то получим два варианта:

--Вариант 1
SELECT 
	[Contact].[Id], [Contact].[Name]
FROM 
	[Contact] 
WHERE 
	(
		SELECT 
			COUNT([Id])
		FROM 
			[ContactAddress]
		WHERE 
			[ContactAddress].[ContactId]  = [Contact].[Id]
	) > 1

--Вариант 2
SELECT 
    [c].[Name], [ca].[ContactId] 
FROM 
    [Contact] [c]
INNER JOIN 
    [ContactAddress] [ca]
ON
    [ca].[ContactId] = [c].[Id]
GROUP BY 
    [ca].[ContactId], [c].[Name]
HAVING
    COUNT([ca].[ContactId]) > 1

Что один, что второй запрос вернет нужную информацию. Второй запрос можно написать с применением Terrasoft.Core.DB.Select класса (с первым вариантом все сложнее). Протестировал в бизнес процессе - подход рабочий.

 

1) В методы бизнес процесса подключаем следующие пространства  имен:

 

Terrasoft.Core.DB

Newtonsoft.Json

System

System.Collections.Generic

System.Data

Terrasoft.Common

Terrasoft.Core

 

2) В методы бизнес процесса добавляем приватный метод CreateJson:

private string CreateJson(IDataReader dataReader)
{
	var list = new List<dynamic>();
	var cnt = dataReader.FieldCount;
	var fields = new List<string>();
	for (int i = 0; i < cnt; i++)
	{
		fields.Add(dataReader.GetName(i));
	}
	while (dataReader.Read())
	{
		dynamic exo = new System.Dynamic.ExpandoObject();
		foreach (var field in fields)
		{
			((IDictionary<String, Object>)exo).Add(field, dataReader.GetColumnValue(field));
		}
		list.Add(exo);
	}
	return JsonConvert.SerializeObject(list);
}

3) В схеме бизнес процесса:

в скрипт-таске указываем код:

var result = "{}";
var select = new Select(UserConnection)
	.Column("Contact","Name").As("Name") 
	.Column("ContactAddress","ContactId").As("ContactId")
	.From("Contact")
	.Join(JoinType.Inner, "ContactAddress")
	.On("Contact", "Id").IsEqual("ContactAddress", "ContactId")
	.GroupBy("ContactAddress", "ContactId")
	.GroupBy("Contact", "Name")
	.Having(Func.Count("ContactAddress", "ContactId")).IsGreater(Column.Parameter("1"))
	as Select;
using (DBExecutor dBExecutor = UserConnection.EnsureDBConnection())
{
	using (IDataReader dataReader = select.ExecuteReader(dBExecutor))
	{
		result = CreateJson(dataReader);
	}
}
Set<string>("ResultJSON", result);
return true;

4) Добавляем текстовый параметр с кодом ResultJSON в бизнес процесс.

5) Отображаем в автогенерируемой странице результат (значение параметра ResultJSON).

 

Результат:

Если нужны только имена, то из кода нужно убрать 

 

.Column("ContactAddress","ContactId").As("ContactId")

 

Судя по Вашему коду, Вы будете использовать GetContactsBySubFilterDetail как эндпоинт кастомного сервиса, но сути не меняет, нужно использовать этот же подход с Terrasoft.Core.DB.Select классом. Просто добавьте нужные простраства имен, приватный метод CreateJson и перепишите свой метод GetContactsBySubFilterDetail и можете получить нужный результат.

Показать все комментарии