EF Core 2.1.2 -'must be reducible node' error when referencing group key in subquery

[branislavsimo]

This LINQ query fails during runtime with error in subject:

var p = (from t in db.Tenants
 group t by t.Id into g
 select new {
   id = g.Key,
   cnt = (from su in db.Subscription where (su.TenantId == g.Key) select su).Count()
 }).ToList();

I expected this SQL query to be generated:

SELECT [t].[Id], (SELECT COUNT(*) FROM [Subscription] AS [su] WHERE [su].[TenantId] = [t].[Id]) AS [cnt]
  FROM [Tenants] AS [t]
  GROUP BY [t].[Id];

When I replace the "g.Key" with local variable "guid" (i.e. su.TenantId == guid), an SQL query is generated correctly.

NOTE: This query is a simplification of my original query with joins just to show the error.

Test project: LinqTest.zip
Project debug output with error OUT_group_key_error.txt
Project debug output when using local wariable instead of key reference OUT_local_variable_ok.txt

Further technical details

EF Core version: 2.1.2
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Win10
IDE: Visual Studio 2017 15.8.2

[m17kea]

@maumar there is still one issue with the GetPortfolioHierarchyByUserIdReturnsPermissionedPortfoliosOnly test in the repo I shared with you. It looks like it is the same as this one. In file \src\Derivitec.Infrastructure.Data.Db\Views\ViewPortfolioHierarchySearches.cs line 264 I am using a grouped sum in a where clause which seems to correlate with this issue.

@smitpatel as you are assigned here I will invite you to the repo.

[m17kea]

@smitpatel is there any work around here or is it just a case of waiting for version 3. I'm migrating from EF6 where my query worked perfectly and I will have to completely rewrite a function in my application to get around this so some guidance would be greatly appreciated.

[smitpatel]

Work-around:

var query = from e in db.Blogs
            join i in (from a in (from b in db.Blogs
                                    join p in db.Posts on b.Id equals p.BlogId
                                    select b)
                        group a by a.Id into g
                        select new
                        {
                            Id = g.Key,
                            Count = g.Count()
                        }) on e.Id equals i.Id into grouping
            from i in grouping.DefaultIfEmpty()
            select new
            {
                Id = e.Id,
                Count = i.Count
            };

    query.ToList();

Since EF does not support complex operations on groups, the work-around involves doing grouping after the join. So you join blog with post and then do group so each blog is repeated # of times posts are associated with blog. This give you the counts as expected in first query except for the blogs which don't have any posts for them (since join would filter them out). So after grouping you need to do another left join to re-introduce the blogs without any posts.

Generated SQL

      SELECT [t].[Id], [t].[Count], [e].[Id] AS [Id0]
      FROM [Blogs] AS [e]
      LEFT JOIN (
          SELECT [b].[Id], COUNT(*) AS [Count]
          FROM [Blogs] AS [b]
          INNER JOIN [Posts] AS [p] ON [b].[Id] = [p].[BlogId]
          GROUP BY [b].[Id]
      ) AS [t] ON [e].[Id] = [t].[Id]

It gives correct results through EF materializer.

[m17kea]

@smitpatel thank you so much for the work around, very useful!

[smitpatel]

Generated SQL in 3.1

      SELECT [b].[Id] AS [id], (
          SELECT COUNT(*)
          FROM [Subscription] AS [s]
          WHERE [s].[TenantId] = [b].[Id]) AS [cnt]
      FROM [Blog] AS [b]
      GROUP BY [b].[Id]