isof in any filter

[AndrewZenith]

Hi,

I'm trying to use isof in an any filter, however the isof applies to the main query rather than the sub query.

https://localhost:7102/odata/tenant/hierarchyheaders?$expand=metrics,children&$filter=children/any(children: isof('Edm.Tenant.CusItm'))

SELECT [h].[Id], [h].[Code], [h].[CreatedDateTime], [h].[CreatedUser], [h].[Discriminator], [h].[Identifier], [h].[ParentId], [h].[Reference], [h].[ReferenceDate], [h1].[Id], [h1].[AmendmentID], [h1].[AmendmentParentID], [h1].[Code], [h1].[CreatedDateTime], [h1].[CreatedUser], [h1].[HierarchyHeaderId], [h1].[ValueAmount], [h1].[ValueDataCode], [h1].[ValueDateTime], [h1].[ValueInteger], [h1].[ValueLanguage], [h1].[ValueText], [h1].[Version], [h2].[Id], [h2].[Code], [h2].[CreatedDateTime], [h2].[CreatedUser], [h2].[Discriminator], [h2].[Identifier], [h2].[ParentId], [h2].[Reference], [h2].[ReferenceDate]
FROM [HierarchyHeaders] AS [h]
LEFT JOIN [HierarchyDatas] AS [h1] ON [h].[Id] = [h1].[HierarchyHeaderId]
LEFT JOIN [HierarchyHeaders] AS [h2] ON [h].[Id] = [h2].[ParentId]
WHERE EXISTS (
SELECT 1
FROM [HierarchyHeaders] AS [h0]
WHERE [h].[Id] = [h0].[ParentId] AND CASE
WHEN [h].[Discriminator] = N'CusItm' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END = CAST(1 AS bit))
ORDER BY [h].[Id], [h1].[Id]

Nearly, but not quite. Needs to be (notice the h0):
WHEN [h0].[Discriminator] = N'CusItm' THEN CAST(1 AS bit)

Is there any way to use the isof function in an any filter?

Thanks!

[habbes]

@AndrewZenith just to see if I understand the question, is what you're saying that isof is being applied to the /hieararchyheaders instance instead of the children instance?

[AndrewZenith]

Yes, that's right.

[gathogojr]

@AndrewZenith Try $filter=children/any(child:isof(child,Edm.Tenant.Cusltm))

[AndrewZenith]

Thanks!

https://localhost:7102/odata/tenant/hierarchyheaders?$expand=metrics,children&$filter=children/any(child: isof(child, 'Edm.Tenant.CusItm'))

SELECT [h].[Id], [h].[Code], [h].[CreatedDateTime], [h].[CreatedUser], [h].[Discriminator], [h].[Identifier], [h].[ParentId], [h].[Reference], [h].[ReferenceDate], [h1].[Id], [h1].[AmendmentID], [h1].[AmendmentParentID], [h1].[Code], [h1].[CreatedDateTime], [h1].[CreatedUser], [h1].[HierarchyHeaderId], [h1].[ValueAmount], [h1].[ValueDataCode], [h1].[ValueDateTime], [h1].[ValueInteger], [h1].[ValueLanguage], [h1].[ValueText], [h1].[Version], [h2].[Id], [h2].[Code], [h2].[CreatedDateTime], [h2].[CreatedUser], [h2].[Discriminator], [h2].[Identifier], [h2].[ParentId], [h2].[Reference], [h2].[ReferenceDate]
FROM [HierarchyHeaders] AS [h]
LEFT JOIN [HierarchyDatas] AS [h1] ON [h].[Id] = [h1].[HierarchyHeaderId]
LEFT JOIN [HierarchyHeaders] AS [h2] ON [h].[Id] = [h2].[ParentId]
WHERE EXISTS (
SELECT 1
FROM [HierarchyHeaders] AS [h0]
WHERE [h].[Id] = [h0].[ParentId] AND CASE
WHEN [h0].[Discriminator] = N'CusItm' THEN CAST(1 AS bit)
ELSE CAST(0 AS bit)
END = CAST(1 AS bit))
ORDER BY [h].[Id], [h1].[Id]