Could the performance of IsNotInAny be better?

[jersiovic]

Currently queries like this one https://github.com/OrchardCMS/OrchardCore/blob/main/src/OrchardCore/OrchardCore.OpenId.Core/YesSql/Stores/OpenIdTokenStore.cs#L441
Are translated to sth like this:

(@p0 datetime,@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 datetime)
SELECT DISTINCT TOP (100) [OpenId_Document].*,[OpenId_Document].[Id] 
FROM [OpenId_Document] 
INNER JOIN [OpenId_OpenIdTokenIndex] AS [OpenIdTokenIndex_a1] 
ON [OpenIdTokenIndex_a1].[DocumentId] = [OpenId_Document].[Id] 
WHERE (([OpenIdTokenIndex_a1].[CreationDate] < @p0) and (((([OpenIdTokenIndex_a1].[Status] <> @p1) and ([OpenIdTokenIndex_a1].[Status] <> @p2)) 
or [OpenIdTokenIndex_a1].[AuthorizationId] 
NOT IN (
              SELECT [OpenIdAuthorizationIndex_a1].[AuthorizationId] 
              FROM [OpenId_OpenIdAuthorizationIndex] AS [OpenIdAuthorizationIndex_a1] 
              WHERE ([OpenIdAuthorizationIndex_a1].[Status] = @p3)) 
) 
or ([OpenIdTokenIndex_a1].[ExpirationDate] < @p4))) 
ORDER BY [OpenId_Document].[Id]

The performance of not in is not good.
@sebastienros what do you think of replacing this translation per another based on left join and check if joined index table id is not null. Would you accept a PR like that?

[jersiovic]

I close the issue cause what I request won't solve the problem of the query I mention