[2.1rc]SqlException: Each GROUP BY expression must contain at least one column that is not an external reference.(translated with google)

[yyjdelete]

Describe what is not working as expected.

EF2.1-rc1 generate wrong SQL when group by with anonymous class mixed with db properties and constant.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message: System.Data.SqlClient.SqlException:“Each GROUP BY expression must contain at least one column that is not an external reference." (translated to English)
Stack trace:

Steps to reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

public class Device{ int DeviceId {get;set;} }

                var tmp = ctx.Device.GroupBy(z => new { z.DeviceId, PlaceHolder = 1 })
                    .Select(d => d.Sum(d1 => d1.DeviceId))
                    .ToList();

The SQL generated is wrong, and can not be executed.

      SELECT SUM([z].[Device_ID])
      FROM [Device] AS [z]
      GROUP BY [z].[Device_ID], 1

And it should be some thing like

      SELECT SUM([t].[DeviceId])
      FROM (
          SELECT [d].[Device_ID] AS [DeviceId], 1 AS [PlaceHolder]
          FROM [Device] AS [d]
      ) AS [t]
      GROUP BY [t].[DeviceId], [t].[PlaceHolder]

Further technical details

EF Core version: (found in project.csproj or packages.config) 2.1.0-rc1/2.2.0-preview1-34228(myget)
Database Provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)Microsoft.EntityFrameworkCore.SqlServer
Operating system: win10-x64
IDE: (e.g. Visual Studio 2017 15.4)

[ajcvickers]

@yyjdelete What is the reason for using "PlaceHolder = 1" like this?

[yyjdelete]

@ajcvickers
I dynamic generate an Express to group by with an modified(zzzprojects/System.Linq.Dynamic.Core#165) version of System.Linq.Dynamic.Core. And to workaround #11905, I always added an placeholder to the latest in case there is no fields.

Ok, I recrogized that I can simplely use .GroupBy(d=>1) only if there is no fields, and object in other case. PlaceHolder is not really needed in the case.

[ajcvickers]

@smitpatel to reference this from the other more general issue. We will fix this as part of doing that work.

[smitpatel]

#12089

[smitpatel]

In 3.1 we don't add constant/parameters into GroupBy clause. If they are used in projection, we can add them into projection directly.

Generated SQL for above case.

      SELECT SUM([d].[DeviceId])
      FROM [Device] AS [d]
      GROUP BY [d].[DeviceId]