Translate GroupBy after Join with aggregate in projection

[gongjie18675517757]

Note: works fine in a single table
Exception: Working abnormally when using multiple tables to associate queries (EF 6.0 is normal)
example:

            var query = from a in m.BomInfo
                        join b in m.BomInfoDetail on a.Id equals b.BillId
                        group new { a,b} by new { b.BillId,a.BillDate} into g
                        select new
                        {
                           g.Key.BillDate,
                           g.Key.BillId,
                           Count=g.Count()
                        };

SQL:
SELECT [a].[Id], [a].[Batch], ......
FROM [BomInfo] AS [a]
INNER JOIN [BomInfoDetail] AS [b] ON [a].[Id] = [b].[BillId]
ORDER BY [b].[BillId], [a].[BillDate]	  

[ghost]

I can confirm the bug.
Btw, this is a bug not an "enhancement" because no warning is print and group by is not evaluate in memory.

[gongjie18675517757]

Yes Yes.
The 2.1 documentation indicates that this method is already supported, but it does not actually take effect on multiple tables.

source:
https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.1

[ajcvickers]

@gongjie18675517757 Can you please post a runnable project/solution or complete code listing that demonstrates the behavior you are seeing?

[gongjie18675517757]

@ajcvickers
EFCore.GroupTest.zip
Hello there,the file is a test solution,
you need change the db connectionstring
then run

update-database 

[gongjie18675517757]

@ajcvickers
linq :

from a in db.Order
join b in db.OrderDetail on a.Id equals b.OrderId
group new { a, b } by new { a.Id } into g
select new
 {
      Id = g.Key,
      Count = g.Count(),
      Sum = g.Sum(x => x.b.Quantity),
      Max = g.Max(x => x.b.Quantity),
      Min = g.Min(x => x.b.Quantity)
  }

should sql is :

SELECT [a].[Id], COUNT(1) [Count], SUM([b].[Quantity]) [Sum],MAX([b].[Quantity]) [Max],MIN([b].[Quantity]) [Min]
FROM [Order] AS [a]
INNER JOIN [OrderDetail] AS [b] ON [a].[Id] = [b].[OrderId]
group by [a].[Id]

actually sql is :

SELECT [a].[Id], [a].[OrderNumber], [b].[Id], [b].[Name], [b].[OrderId], [b].[Quantity]
FROM [Order] AS [a]
INNER JOIN [OrderDetail] AS [b] ON [a].[Id] = [b].[OrderId]
ORDER BY [a].[Id]

then group by in memory

[ajcvickers]

@gongjie18675517757 From running the code, I see warnings and get the correct results. So it looks like it is being client-evaluated and returns the correct results. Translating this is considered an enhancement rather than a bug fix, unless I am missing some other way that this is a bug.

warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'GroupBy(new <>f__AnonymousType3`1(Id = [a].Id), new <>f__AnonymousType2`2(a = [a], b = [b]))' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'Count()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'Count()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'Sum()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'Sum()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'Max()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'Max()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'Min()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
      => Microsoft.EntityFrameworkCore.Query.RelationalQueryModelVisitor
      The LINQ expression 'Min()' could not be translated and will be evaluated locally.

[gongjie18675517757]

@ajcvickers
Thank you for your answer
I know that the returned result is correct and gives a warning, but this Group by should be aggregated in the database, not in the local client.

[ghost]

@gongjie18675517757 From running the code, I see warnings and get the correct results. So it looks like it is being client-evaluated and returns the correct results. Translating this is considered an enhancement rather than a bug fix, unless I am missing some other way that this is a bug.

Hi,
in my case the group by is not execute at all. but I use the fluent syntax with a where clause and create a DTO object in the select.

so, the query can be translate in this way:

class DtoOrder {
        public string Id { get; set; }
        public string OrderNumber { get; set; }
        public int NumberItem {get; set;}
}
//code

var query = db.Order.Join(db.OrderDetail,
o => o.Id,
d => d.OrderDetail,
(o, d) => new {o, d}).Where(a => a.o.OrderNumber == "1")
.GroupBy(a => a.o.Id)
.Select(g => new DtoOrder() {
Id = g.Key,
OrderNumber = g.Max(v => v.o.OrderNumber),
NumberItem = g.Count()
});

query.ToList();

//code

Sorry, I do not have time to create a whole project. :-(

P.S. as @gongjie18675517757 said, in the documentation the developer team wrote:
"We now support translating it to the SQL GROUP BY clause in most common cases."
https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.1

A group by after a simple join is a common case IMHO. :-)

[ajcvickers]

@gongjie18675517757 Agreed, but that is not implemented yet, which is why this is an open issue.

@lfongaroScp If you are seeing something different, then please file a new issue with a complete description and a runnable project/solution or complete code listing to reproduce the issue. Without that there isn't much we can do.

[AndriySvyryd]

Possibly related to #13887

[smitpatel]

This is likely fixed in 3.0

[smitpatel]

        [ConditionalTheory]
        [MemberData(nameof(IsAsyncData))]
        public virtual Task Join_GroupBy_Aggregate(bool isAsync)
        {
            return AssertQuery<Order, Customer>(
                isAsync,
                (os, cs) =>
                    (from o in os
                     join c in cs
                         on o.CustomerID equals c.CustomerID
                     group o by c.CustomerID)
                    .Select(
                        g => new
                        {
                            g.Key,
                            Count = g.Average(o => o.OrderID)
                        }),
                e => e.Key);
        }

Generates

SELECT [c].[CustomerID] AS [Key], AVG(CAST([o].[OrderID] AS float)) AS [Count]
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerID] = [c].[CustomerID]
GROUP BY [c].[CustomerID]