Invalid column name: orderby uses a column alias that does not exist

[mcorthout]

Under rather peculiar circumstances, orderby tries to use a column alias in the ORDER BY clause that is not present in the SELECT clause.

Exception:

fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (29ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [item].[Id], [item].[Name] AS [AnotherName]
      FROM [Items] AS [item]
      ORDER BY [item].[Id], [SomeName]
System.Data.SqlClient.SqlException (0x80131904): Invalid column name 'SomeName'.
   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)

Steps to reproduce

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Console;
using System;
using System.ComponentModel.DataAnnotations;
using System.Linq;

namespace EFCoreBugTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new Context())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();

                var problem = from item in context.Items
                              orderby item.Id, item.Name
                              select new SomeOtherObject
                              {
                                  Id = item.Id,
                                  SomeName = item.Name,
                                  AnotherName = item.Name
                              };

                var boom = problem.ToList();
            }

            Console.ReadLine();

        }
    }

    public class Context : DbContext
    {
        public static readonly LoggerFactory logger = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) });

        public DbSet<Item> Items { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLoggerFactory(logger);
            optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=master;Database=demo;Integrated Security=True");
        }        
    }

    public class Item
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class SomeOtherObject
    {
        public int Id { get; set; }
        public string SomeName { get; set; }
        public string AnotherName { get; set; }
    }
}

Expected result

SELECT [item].[Id], [item].[Name] AS [AnotherName]
FROM [Items] AS [item]
ORDER BY [item].[Id], [item].[Name] 

Actual result

SELECT [item].[Id], [item].[Name] AS [AnotherName]
FROM [Items] AS [item]
ORDER BY [item].[Id], [SomeName]

Further technical details

EF Core version: 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 7
IDE: Visual Studio 2017 15.7.2

[ajcvickers]

@mcorthout Do you know if this was working with 2.0 release?

[smitpatel]

@ajcvickers - Its a regression from 2.0.2

[ajcvickers]

@divega Looks like a reasonably common case--should we patch?

@smitpatel Do you know if there is a workaround?

[smitpatel]

It is slightly uncommon to include same property multiple times in the selector. (alongwith the same property being used in ordering which is necessary for bug to reproduce)
The easiest work-around would be to remove duplicated property reference. (In this case remove AnotherName = item.Name line). In case of DTO like above it may not be always possible in that case the query could be rewritten like following:

                var problem = from a in 
                              (from item in context.Items
                               orderby item.Id, item.Name
                              select new { item.Id, item.Name }
                              ).ToList()
                               select new SomeOtherObject
                              {
                                  Id = a.Id,
                                  SomeName = a.Name,
                                  AnotherName = a.Name
                              };

It cause client eval for DTO part but it will not pull any additional data from server. In case of the DTO is inside subquery it is going to cause client eval anyway so above work-around does not worsen the query.

[ajcvickers]

Triage: after considering the fix, we think the risk is low enough that we can take this as a patch.

[fschlaef]

I also had this issue. The workaround works, but keep in mind that this :

context.Address.Select(a => new
{
  a.ClientFk,
  a.ClientFkNavigation.Id,
});

Will also throw the error because it is effectively the same property, even though it is not obvious at first glance.

[ajcvickers]

This is approved for 2.1.3. Do not merge yet; branch is expected to open Monday.

Given these issues have been parked for some time, please be careful to ensure the correct commits get into the correct branches.

[ajcvickers]

@smitpatel This issue is approved for patch and the release\2.1 branch is now open for merging. Please ensure:

• The appropriate changes get into both the release and dev branches
• Quirking is included for the release branch only

[smitpatel]

Ported to release in 23e9fc8
Already merged to dev in 995822a