Group by with composite objects, with join and count of fields cannot be translated to SQL

[arthur-liberman]

I am porting SQL queries in our product to Linq queries to be used with EF. We have an SQL query similar to this:

SELECT I.id, I.name, COUNT(C.name) AS CatCount, COUNT(DISTINCT G.name) AS GroupCount
FROM [Item] I
LEFT OUTER JOIN [Group] G ON I.groupId = G.id
LEFT OUTER JOIN [Category] C ON I.categoryId = C.id
GROUP BY I.id, I.name

Executing this on the DB directly works as expected.

The following query in Linq

public IList<Dto> ExecuteQuery()
{
    using (var dbContext = new MyDbContext())
    {
        return
           (from i in dbContext.Items
            join tmp_g in dbContext.Groups on i.GroupId equals tmp_g.Id into gj_g
            from g in gj_g.DefaultIfEmpty()
            join tmp_c in dbContext.Categories on i.CategoryId equals tmp_c.Id into gj_c
            from c in gj_c.DefaultIfEmpty()
            group new { GroupName = g.Name, CatName = c.Name } by new { i.Id, i.Name }
            into g2
            select new Dto
            {
                Id = g2.Key.Id,
                Name = g2.Key.Name,
                CategoryCount = g2.Select(a => a.CatName).Count(),
                GroupCount = g2.Select(a => a.GroupName).Distinct().Count()
            }).ToList();
    }
}

generates the following exception:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 5.0.0-alpha.1.20104.1 initialized 'MyDbContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: SensitiveDataLoggingEnabled
Unhandled exception. System.InvalidOperationException: The LINQ expression '(GroupByShaperExpression:
KeySelector: new {
    Id = (i.id),
    Name = (i.name)
 },
ElementSelector:new {
    GroupName = (ProjectionBindingExpression: GroupName),
    CatName = (ProjectionBindingExpression: CatName)
 }
)
    .Select(a => a.CatName)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)
   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)
   at System.Linq.Expressions.MemberInitExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at joingroupcountquery.Logic.ExecuteQuery() in C:\Users\libermaa\Documents\efcore\joingroupcountquery\Entities.cs:line 19
   at joingroupcountquery.Program.Main(String[] args) in C:\Users\libermaa\Documents\efcore\joingroupcountquery\Program.cs:line 11

I checked on 3.1 and latest 5.0/master with identical results. If I remove .Select(a => a.GroupName) and .Select(a => a.CatName), the generated SQL is as follows (missing select for GroupCount):

      SELECT [i].[id] AS [Id], [i].[name] AS [Name], COUNT(*) AS [CategoryCount]
      FROM [Item] AS [i]
      LEFT JOIN [Group] AS [g] ON [i].[groupId] = [g].[id]
      LEFT JOIN [Category] AS [c] ON [i].[categoryId] = [c].[id]
      GROUP BY [i].[id], [i].[name]

In addition to the above, using Distinct() also results in a similar error that the query cannot be translated.

Steps to reproduce

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System.Linq;
using System;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using System.Linq.Dynamic.Core;

namespace joingroupcountquery
{
    class Program
    {
        static void Main(string[] args)
        {
            var seeder = new Seeder();
            seeder.Seed();
            new Logic().ExecuteQuery();
            Console.WriteLine("Hello World!");
        }
    }

    public class Logic
    {
        public IList<Dto> ExecuteQuery()
        {
            using (var dbContext = new MyDbContext())
            {
                return
                (from i in dbContext.Items
                 join tmp_g in dbContext.Groups on i.GroupId equals tmp_g.Id into gj_g
                 from g in gj_g.DefaultIfEmpty()
                 join tmp_c in dbContext.Categories on i.CategoryId equals tmp_c.Id into gj_c
                 from c in gj_c.DefaultIfEmpty()
                 group new { GroupName = g.Name, CatName = c.Name } by new { i.Id, i.Name }
                 into g2
                 select new Dto
                 {
                     Id = g2.Key.Id,
                     Name = g2.Key.Name,
                     CategoryCount = g2.Select(a => a.CatName).Count(),
                     GroupCount = g2.Select(a => a.GroupName).Count()
                 }).ToList();
            }
        }
    }

