Query: JOIN/Left-outer-join queries wrapped in client projection could be simplified to ShapedQueries to reduce client evaluation

[maumar]

The following query:

from l2_outer in
    (from l2_inner in ctx.LevelTwo
     join l1_inner in ctx.LevelOne on l2_inner.Level1_Required_Id equals l1_inner.Id
     select l2_inner).Take(2)
join l1_outer in ctx.LevelOne on l2_outer.Level1_Required_Id equals l1_outer.Id
select l2_outer.Name;

produces the following query plan:

TRACKED: False
(QueryContext queryContext) => IEnumerable<string> _Select(
    source: IEnumerable<TransparentIdentifier<ValueBuffer, ValueBuffer>> _Join(
        outer: IEnumerable<ValueBuffer> _Select(
            source: IEnumerable<TransparentIdentifier<ValueBuffer, ValueBuffer>> _ShapedQuery(
                queryContext: queryContext, 
                shaperCommandContext: SelectExpression: 
                    SELECT [t].[Level1_Required_Id], [t].[Name]
                    FROM (
                        SELECT TOP(@__p_0) [l2_inner].*
                        FROM [Level2] AS [l2_inner]
                        INNER JOIN [Level1] AS [l1_inner] ON [l2_inner].[Level1_Required_Id] = [l1_inner].[Id]
                    ) AS [t]
                , 
                shaper: CompositeShaper`3
            )
            , 
            selector: (TransparentIdentifier<ValueBuffer, ValueBuffer> t0) => t0.Outer
        )
        , 
        inner: IEnumerable<ValueBuffer> _ShapedQuery(
            queryContext: queryContext, 
            shaperCommandContext: SelectExpression: 
                SELECT [l1_outer].[Id]
                FROM [Level1] AS [l1_outer]
            , 
            shaper: ValueBufferShaper
        )
        , 
        outerKeySelector: (ValueBuffer l2_outer) => int TryReadValue(l2_outer, 0, null), 
        innerKeySelector: (ValueBuffer l1_outer) => int TryReadValue(l1_outer, 0, null), 
        resultSelector: (ValueBuffer l2_outer | ValueBuffer l1_outer) => TransparentIdentifier<ValueBuffer, ValueBuffer> CreateTransparentIdentifier(
            outer: l2_outer, 
            inner: l1_outer
        )
    )
    , 
    selector: (TransparentIdentifier<ValueBuffer, ValueBuffer> t0) => string TryReadValue(t0.Outer, 1, null)
)

Currently we produce client join here because the outer element (which is a join itself) has a client projection. However, we only project Outer element of that translated join, so it could be simplified to ShapedQuery.

This in turn would allow outer join to get simplified - currently we don't try to translate joins if one of the elements has a client projection (see RelationalQueryModelVisitor.CanFlattenJoin)

[smitpatel]

What would be the SelectExpression for server translated join?

[maumar]

SELECT [t].[Level1_Required_Id], [t].[Name]
FROM (
    SELECT TOP(@__p_0) [l2_inner].*
    FROM [Level2] AS [l2_inner]
    INNER JOIN [Level1] AS [l1_inner] ON [l2_inner].[Level1_Required_Id] = [l1_inner].[Id]
) AS [t]
LEFT OUTER JOIN [Level1] as [l1_outer] ON [t].[Level1_Required_Id] = [l1_outer].[Id]

and then client projection on top of that

[maumar]

Fixed in 74b7e4d