Efcore: The LINQ expression "GroupBy, Average, OrderBy, Take" could not be translated and will be evaluated locally

[LeXaMeN]

I tried different solutions found here and on other sites, but nothing helps. Ideally, I still need to make a JOIN for Product. I tried this in the database and everything worked, but EF Core does not want to do GROUP BY, AVG and ORDER BY Average DESC LIMIT.

``` C#
public class ProductDigi
{
public int Id { get; set; }

public int ProductId { get; set; }
public Product Product { get; set; }

public bool Allowed { get; set; }

[MaxLength(55)]
public string Name { get; set; }
public bool NameChanged { get; set; }

[Column(TypeName = "tinyint unsigned")]
public byte AgencyFee { get; set; }
public bool AgencyFeeChanged { get; set; }

public DateTime UpdatedAt { get; set; }

}

### Steps to reproduce

LINQ
``` C#
DbContext.ProductDigis.AsNoTracking()
    .Where(pd => pd.Allowed && !pd.NameChanged && !pd.AgencyFeeChanged)
    .GroupBy(pd => pd.ProductId)
    .Select(g => new { ProductId = g.Key, Average = g.Average(pd => pd.AgencyFee) })
    .OrderByDescending(g => g.Average).Take(limit)
    .ToList();

Expected

SELECT `pd`.`ProductId`, AVG(AgencyFee) AS Average
FROM `ProductDigi` AS `pd`
WHERE ((`pd`.`Allowed` = TRUE) AND (`pd`.`NameChanged` = FALSE)) AND (`pd`.`AgencyFeeChanged` = FALSE)
GROUP BY `pd`.`ProductId`
ORDER BY Average DESC LIMIT 10

Result

[WRN] The LINQ expression '"GroupBy([pd].ProductId, [pd])"' could not be translated and will be evaluated locally.
[WRN] The LINQ expression '"Average()"' could not be translated and will be evaluated locally.
[WRN] The LINQ expression '"orderby {from ProductDigi pd in [g] select Convert([pd].AgencyFee, Int32) => Average()} desc"' could not be translated and will be evaluated locally.
[WRN] The LINQ expression '"Take(__p_0)"' could not be translated and will be evaluated locally.

SELECT `pd`.`Id`, `pd`.`AgencyFee`, `pd`.`AgencyFeeChanged`, `pd`.`Allowed`, `pd`.`Name`, `pd`.`NameChanged`, `pd`.`ProductId`, `pd`.`UpdatedAt`
FROM `ProductDigi` AS `pd`
WHERE ((`pd`.`Allowed` = TRUE) AND (`pd`.`NameChanged` = FALSE)) AND (`pd`.`AgencyFeeChanged` = FALSE)
ORDER BY `pd`.`ProductId`

Further technical details

EF Core version: 2.2.6-servicing-10079
Database provider: Pomelo.EntityFrameworkCore.MySql v2.2.6
Target framework: NET Core 2.2

[roji]

This translates as-is on EF Core 3.1 - note that 2.2 is out of support. I'd recommend moving to 3.1, if you encounter any blocking issues while do that please let us know.

[LeXaMeN]

Migrating from 2.2 to 3.1 is not easy and takes time. Migrating due to one function of the EF Core is not practical.
Are there any alternatives?
There is an article about version 2.1, which says about GroupBy support, but for some reason it does not work in 2.2. Maybe I need to create a request in a different way or something else?

[roji]

If your only issue in 2.2 is this one query, then as a temporary stop-gap solution you can also use raw SQL until you have time to migrate to 3.1.

[ajcvickers]

Closing as fixed in 3.1.