Query: Simplify aliases for complex projection if being used in order by clause

[smitpatel]

For query like this

selection = db.Nodes.Include(x => x.NodeRecords)
        .Select(s => new HighNode()
        {
            Name = s.Name,
            Change = s.NodeRecords.OrderByDescending(o => o.NodeRecordId).Skip(days).FirstOrDefault().Change
        }).OrderByDescending(t => t.Change).Take(100).ToList();

We generate Sql like this

SELECT TOP(@__p_1) [x].[Name], (
    SELECT [o0].[Change]
    FROM [NodeRecords] AS [o0]
    WHERE [x].[Name] = [o0].[Node_Name]
    ORDER BY [o0].[NodeRecordId] DESC
    OFFSET @__days_0 ROWS FETCH NEXT 1 ROWS ONLY
)
FROM [Nodes] AS [x]
ORDER BY (
    SELECT [o].[Change]
    FROM [NodeRecords] AS [o]
    WHERE [x].[Name] = [o].[Node_Name]
    ORDER BY [o].[NodeRecordId] DESC
    OFFSET @__days_0 ROWS FETCH NEXT 1 ROWS ONLY
) DESC

We can just alias the subquery in projection and use alias in order by clause as enhancement.

For model and more info see #6257

[smitpatel]

Another expression to alias is COALESCE

[smitpatel]

This is a necessity for the case of coalesce when it is inside a subquery because we need to copy order by on outer query but the columns for coalesce are not projected out hence generates invalid query like this.
Disabling test Select_take_skip_null_coalesce_operator

exec sp_executesql N'SELECT [t].*
FROM (
    SELECT TOP(@__p_0) [c].[CustomerID], [c].[CompanyName], COALESCE([c].[Region], N''ZZ'') AS [c0]
    FROM [Customers] AS [c]
    ORDER BY COALESCE([c].[Region], N''ZZ'')
) AS [t]
ORDER BY COALESCE([t].[Region], N''ZZ'')
OFFSET @__p_1 ROWS',N'@__p_0 int,@__p_1 int',@__p_0=10,@__p_1=5

[smitpatel]

@anpete @maumar - I was looking this in my current work. For complex expressions, we are able to alias the projection and use it in order by but for subquery it is failing because we generate different select expression (essentially the table aliases are different) because our subquerymodelvisitor will run twice on same qm. Thoughts where we should optimize this?

[smitpatel]

@anpete @maumar - For all cases except subquery, alias-ing of ordering should be working. As explained above, to alias select expression in order by we need that subqueryExpression referencing same QM generating same SelectExpression. Do we want to keep this issue open now? Or merge into the larger issue?

[divega]

Do we want to keep this issue open now? Or merge into the larger issue?

@smitpatel this got closed automatically when you pushed the commit, that might explain why no one answered 😄 Should it be reopened? Otherwise closed as fixed?

[smitpatel]

We do alias complex projections appearing in projection & order by both and refer to the alias in order by clause.
For subqueries, the generated select expressions are different so we have no way to match them and use alias.
All possible work for this issue has been done at this point.