Query: invalid sql produced for query with join on a navigation subquery with composite join key and orderby on the inner sequence

[maumar]

repro:

                var query = from g in ctx.Gears
                            join t in ctx.Tags.OrderBy(tt => tt.Id) on g.FullName equals t.Gear.FullName
                            select new
                            {
                                g.FullName,
                                t.Note
                            };

OrderBy(tt => tt.Id) is what causes the problem

query model:

from Gear g in DbSet<Gear>
join CogTag t in 
    from CogTag tt in DbSet<CogTag>
    order by [tt].Id asc
    select [tt]
on [g].FullName equals 
    (from Gear subQuery in DbSet<Gear>
    where  ?= new AnonymousObject(new object[]
    { 
        (object)Property([subQuery], "Nickname"), 
        (object)Property([subQuery], "SquadId") 
    }) == new AnonymousObject(new object[]
    { 
        (object)Property([t], "GearNickName"), 
        (object)Property([t], "GearSquadId") 
    }) =? 
    select [subQuery].FullName).FirstOrDefault()
select new { 
    [g].FullName, 
    [t].Note
 }

query plan:

(QueryContext queryContext) => IEnumerable<<>f__AnonymousType131<string, string>> _InterceptExceptions(
|__ source: IEnumerable<<>f__AnonymousType131<string, string>> _Select(
|   |__ source: IEnumerable<TransparentIdentifier<ValueBuffer, ValueBuffer>> _Join(
|   |   |__ outer: IEnumerable<ValueBuffer> _ShapedQuery(
|   |   |   |__ queryContext: queryContext, 
|   |   |   |__ shaperCommandContext: SelectExpression: 
|   |   |   |       SELECT [g].[FullName]
|   |   |   |       FROM [Gears] AS [g]
|   |   |   |       WHERE [g].[Discriminator] IN (N'Officer', N'Gear'), 
|   |   |   |__ shaper: ValueBufferShaper), 
|   |   |__ inner: IEnumerable<ValueBuffer> _ShapedQuery(
|   |   |   |__ queryContext: queryContext, 
|   |   |   |__ shaperCommandContext: SelectExpression: 
|   |   |   |       SELECT [tt0].[GearNickName], [tt0].[GearSquadId]
|   |   |   |       FROM [Tags] AS [tt0]
|   |   |   |       ORDER BY [tt0].[Id], 
|   |   |   |__ shaper: ValueBufferShaper), 
|   |   |__ outerKeySelector: (ValueBuffer g) => string TryReadValue(g, 0, Gear.FullName), 
|   |   |__ innerKeySelector: (ValueBuffer t) => string FirstOrDefault(IEnumerable<string> _Select(
|   |   |   |__ source: IEnumerable<ValueBuffer> _Where(
|   |   |   |   |__ source: IEnumerable<ValueBuffer> _ShapedQuery(
|   |   |   |   |   |__ queryContext: queryContext, 
|   |   |   |   |   |__ shaperCommandContext: SelectExpression: 
|   |   |   |   |   |       SELECT [subQuery].[Nickname], [subQuery].[SquadId], [subQuery].[FullName], [subQuery].[Note]
|   |   |   |   |   |       FROM [Gears] AS [subQuery]
|   |   |   |   |   |       WHERE [subQuery].[Discriminator] IN (N'Officer', N'Gear'), 
|   |   |   |   |   |__ shaper: ValueBufferShaper), 
|   |   |   |   |__ predicate: (ValueBuffer subQuery) =>  ?= new AnonymousObject(new object[]
|   |   |   |       { 
|   |   |   |           (object)string TryReadValue(subQuery, 0, Gear.Nickname), 
|   |   |   |           (object)int TryReadValue(subQuery, 1, Gear.SquadId) 
|   |   |   |       }) == new AnonymousObject(new object[]
|   |   |   |       { 
|   |   |   |           (object)string TryReadValue(t, 0, CogTag.GearNickName), 
|   |   |   |           (object)Nullable<int> TryReadValue(t, 1, CogTag.GearSquadId) 
|   |   |   |       }) =? ), 
|   |   |   |__ selector: (ValueBuffer subQuery) => string TryReadValue(subQuery, 2, Gear.FullName))), 
|   |   |__ resultSelector: (ValueBuffer g | ValueBuffer t) => TransparentIdentifier<ValueBuffer, ValueBuffer> CreateTransparentIdentifier(
|   |       |__ outer: g, 
|   |       |__ inner: t)), 
|   |__ selector: (TransparentIdentifier<ValueBuffer, ValueBuffer> t0) => new { 
|           string TryReadValue(t0.Outer, 0, Gear.FullName), 
|           string TryReadValue(t0.Inner, 3, CogTag.Note)
|        }), 
|__ contextType: TestModels.GearsOfWarModel.GearsOfWarContext, 
|__ logger: DiagnosticsLogger<Query>, 
|__ queryContext: queryContext)

exception:

System.Data.SqlClient.SqlException : Invalid column name 'Note'.
	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.WhereSelectEnumerableIterator`2.MoveNext()
	at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
	at lambda_method(Closure , ValueBuffer )
	at System.Linq.Lookup`2.CreateForJoin(IEnumerable`1 source, Func`2 keySelector, IEqualityComparer`1 comparer)
	at System.Linq.Enumerable.<JoinIterator>d__38`4.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)