Include().ThenInclude and order by throws exception

[Tasteful]

Tested with and without #5443

[Fact]
        public virtual void Then_include_collection_order_by_collection_column()
        {
            using (var context = CreateContext())
            {
                var customer
                    = context.Set<Customer>()
                        .Include(c => c.Orders)
                        .ThenInclude(o => o.OrderDetails)
                        .Where(c => c.CustomerID.StartsWith("W"))
                        .OrderByDescending(c => c.Orders.OrderByDescending(oo => oo.OrderDate).FirstOrDefault().OrderDate)
                        .FirstOrDefault();

                Assert.NotNull(customer);
                Assert.NotNull(customer.Orders);
                Assert.NotEmpty(customer.Orders);
                Assert.NotNull(customer.Orders.First().OrderDetails);
                Assert.NotEmpty(customer.Orders.First().OrderDetails);
            }
        }

throws exception (this exception after #5443 is applied)

Test Name:  IncludeSqlServerTest.Then_include_collection_order_by_collection_column
Test FullName:  Microsoft.EntityFrameworkCore.SqlServer.FunctionalTests.IncludeSqlServerTest.Then_include_collection_order_by_collection_column
Test Source:    C:\Git\EntityFramework\test\Microsoft.EntityFrameworkCore.SqlServer.FunctionalTests\SqlAzure\Model\Product.cs : line 27
Test Outcome:   Failed
Test Duration:  0:00:01,783

Result StackTrace:  
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection) in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore.Relational\Storage\Internal\RelationalCommand.cs:line 222
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection) in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore.Relational\Storage\Internal\RelationalCommand.cs:line 95
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext() in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore.Relational\Query\Internal\QueryingEnumerable.cs:line 69
   at Microsoft.EntityFrameworkCore.Query.Internal.IncludeCollectionIterator.<GetRelatedValues>d__4.MoveNext() in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore.Relational\Query\Internal\IncludeCollectionIterator.cs:line 28
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.Include(QueryContext queryContext, Object entity, IReadOnlyList`1 navigationPath, IReadOnlyList`1 relatedEntitiesLoaders, Int32 currentNavigationIndex, Boolean queryStateManager) in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore\Query\Internal\QueryBuffer.cs:line 160
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.<>c__DisplayClass11_0.<Include>b__0(EntityLoadInfo eli) in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore\Query\Internal\QueryBuffer.cs:line 170
   at System.Linq.Enumerable.<>c__DisplayClass149_0`3.<CombineSelectors>b__0(TSource x)
   at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()
   at System.Linq.Enumerable.WhereEnumerableIterator`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.Include(QueryContext queryContext, Object entity, IReadOnlyList`1 navigationPath, IReadOnlyList`1 relatedEntitiesLoaders, Int32 currentNavigationIndex, Boolean queryStateManager) in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore\Query\Internal\QueryBuffer.cs:line 160
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryBuffer.Include(QueryContext queryContext, Object entity, IReadOnlyList`1 navigationPath, IReadOnlyList`1 relatedEntitiesLoaders, Boolean queryStateManager) in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore\Query\Internal\QueryBuffer.cs:line 134
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_Include>d__30`1.MoveNext() in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore.Relational\Query\QueryMethodProvider.cs:line 335
   at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
   at lambda_method(Closure , QueryContext )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass19_1`1.<CompileQuery>b__1(QueryContext qc) in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore\Query\Internal\QueryCompiler.cs:line 168
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore\Query\Internal\QueryCompiler.cs:line 82
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore\Query\Internal\EntityQueryProvider.cs:line 37
   at Microsoft.EntityFrameworkCore.Specification.Tests.IncludeTestBase`1.Then_include_collection_order_by_collection_column() in C:\Git\EntityFramework\src\Microsoft.EntityFrameworkCore.Specification.Tests\IncludeTestBase.cs:line 501
Result Message: The multi-part identifier "c.CustomerID" could not be bound.

IncludeExpressionVisitor is creating the following sql when debug and the problem is probably in the LiftOrderBy method that not converting the subselect order by into AliasExpression.

SELECT [o1].[OrderID], [o1].[ProductID], [o1].[Discount], [o1].[Quantity], [o1].[UnitPrice]
FROM [Order Details] AS [o1]
INNER JOIN (
    SELECT DISTINCT (
        SELECT TOP(1) [oo].[OrderDate]
        FROM [Orders] AS [oo]
        WHERE [c].[CustomerID] = [oo].[CustomerID]
        ORDER BY [oo].[OrderDate] DESC
    ), [c2].[CustomerID], [o0].[OrderID]
    FROM [Orders] AS [o0]
    INNER JOIN (
        SELECT DISTINCT TOP(1) (
            SELECT TOP(1) [oo].[OrderDate]
            FROM [Orders] AS [oo]
            WHERE [c].[CustomerID] = [oo].[CustomerID]
            ORDER BY [oo].[OrderDate] DESC
        ), [c].[CustomerID]
        FROM [Customers] AS [c]
        WHERE [c].[CustomerID] LIKE N'W' + N'%'
        ORDER BY (
            SELECT TOP(1) [oo].[OrderDate]
            FROM [Orders] AS [oo]
            WHERE [c].[CustomerID] = [oo].[CustomerID]
            ORDER BY [oo].[OrderDate] DESC
        ) DESC, [c].[CustomerID]
    ) AS [c2] ON [o0].[CustomerID] = [c2].[CustomerID]
) AS [o00] ON [o1].[OrderID] = [o00].[OrderID]
ORDER BY (
    SELECT TOP(1) [oo].[OrderDate]
    FROM [Orders] AS [oo]
    WHERE [c].[CustomerID] = [oo].[CustomerID]
    ORDER BY [oo].[OrderDate] DESC
) DESC, [o00].[CustomerID], [o00].[OrderID]

@anpete @smitpatel Separate PR or include in the #5443?

[smitpatel]

@Tasteful - To see exactly what is wrong I need to debug but looking at above query,
First Order By subquery is not aliased. Not sure if that would happen in LiftOrderBy only.
The exception being throw because the subquery is being put outside as is without changing the alias therefore [c].[CustomerID] which should be changed to [o00].[CustomerID] not happening.

Since both are similar issue (making alias for order by subquery), If you want to fix it include it in same PR #5443