Invalid ORDER BY clause is generated for query with projection that includes foreign key property

[jemiller0]

The following query fails with EF Core 2.1.1 with following exception.

var q = from e2 in oleContext.Encumbrances
        .OrderBy(e2 => e2.OriginCode)
        select new
        {
            //Origin1 = e2.OriginCode,
            Origin1 = e2.Origin.Code,
            e2.OriginCode,
        };

OleLibrary Verbose: 0 : Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='90']
SELECT [e2].[fs_origin_cd] AS [OriginCode]
FROM [gl_encumbrance_t] AS [e2]
ORDER BY [Origin1]
    ThreadId=1
    DateTime=2018-07-04T16:03:11.1093342Z
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'Origin1'.
   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)
   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 Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
   at OleConsoleApplication.Program.TestEncumbrancesQuery() in C:\Users\jemiller\Documents\Visual Studio 2017\Projects\Ole7\OleConsoleApplication\Program.cs:line 45
   at OleConsoleApplication.Program.Main(String[] args) in C:\Users\jemiller\Documents\Visual Studio 2017\Projects\Ole7\OleConsoleApplication\Program.cs:line 21
ClientConnectionId:b4644a78-d71d-4f6b-82ca-d9e5b3f72834
Error Number:207,State:1,Class:16

When using EF Core 2.0.2, the query works fine and produces the following SQL.

OleLibrary Verbose: 0 : Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='90']
SELECT [e2].[fs_origin_cd] AS [Origin1]
FROM [gl_encumbrance_t] AS [e2]
ORDER BY [Origin1]

I tested with the SQL Server, PostgreSQL, and Pomelo MySQL providers and the behavior is the same for all of them. Also, I tested on .NET Framework and .NET Core.

The problem looks like it has something to do with the fact that the query is doing a projection and is including the same foreign key value more than once. The above example is for a query that isolates and demonstrates the problem. The reason I am selecting the value like that twice is that I have a scaffolding generator application that I developed which generates web pages with grids. The generator generates the queries so that only the needed fields are selected. It includes foreign key values so that hyperlinks can be generated that link to the referenced objects. The hyperlink text is set to the DisplayColumn for the referenced entity. In some cases the DisplayColumn happens to be the foreign key in the referenced table. I just wanted to give some background as to why I am doing that. This has always worked with previous versions of EF Core and EF. I found that the behavior is the same whether the foreign key property in the referencing entity is used or the primary key property in the referenced entity is used. I am assuming that EF Core is eliminating the duplicates from the SQL query, but, it is not quite right with regard to the ORDER BY clause.

EF Core version: 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer 2.1.1, Npgsql.EntityFrameworkCore.PostgreSQL 2.1.1, Pomelo.EntityFrameworkCore.MySql 2.1.0-rc2-final
Operating system: Windows 10
IDE: Visual Studio 2017 15.7.4

The following file contains a project that demonstrates the problem.

Ole7201807041149.zip

[jemiller0]

Thanks. I tried that, but, it gave the same results. Jon From: Igo Ventura <[email protected]> Sent: Wednesday, July 4, 2018 12:54 PM To: aspnet/EntityFrameworkCore <[email protected]> Cc: Jon Miller <[email protected]>; Author <[email protected]> Subject: Re: [aspnet/EntityFrameworkCore] Invalid ORDER BY clause is generated for query with projection that includes foreign key property (#12551) I think that the error is at the OrderBy in linq: var q = from e2 in oleContext.Encumbrances // .OrderBy(e2 => e2.OriginCode) causes error .OrderBy(e2 => e2.Origin.Code) // try this select new { //Origin1 = e2.OriginCode, Origin1 = e2.Origin.Code, e2.OriginCode, }; I think it's missing the dot. ASAP i'll test the project in the issue description and check. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<#12551 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/APMZ5fRRvyRq_IOaFwQzkrYJmcZP_kYhks5uDQFPgaJpZM4VC2kV>.

[ajcvickers]

@smitpatel to de-dupe.

[smitpatel]

Duplicate of #12180

[smitpatel]

Added regression test in 956f339