ASP.Net Core OData – filtrar por número de coincidencias anidadas.
Tengo una entidad raíz llamada Compañía con hijos de tipo CompanyType. Quiero filtrar las compañías que tienen todos los tipos proporcionados. Es posible crear dicho filtro en SQL puro:
SELECT Companies.Id, Companies.Name
FROM Companies
JOIN (
SELECT CompanyTypes.CompanyId FROM CompanyTypes
WHERE CompanyTypes.CompanyTypeId IN (123, 456, 789, 555)
GROUP BY CompanyId
HAVING COUNT(*) = 4
) Types ON Companies.ID = Types.CompanyID
También es posible crear dicho filtro utilizando LINQ to SQL:
var typeIds = new[] { 123, 456, 789, 555 };
var companies = await EFContext.Companies.Where(company => company.CompanyTypes.Count(type => typeIds.Contains(type.CompanyTypeId)) == typeIds.Length).ToListAsync();
¿Existe alguna forma de componer esta consulta utilizando la sintaxis de OData? He intentado esto:
companyTypeIds/any(i: i eq 123) and companyTypeIds/any(i: i eq 456) and companyTypeIds/any(i: i eq 789) and companyTypeIds/any(i: i eq 555)
Esto devuelve el resultado esperado, pero la consulta subyacente es diferente y se vuelve muy lenta cuando aumenta el número de condiciones. ¿Existe alguna forma de hacerlo más eficiente, como en las consultas SQL proporcionadas? El backend está utilizando ASP.Net Core 6, EF Core 6, ASP.Net Core OData 8 (la versión más reciente en este momento).
davy.ai
Desafortunadamente, la sintaxis de OData no proporciona una manera de componer una consulta con la misma eficiencia que las consultas SQL proporcionadas. La sintaxis de OData admite filtrado utilizando operadores lógicos (como and/or), operadores de comparación (como eq/gt/lt) y funciones como contains, substringof, etc. Sin embargo, no admite agrupación, agregación, ni la capacidad de filtrar en función del recuento de entidades relacionadas.
En casos como este, donde la sintaxis de OData no es suficiente para expresar la consulta deseada, el enfoque recomendado es utilizar una acción o función personalizada definida en el controlador de OData que pueda ejecutar la consulta deseada utilizando SQL puro u otros mecanismos eficientes.
Por ejemplo, podría definir una función personalizada en el controlador de Compañía que acepte una matriz de identificadores de tipos de compañía y devuelva las Compañías filtradas utilizando la primera consulta SQL proporcionada anteriormente. Esta función se puede exponer como un punto final de OData y se puede llamar desde el cliente utilizando la sintaxis de OData.
En el lado del cliente, la función se puede llamar utilizando la sintaxis de OData de la siguiente manera:
Este enfoque ofrece lo mejor de ambos mundos: la capacidad de usar consultas SQL eficientes para filtrar datos y la conveniencia de utilizar la sintaxis de OData para llamar al punto final desde el cliente.