Hi
We are having an issue where LLBLGen generates an inner join for a LINQ or statement. The code in question can be seen below:

var isRoleOwner = metadata.Role
                    .Any(r =>
                        r.TenantId == tenantId &&
                        r.IsActive &&
                        (
                            r.OwnerUserId == CurrentUserId ||
                            r.SecondaryOwners.Any(x => x.UserId == CurrentUserId) 
                        ));

This resulted in the following SQL being generated:

SELECT TOP(@p6) 
    CASE WHEN  EXISTS 
        (
            SELECT [LPA_L2].[VisibleForSelectionByManagers] 
            FROM ([ServiceManager].[dbo].[Role] [LPA_L2] 
                INNER JOIN [ServiceManager].[dbo].[RoleSecondaryOwner] [LPA_R3] ON [LPA_L2].[RoleId]=[LPA_R3].[RoleId])
            WHERE ( ( ( ( [LPA_L2].[TenantId] = @p1) AND ( [LPA_L2].[IsActive] = @p2)) 
                AND ( ( [LPA_L2].[OwnerUserId] = @p3) OR  
                    EXISTS (
                        SELECT [LPLA_2].[WhenChangedAD] 
                        FROM [ServiceManager].[dbo].[User] [LPLA_2] 
                        WHERE ( [LPLA_2].[UserId] = [LPA_R3].[UserId] AND ( [LPLA_2].[UserId] = @p4))))))
        ) THEN 1 ELSE 0 END AS [LPFA_1] 
FROM [ServiceManager].[dbo].[Role] [LPLA_1]

This returns false for users that are Owners and not SecondaryOwners since SecondaryOwners is empty.

As a workaround we rewrote the query to this:

isRoleOwner = metadata.Role
                    .Any(r =>
                        r.TenantId == tenantId &&
                        r.IsActive &&
                        (
                            r.OwnerUserId == CurrentUserId ||
                            metadata.RoleSecondaryOwner.Any(ros => ros.RoleId == r.RoleId && ros.UserId == CurrentUserId)
                        ));

This resulted in the following SQL being generated:

SELECT TOP(@p6) 
    CASE WHEN  EXISTS (
        SELECT [LPLA_1].[VisibleForSelectionByManagers] 
        FROM [ServiceManager].[dbo].[Role] [LPLA_1] 
        WHERE ( ( ( ( [LPLA_1].[TenantId] = @p1) AND ( [LPLA_1].[IsActive] = @p2)) AND ( ( [LPLA_1].[OwnerUserId] = @p3) 
                OR  EXISTS (
                    SELECT [LPLA_2].[UserId] 
                    FROM [ServiceManager].[dbo].[RoleSecondaryOwner] [LPLA_2] 
                    WHERE ( ( ( [LPLA_2].[RoleId] = [LPLA_1].[RoleId]) AND ( [LPLA_2].[UserId] = @p4)))))))
    ) THEN 1 ELSE 0 END AS [LPFA_1] 
FROM [ServiceManager].[dbo].[Role] [LPLA_1]

This returns true for users that are owners or secondary owners.

LLBLGEN Pro version: 5.6 (5.6.3) RTM Build date: 18-mai-2020
LLBLGEN Pro runtime library version: 5.6.0
Template group: Adapter .Net 4.7.2
Database: Microsoft SQL Server 2017, Provider: SD.LLBLGen.Pro.DQE.SqlServer 5.6.0

I think this happens because the m:n relationship navigated at the r.SecondaryOwners.Any( level is placed at the scope where it makes sense, which is at the first FROM. It's not clever enough to rewrite the query so it postpones the relationship to the second Any as you did manually with the correlation predicate at the bottom.

It's sadly not always possible to convert intend 1:1 to a sql query at first glance, so the linq provider has to interpret what is the intend of the query. With Any this is particularly difficult (like with groupby) as its scope in sql is on both sides of the method: it translates to a WHERE EXISTS, but the 'WHERE' has to be placed on a set and the any predicate has to be placed on the set the EXISTS works on. So in the top query, our provider can't do much else than deciding the set the where exists will work on is the full m:n relationship, and that's an inner join.

In your case this isn't preferable, so to help the provider you had to rewrite it and cut down the set the where exists works on to a single table. I think (but have to check) if the m:1 relationships are nullable in the intermediate table, it would have added a left join, but as they're likely not nullable, it emitted an inner join. There's no other way for it to decide what to do there: left join or inner join.

I can confirm that it is working if I replace the inner Any with Count > 0 :

var isRoleOwner = metadata.Role
                    .Any(r =>
                        r.TenantId == tenantId &&
                        r.IsActive &&
                        (
                            r.OwnerUserId == CurrentUserId ||
                            r.SecondaryOwners.Count(x => x.UserId == CurrentUserId)  > 0
                        ));

This turns into the following SQL:

SELECT TOP(@p8)
    CASE WHEN  EXISTS (SELECT [LPLA_1].[VisibleForSelectionByManagers]
    FROM [ServiceManager].[dbo].[Role] [LPLA_1]
    WHERE ( ( ( ( [LPLA_1].[TenantId] = @p1) AND ( [LPLA_1].[IsActive] = @p2)) AND ( ( [LPLA_1].[OwnerUserId] = @p3) OR ( (SELECT COUNT(*) AS [LPAV_]
        FROM ([ServiceManager].[dbo].[User] [LPA_L2] INNER JOIN [ServiceManager].[dbo].[RoleSecondaryOwner] [LPA_R3] ON [LPA_L2].[UserId]=[LPA_R3].[UserId])
        WHERE ( ( [LPLA_1].[RoleId] = [LPA_R3].[RoleId]) AND ( [LPA_L2].[UserId] = @p6))) > @p5))))) THEN 1 ELSE 0 END AS [LPFA_2]
FROM [ServiceManager].[dbo].[Role] [LPLA_1]

That's indeed the case because the Count method is an aggregate which can be placed in the projection as-is, but the Any clause is a WHERE EXISTS predicate working on sets, so it can't be placed at the spot of the count (and moving the inner join for the m:n navigation to the nested subquery). Any() is, together with Group by, Linq's headache, it's easy to use in C# but problematic to convert to SQL as it sometimes fails as it has to be converted based on interpreting what was intended...