    public class Dto
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int GroupCount { get; set; }
        public int CategoryCount { get; set; }
    }

    public class Seeder
    {
        public void Seed()
        {
            using (var dbContext = new MyDbContext())
            {
                if (!dbContext.Items.Any())
                {
                    for (int i = 0; i < 10; i++)
                    {
                        var rand = new Random();
                        int group = rand.Next() % 4;
                        int cat = rand.Next() % 3;
                        var item = new Item { GroupId = group, CategoryId = cat, Name = $"Item {i}" };
                        dbContext.Items.Add(item);
                    }
                }
                if (!dbContext.Groups.Any())
                {
                    for (int i = 0; i < 4; i++)
                    {
                        var group = new Group { Name = $"Group {i}" };
                        dbContext.Groups.Add(group);
                    }
                }
                if (!dbContext.Categories.Any())
                {
                    for (int i = 0; i < 3; i++)
                    {
                        var category = new Category { Name = $"Category {i}" };
                        dbContext.Categories.Add(category);
                    }
                }
                dbContext.SaveChanges();
            }
        }
    }

    public class Item
    {
        public int Id { get; set; }
        public int GroupId { get; set; }
        public int CategoryId { get; set; }
        public string Name { get; set; }
    }

    public class Group
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public class MyDbContext : DbContext
    {
        public static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder =>
        {
            builder.AddConsole();
        });

        public DbSet<Item> Items { get; set; }
        public DbSet<Group> Groups { get; set; }
        public DbSet<Category> Categories { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(new ItemConfiguration());
            modelBuilder.ApplyConfiguration(new GroupConfiguration());
            modelBuilder.ApplyConfiguration(new CategoryConfiguration());
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseLoggerFactory(loggerFactory)
                .EnableSensitiveDataLogging()
                .UseSqlServer(@"Server=.;Database=JoinGroupCount;User Id=sa;Password=password");
        }
    }

    public class ItemConfiguration : IEntityTypeConfiguration<Item>
    {
        public void Configure(EntityTypeBuilder<Item> builder)
        {
            builder.ToTable("Item");
            builder.HasKey(o => o.Id);
            builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
            builder.Property(t => t.GroupId).HasColumnName("groupId");
            builder.Property(t => t.CategoryId).HasColumnName("categoryId");
            builder.Property(t => t.Name).HasColumnName("name");
        }
    }

    public class GroupConfiguration : IEntityTypeConfiguration<Group>
    {
        public void Configure(EntityTypeBuilder<Group> builder)
        {
            builder.ToTable("Group");
            builder.HasKey(o => o.Id);
            builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
            builder.Property(t => t.Name).HasColumnName("name");
        }
    }

    public class CategoryConfiguration : IEntityTypeConfiguration<Category>
    {
        public void Configure(EntityTypeBuilder<Category> builder)
        {
            builder.ToTable("Category");
            builder.HasKey(o => o.Id);
            builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
            builder.Property(t => t.Name).HasColumnName("name");
        }
    }
}

Further technical details

EF Core version: 3.1.1 (SDK 3.1.101) Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: NET Core 3.1, 5.0 Operating system: Windows Server 2012 R2 IDE: Visual Studio 2019 16.4.2, Visual Studio Code

[arthur-liberman]

This seems to be the best I can do right now.

public IList<Dto> ExecuteQuery()
{
    using (var dbContext = new MyDbContext())
    {
        var grouping = (from i in dbContext.Items
                        join tmp_g in dbContext.Groups on i.GroupId equals tmp_g.Id into gj_g
                        from g in gj_g.DefaultIfEmpty()
                        join tmp_c in dbContext.Categories on i.CategoryId equals tmp_c.Id into gj_c
                        from c in gj_c.DefaultIfEmpty()
                        select new { i, g, c }).ToList();
        var result = from g in grouping
                        group new { gName = g.g.Name, cName = g.c.Name } by new { g.i.Id, g.i.Name } into g2
                        select new Dto
                        {
                            Id = g2.Key.Id,
                            Name = g2.Key.Name,
                            GroupCount = g2.Select(a => a.gName).Distinct().Count(),
                            CategoryCount = g2.Select(a => a.cName).Count()
                        };

        return result.ToList();
    }
}

SELECT [i].[id], [i].[categoryId], [i].[groupId], [i].[name], [g].[id], [g].[name], [c].[id], [c].[name]
FROM [Item] AS [i]
LEFT JOIN [Group] AS [g] ON [i].[groupId] = [g].[id]
LEFT JOIN [Category] AS [c] ON [i].[categoryId] = [c].[id]

[smitpatel]

Duplicate of #17376

[arthur-liberman]

@smichtch I've searched, but couldn't find any dups. What about the Distinct() issue I mentioned? Is it the same as Select()?

[ajcvickers]

@arthur-liberman #17376 is the duplicate.

[arthur-liberman]

@ajcvickers The error seems to be different, but the situation described in that issue is the same. Only in my case g2.Select(a => a.CatName).Count() fails too. There seem to be 2 issues here. With the key selector and with the Distinct call.

[smitpatel]

g2.Select(a => a.CatName).Count() is same as g2.Count() so we don't support Select when doing Count.

[arthur-liberman]

Then perhaps I'm doing something wrong. The idea is to get the SQL query I posted in the OP.

SELECT I.id, I.name, COUNT(C.name) AS CatCount, COUNT(DISTINCT G.name) AS GroupCount
FROM [Item] I
LEFT OUTER JOIN [Group] G ON I.groupId = G.id
LEFT OUTER JOIN [Category] C ON I.categoryId = C.id
GROUP BY I.id, I.name

If you seed the DB using the code sample in OP, and run the query above on the DB directly, you will see that each count column will contain different results. Maybe the way I use count is wrong? Am I supposed to use g2.Count(a => a.CatName != null)?

[smitpatel]

Yes, but that would also be duplicate of #11711

[arthur-liberman]

To be completely frank, I don't think I follow. I understand that I can't use Distinct() right now, and it's a known issue. But I still don't fully understand what would be the correct LINQ in order to achieve a matching translation to the original SQL query if both #11711 and #17376 were fixed.