Query: SubQueryMemberPushdown doesn't work for chained navigation properties

[maumar]

SubQueryMemberPushdown optimization can only optimize one member per pass. We should consider making it work for more complex scenarios with multiple properties chained off one another.

example:

l3s.Select(l3 => l3s.OrderBy(l => l.Id).First().OneToOne_Required_FK_Inverse.OneToOne_Required_FK_Inverse.Name)

initial QM:

from Level3 l3 in DbSet<Level3>
select 
    (from Level3 l in DbSet<Level3>
    order by [l].Id asc
    select [l]).First().OneToOne_Required_FK_Inverse.OneToOne_Required_FK_Inverse.Name

after first pushdown:

from Level3 l3 in DbSet<Level3>
select 
    (from Level3 l in DbSet<Level3>
    order by [l].Id asc
    select [l].OneToOne_Required_FK_Inverse).First().OneToOne_Required_FK_Inverse.Name

after nav rewite:

from Level3 l3 in DbSet<Level3>
select 
    (from Level3 l in DbSet<Level3>
    join Level2 l.OneToOne_Required_FK_Inverse in DbSet<Level2>
    on Property([l], "Level2_Required_Id") equals Property([l.OneToOne_Required_FK_Inverse], "Id")
    order by [l].Id asc
    select [l.OneToOne_Required_FK_Inverse]).First().OneToOne_Required_FK_Inverse.Name

after second pushdown:

from Level3 l3 in DbSet<Level3>
select 
    (from Level3 l in DbSet<Level3>
    join Level2 l.OneToOne_Required_FK_Inverse in DbSet<Level2>
    on Property([l], "Level2_Required_Id") equals Property([l.OneToOne_Required_FK_Inverse], "Id")
    order by [l].Id asc
    select [l.OneToOne_Required_FK_Inverse].OneToOne_Required_FK_Inverse).First().Name

This query ends up throwning NRE - we should be able to fully translate it though, push all the members inside the selector and only then run nav rewrite

[maumar]

After fixing unskip test in ComplexNavigationsOwnedQueryTestBase for #8248

[maumar]

this has been done as part of nav expansion rework. the remaining work is to extract the logic to a separate visitor

[divega]

@maumar to close this as fixed and create a new issue.

[maumar]

this is fixed in the current bits, filed #16180 to track the cleanup work