Query: invalid sql generated for complex query with groupjoin on a subquery containing another groupjoin with order by on one of the inner sequences

[maumar]

query:

                var query = from x in
                        (from l1 in ctx.LevelOne
                         join l2 in ctx.LevelTwo.OrderBy(ee => ee.Date) on l1.Id equals l2.Level1_Optional_Id into grouping
                         from l2 in grouping.DefaultIfEmpty()
                         orderby l1.Id
                         select l2).Take(2)
                            join l1_outer in ctx.LevelOne on x.Level1_Optional_Id equals l1_outer.Id into grouping_outer
                            from l1_outer in grouping_outer.DefaultIfEmpty()
                            select l1_outer.Name;

                var result = query.ToList();

query plan:

(QueryContext queryContext) => IEnumerable<string> _InterceptExceptions(
|__ source: IEnumerable<string> _Select(
|   |__ source: IEnumerable<TransparentIdentifier<TransparentIdentifier<Level2, IEnumerable<ValueBuffer>>, ValueBuffer>> _SelectMany(
|   |   |__ source: IEnumerable<TransparentIdentifier<Level2, IEnumerable<ValueBuffer>>> _GroupJoin(
|   |   |   |__ outer: IEnumerable<Level2> _Select(
|   |   |   |   |__ source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, IEnumerable<Level2>>, Level2>> Take(
|   |   |   |   |   |__ source: IEnumerable<TransparentIdentifier<TransparentIdentifier<ValueBuffer, IEnumerable<Level2>>, Level2>> _SelectMany(
|   |   |   |   |   |   |__ source: IEnumerable<TransparentIdentifier<ValueBuffer, IEnumerable<Level2>>> _GroupJoin(
|   |   |   |   |   |   |   |__ outer: IEnumerable<ValueBuffer> _ShapedQuery(
|   |   |   |   |   |   |   |   |__ queryContext: queryContext, 
|   |   |   |   |   |   |   |   |__ shaperCommandContext: SelectExpression: 
|   |   |   |   |   |   |   |   |       SELECT [l10].[Id], [l10].[Level1_Optional_Id]
|   |   |   |   |   |   |   |   |       FROM [LevelOne] AS [l10]
|   |   |   |   |   |   |   |   |       ORDER BY [l10].[Id], 
|   |   |   |   |   |   |   |   |__ shaper: ValueBufferShaper), 
|   |   |   |   |   |   |   |__ inner: IEnumerable<Level2> _ShapedQuery(
|   |   |   |   |   |   |   |   |__ queryContext: queryContext, 
|   |   |   |   |   |   |   |   |__ shaperCommandContext: SelectExpression: 
|   |   |   |   |   |   |   |   |       SELECT [ee2].[Id], [ee2].[Date], [ee2].[Level1_Optional_Id], [ee2].[Level1_Required_Id], [ee2].[Name], [ee2].[OneToMany_Optional_Inverse2Id], [ee2].[OneToMany_Optional_Self_Inverse2Id], [ee2].[OneToMany_Required_Inverse2Id], [ee2].[OneToMany_Required_Self_Inverse2Id], [ee2].[OneToOne_Optional_PK_Inverse2Id], [ee2].[OneToOne_Optional_Self2Id]
|   |   |   |   |   |   |   |   |       FROM [LevelTwo] AS [ee2]
|   |   |   |   |   |   |   |   |       ORDER BY [ee2].[Date], 
|   |   |   |   |   |   |   |   |__ shaper: BufferedEntityShaper<Level2>), 
|   |   |   |   |   |   |   |__ outerKeySelector: (ValueBuffer l1) => (Nullable<int>)int TryReadValue(l1, 0, Level1.Id), 
|   |   |   |   |   |   |   |__ innerKeySelector: (Level2 l2) => l2.Level1_Optional_Id, 
|   |   |   |   |   |   |   |__ resultSelector: (ValueBuffer l1 | IEnumerable<Level2> grouping) => TransparentIdentifier<ValueBuffer, IEnumerable<Level2>> CreateTransparentIdentifier(
|   |   |   |   |   |   |       |__ outer: l1, 
|   |   |   |   |   |   |       |__ inner: grouping)), 
|   |   |   |   |   |   |__ collectionSelector: (TransparentIdentifier<ValueBuffer, IEnumerable<Level2>> t0) => IEnumerable<Level2> DefaultIfEmpty(t0.Inner), 
|   |   |   |   |   |   |__ resultSelector: (TransparentIdentifier<ValueBuffer, IEnumerable<Level2>> t0 | Level2 l2) => TransparentIdentifier<TransparentIdentifier<ValueBuffer, IEnumerable<Level2>>, Level2> CreateTransparentIdentifier(
|   |   |   |   |   |       |__ outer: t0, 
|   |   |   |   |   |       |__ inner: l2)), 
|   |   |   |   |   |__ count: int GetParameterValue(
|   |   |   |   |       |__ queryContext: queryContext, 
|   |   |   |   |       |__ parameterName: "__p_0")), 
|   |   |   |   |__ selector: (TransparentIdentifier<TransparentIdentifier<ValueBuffer, IEnumerable<Level2>>, Level2> t1) => t1.Inner), 
|   |   |   |__ inner: IEnumerable<ValueBuffer> _ShapedQuery(
|   |   |   |   |__ queryContext: queryContext, 
|   |   |   |   |__ shaperCommandContext: SelectExpression: 
|   |   |   |   |       SELECT [l1_outer].[Id], [l1_outer].[Name]
|   |   |   |   |       FROM [LevelOne] AS [l1_outer], 
|   |   |   |   |__ shaper: ValueBufferShaper), 
|   |   |   |__ outerKeySelector: (Level2 x) => x?.Level1_Optional_Id, 
|   |   |   |__ innerKeySelector: (ValueBuffer l1_outer) => (Nullable<int>)int TryReadValue(l1_outer, 0, Level1.Id), 
|   |   |   |__ resultSelector: (Level2 x | IEnumerable<ValueBuffer> grouping_outer) => TransparentIdentifier<Level2, IEnumerable<ValueBuffer>> CreateTransparentIdentifier(
|   |   |       |__ outer: x, 
|   |   |       |__ inner: grouping_outer)), 
|   |   |__ collectionSelector: (TransparentIdentifier<Level2, IEnumerable<ValueBuffer>> t0) => IEnumerable<ValueBuffer> DefaultIfEmpty(t0.Inner), 
|   |   |__ resultSelector: (TransparentIdentifier<Level2, IEnumerable<ValueBuffer>> t0 | ValueBuffer l1_outer) => TransparentIdentifier<TransparentIdentifier<Level2, IEnumerable<ValueBuffer>>, ValueBuffer> CreateTransparentIdentifier(
|   |       |__ outer: t0, 
|   |       |__ inner: l1_outer)), 
|   |__ selector: (TransparentIdentifier<TransparentIdentifier<Level2, IEnumerable<ValueBuffer>>, ValueBuffer> t1) => !(t1.Inner.IsEmpty) ? string TryReadValue(t1.Inner, 1, Level1.Name) : default(string)), 
|__ contextType: TestModels.ComplexNavigationsModel.ComplexNavigationsContext, 
|__ logger: DiagnosticsLogger<Query>, 
|__ queryContext: queryContext)

exception:

System.Data.SqlClient.SqlException : Invalid column name 'Level1_Optional_Id'.
	at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
	at System.Data.SqlClient.SqlInternalConnection.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, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
	at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
	at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
	at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
	at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
	at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
	at System.Data.Common.DbCommand.ExecuteReader()
	at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
	at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
	at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
	at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
	at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
	at System.Linq.Enumerable.<GroupJoinIterator>d__41`4.MoveNext()
	at System.Linq.Enumerable.<SelectManyIterator>d__23`3.MoveNext()
	at System.Linq.Enumerable.<TakeIterator>d__25`1.MoveNext()
	at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
	at System.Linq.Enumerable.<GroupJoinIterator>d__41`4.MoveNext()
	at System.Linq.Enumerable.<SelectManyIterator>d__23`3.MoveNext()
	at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
	at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
	at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
	at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)