EFCore translates query with GROUP BY and JOIN into invalid SQL

[in10se]

EFCore translates LINQ query into invalid T-SQL code.

Exception message:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'AS'.

Steps to reproduce

//code with sensitive fields/names changed
var allowedIds = new int[] {}; //static array of int ids

var query = (from c in dbContext.Calculations
				group c by c.Period into rp
				join x in dbContext.Calculations on rp.Key equals x.Period
				where allowedIds.Contains(x.Id) && !x.IsOfficial && x.ExpirationDate == null
				orderby rp.Key
				select x);

//get SQL to be executed
string sql = query.ToSql();
Console.WriteLine(sql);

//error happens here
var calcs = query.ToList();

The EFCore query produces the following T-SQL code:

SELECT 1
FROM [Calculations] AS [c]
INNER JOIN [Calculations] AS [x] ON [c].[Period] AS [Key] = [x].[Period]
GROUP BY [c].[Period]
HAVING ([x].[Id] IN (CAST(1827 AS bigint), CAST(7186 AS bigint)) AND ([x].[IsOfficial] = 0)) AND [x].[ExpirationDate] IS NULL
ORDER BY [Key]

The SQL is invalid.

Further technical details

EF Core version: 2.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: 15.7.3 / 15.8.0 PREVIEW 2

[smitpatel]

Duplicate of #10012

[smitpatel]

Except in this case the lack of aggregate operation blocked us from making it client eval.

Making it client eval is not possible due to #11215

And it could be mitigated fully by #12089

[ajcvickers]

@smitpatel to document workaround. Fix is considered too risky at this time for patch release.

[smitpatel]

Work-around:

                var allowedIds = new int[] { 1827, 7186 }; //static array of int ids

                var query = (from rp in (from c in db.Calculations
                                        select new { Key = c.Period })
                                        .Distinct()
                             join x in db.Calculations on rp.Key equals x.Period
                             where allowedIds.Contains(x.Id) && !x.IsOfficial && x.ExpirationDate == null
                             orderby rp.Key
                             select x);

                //error happens here
                var calcs = query.ToList();

Generates SQL

      SELECT [t].[Key], [x].[Id], [x].[ExpirationDate], [x].[IsOfficial], [x].[Period]
      FROM (
          SELECT DISTINCT [c].[Period] AS [Key]
          FROM [Calculations] AS [c]
      ) AS [t]
      INNER JOIN [Calculations] AS [x] ON [t].[Key] = [x].[Period]
      WHERE ([x].[Id] IN (1827, 7186) AND ([x].[IsOfficial] = 0)) AND [x].[ExpirationDate] IS NULL
      ORDER BY [t].[Key]

The error happens because grouping is not being referenced apart from key selector so our blocking mechanism in #10012 fails and we translate group by with join generating invalid SQL.
Since only grouping key is used you can remove group by and do custom projection of grouping key and call distinct over that to get same results.

[smitpatel]

Duplicate of #12826

[smitpatel]

Closing as #12826 tracks adding regression test for this.