Query: optimize OrderBy on navigation property

[MaklaCof]

OrderBy could be optimized on navigational property.

For example, this query

this.context.Parts
    .Select(t => new ViewPartModel()
            {
                id = t.Id,
                title = t.Titles
                    .OrderBy(t2 => t2.IsDefault)
                    .Select(t2 => t2.Translates
                        .Where(t3 => t3.LangISOCode == Lang)
                        .Select(t3 => t3.Title)
                        .FirstOrDefault())
                    .FirstOrDefault()
    }).OrderBy(t => t.title)
    .Skip(0)
    .Take(50)
    .ToArray()

produce this SQL:

SELECT [t].[Id], (
    SELECT TOP(1) (
        SELECT TOP(1) [t30].[Title]
        FROM [VarDesign_Parts-Title_Translates] AS [t30]
        WHERE ([t30].[LangISOCode] = 'SLV') AND ([t20].[Id] = [t30].[PartTitleId])
    )
    FROM [VarDesign_Part-Titles] AS [t20]
    WHERE [t].[Id] = [t20].[PartId]
    ORDER BY [t20].[IsDefault]
) AS [title]
FROM [VarDesign_Parts] AS [t]
ORDER BY (
    SELECT TOP(1) (
        SELECT TOP(1) [t3].[Title]
        FROM [VarDesign_Parts-Title_Translates] AS [t3]
        WHERE ([t3].[LangISOCode] = 'SLV') AND ([t2].[Id] = [t3].[PartTitleId])
    )
    FROM [VarDesign_Part-Titles] AS [t2]
    WHERE [t].[Id] = [t2].[PartId]
    ORDER BY [t2].[IsDefault]
)
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

but better would be:

SELECT [t].[Id], (
    SELECT TOP(1) (
        SELECT TOP(1) [t30].[Title]
        FROM [VarDesign_Parts-Title_Translates] AS [t30]
        WHERE ([t30].[LangISOCode] = 'SLV') AND ([t20].[Id] = [t30].[PartTitleId])
    )
    FROM [VarDesign_Part-Titles] AS [t20]
    WHERE [t].[Id] = [t20].[PartId]
    ORDER BY [t20].[IsDefault]
) AS [title]
FROM [VarDesign_Parts] AS [t]
ORDER BY [title]
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

There is no need to select fields again for ORDER BY, just use already returned field if exists.
I am attaching screenshot for ClientStatistics in Management Studio. Trial 1 and 2 are SQL created by EF. Trial 3 and 4 are when I manually correct sort. The difference in execution is approx half faster.

Further technical details

EF Core version: 2.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017

[smitpatel]

Duplicate of #6703

De-duping a SelectExpression in order by is non-trivial task. It is same QueryModel but when we translate it both times, we generate different aliases to avoid name clash. So inside SelectExpression we cannot match and de-dupe it like we do with other complex expression.

@MaklaCof - Given there is perf differences, we will discuss this in team in next triage meeting. Thanks for detailed info on perf.

[MaklaCof]

Given there is perf differences, we will discuss this in team in next triage meeting. Thanks for detailed info on perf.

Great. Thank you.
I imagine this is common scenario when you display result in grid (together with columns from detail tables) and you want to order by column from detail table.

[smitpatel]

Related #20291