Variable in a group JOIN clause added to ORDER BY as a SQL variable

[wc-matteo]

Steps to reproduce

from t1 in _dbContext.Rooms
join t2 in _dbContext.ContentRooms on new { id = t1.RoomsId, lang = _languageId } equals new {id = t2.RoomsId, lang = t2.LanguageId} into gj
from subt2 in gj.DefaultIfEmpty()
select new { t1.RoomsId, Name = (subt2 != null ? subt2.Name : null) };

SELECT [t1].[RoomsId], [t1].[AccomodationsId], [t1].[MinDays], [t1].[PeopleValidForEnd], [t1].[PeopleValidForStart], [t1].[PriceTypesId], [t2].[RoomsId], [t
2].[LanguageId], [t2].[Description], [t2].[Name], [t2].[Subtitle]                                                                                                 
FROM [Rooms] AS [t1]                                                                                                                                        
LEFT JOIN [ContentRooms] AS [t2] ON ([t1].[RoomsId] = [t2].[RoomsId]) AND (@___languageId_0 = [t2].[LanguageId])                                            
ORDER BY [t1].[RoomsId], @___languageId_0          

The issue

A C# variable in a group join clause (in this case, _languageId) gets added to the ORDER BY statement as a SQL variable.

Exception

System.Data.SqlClient.SqlException: The SELECT item identified by the ORDER BY number 2 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
 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 bulkCopyHandl
er, 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)                                                                              
         at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2
 parameterValues, Boolean openConnection, Boolean closeConnection)                                                                                                
         at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues
, Boolean manageConnection)                                                                                                                                       
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext()                                                                 
         at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_GroupJoin>d__26`4.MoveNext()                                                                
         at System.Linq.Enumerable.<SelectManyIterator>d__163`3.MoveNext()                                                                                        
         at System.Linq.Enumerable.SelectEnumerableIterator`2.MoveNext()                                                                                          
         at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()                    
      ClientConnectionId:7014ceed-b429-4ab8-8af0-a8470783025e                                                                                                     
      Error Number:1008,State:1,Class:16                         

The same query with a literal value sometimes work:

...
join t2 in _dbContext.ContentRooms on new { id = t1.RoomsId, lang = 2 } equals new {id = t2.RoomsId, lang = t2.LanguageId} into gj
...

...                                                                                                                                 
LEFT JOIN [ContentRooms] AS [t2] ON ([t1].[RoomsId] = [t2].[RoomsId]) AND (2 = [t2].[LanguageId])                                                           
ORDER BY [t1].[RoomsId], 2       

Further technical details

EF Core version: 1.0.1

Related

#6145

#3036

[wc-matteo]

Is there a way to make this work with only navigation properties?

In this case there are two tables: Rooms and ContentRooms; the latter containing the localized content of the former, with as key the Room ID and the Language ID.

I guess the question would be if there's a way to pre-filter navigation properties by some criteria... or to have a virtual Language ID key column in Rooms that takes the value of the language one needs...

---

BY THE WAY

I'm currently using:

_dbContext.Rooms
    .GroupJoin(_dbContext.ContentRooms
        .Where(t => t.LanguageId == _languageId),
        t => t.RoomsId, t => t.RoomsId, (t, c) => new {
            Rooms = t,
            ContentRooms = c
        }
    )
    .SelectMany(t => t.ContentRooms.DefaultIfEmpty(), (j, t) => new {
        Rooms = j.Rooms,
        ContentRooms = t ?? new ContentRooms()
    })
    .Select(j => new {
        j.Rooms.RoomsId,
        Name = j.ContentRooms.Name
    })

which works and outputs this (pretty good) SQL:

SELECT [t].[RoomsId], [t].[AccomodationsId], [t].[MinDays], [t].[PeopleValidForEnd], [t].[PeopleValidForStart], [t].[PriceTypesId], [t2].[RoomsId], [t2].[LanguageId], [t2].[Description], [t2].[Name], [t2].[Subtitle]
FROM [Rooms] AS [t]
LEFT JOIN (
    SELECT [t1].[RoomsId], [t1].[LanguageId], [t1].[Description], [t1].[Name], [t1].[Subtitle]
    FROM [ContentRooms] AS [t1]
    WHERE [t1].[LanguageId] = @___languageId_0
) AS [t2] ON [t].[RoomsId] = [t2].[RoomsId]
ORDER BY [t].[RoomsId]

_{ContentRooms is also a navigation property defined on Rooms as public List<ContentRooms> ContentRooms { get; set; } and wired up with the fluent API.}

---

Specifying .AsNoTracking() on _dbContext.ContentRooms adds a (useless) subquery ([t1]):

SELECT [t].[RoomsId], [t].[AccomodationsId], [t].[MinDays], [t].[PeopleValidForEnd], [t].[PeopleValidForStart], [t].[PriceTypesId], [t2].[RoomsId], [t2].[LanguageId], [t2].[Description], [t2].[Name], [t2].[Subtitle]
FROM [Rooms] AS [t]
LEFT JOIN (
    SELECT [t1].[RoomsId], [t1].[LanguageId], [t1].[Description], [t1].[Name], [t1].[Subtitle]
    FROM (
        SELECT [c2].[RoomsId], [c2].[LanguageId], [c2].[Description], [c2].[Name], [c2].[Subtitle]
        FROM [ContentRooms] AS [c2]
    ) AS [t1]
    WHERE [t1].[LanguageId] = @___languageId_0
) AS [t2] ON [t].[RoomsId] = [t2].[RoomsId]
ORDER BY [t].[RoomsId]

[smitpatel]

Generated Sql after fix:

exec sp_executesql N'SELECT [t1].[Id], [t2].[Id], [t2].[Language], [t2].[Name], [t2].[RoomId]
FROM [Rooms] AS [t1]
LEFT JOIN [ContentRooms] AS [t2] ON ([t1].[Id] = [t2].[RoomId]) AND (@__languageId_0 = [t2].[Language])
ORDER BY [t1].[Id], (SELECT 1)',N'@__languageId_0 nvarchar(4000)',@__languageId_0=N'en'