Query: we don't convert JOIN to APPLY for query with subquery inside argument to Take method

[maumar]

query:

ss.Set<City>().OrderBy(x => x.Name).Select(x => x.BornGears.OrderBy(g => g.FullName).Take(x.StationedGears.Any() ? 1 : 2))

sql:

SELECT [c].[Name], [t0].[Nickname], [t0].[SquadId], [t0].[AssignedCityName], [t0].[CityOfBirthName], [t0].[Discriminator], [t0].[FullName], [t0].[HasSoulPatch], [t0].[LeaderNickname], [t0].[LeaderSquadId], [t0].[Rank]
FROM [Cities] AS [c]
LEFT JOIN (
    SELECT [t].[Nickname], [t].[SquadId], [t].[AssignedCityName], [t].[CityOfBirthName], [t].[Discriminator], [t].[FullName], [t].[HasSoulPatch], [t].[LeaderNickname], [t].[LeaderSquadId], [t].[Rank]
    FROM (
        SELECT [g].[Nickname], [g].[SquadId], [g].[AssignedCityName], [g].[CityOfBirthName], [g].[Discriminator], [g].[FullName], [g].[HasSoulPatch], [g].[LeaderNickname], [g].[LeaderSquadId], [g].[Rank], ROW_NUMBER() OVER(PARTITION BY [g].[CityOfBirthName] ORDER BY [g].[FullName]) AS [row]
        FROM [Gears] AS [g]
    ) AS [t]
    WHERE [t].[row] <= CASE
        WHEN EXISTS (
            SELECT 1
            FROM [Gears] AS [g0]
            WHERE [c].[Name] = [g0].[AssignedCityName]) THEN 1
        ELSE 2
    END
) AS [t0] ON [c].[Name] = [t0].[CityOfBirthName]
ORDER BY [c].[Name], [t0].[CityOfBirthName], [t0].[FullName], [t0].[Nickname]

error:

SqlException : The multi-part identifier "c.Name" could not be bound.

note that converting to outer apply works just fine

SELECT [c].[Name], [t0].[Nickname], [t0].[SquadId], [t0].[AssignedCityName], [t0].[CityOfBirthName], [t0].[Discriminator], [t0].[FullName], [t0].[HasSoulPatch], [t0].[LeaderNickname], [t0].[LeaderSquadId], [t0].[Rank]
FROM [Cities] AS [c]
OUTER APPLY (
    SELECT [t].[Nickname], [t].[SquadId], [t].[AssignedCityName], [t].[CityOfBirthName], [t].[Discriminator], [t].[FullName], [t].[HasSoulPatch], [t].[LeaderNickname], [t].[LeaderSquadId], [t].[Rank]
    FROM (
        SELECT [g].[Nickname], [g].[SquadId], [g].[AssignedCityName], [g].[CityOfBirthName], [g].[Discriminator], [g].[FullName], [g].[HasSoulPatch], [g].[LeaderNickname], [g].[LeaderSquadId], [g].[Rank], ROW_NUMBER() OVER(PARTITION BY [g].[CityOfBirthName] ORDER BY [g].[FullName]) AS [row]
        FROM [Gears] AS [g]
    ) AS [t]
    WHERE [c].[Name] = [t].[CityOfBirthName] AND [t].[row] <= CASE
        WHEN EXISTS (
            SELECT 1
            FROM [Gears] AS [g0]
            WHERE [c].[Name] = [g0].[AssignedCityName]) THEN 1
        ELSE 2
    END
) AS [t0]
ORDER BY [c].[Name], [t0].[CityOfBirthName], [t0].[FullName], [t0].[Nickname]

[roji]

@maumar that looks similar to #32217, i.e. could this be a result of nav expansion on visiting the argument of Take()? If so, this would be a dup of that.

[maumar]

this is what happens after 32217 is fixed - there is another bug later in the pipeline - currently it produces untranslatable tree