Query: Server Eval Count of GroupBy

[myjimmyzhou]

code:

        public int Count()
        {
            return webUserDao.DbSet().Where(u => u.CreateTime < DateTime.Now).GroupBy(g => g.UserType).Count();
        }

Issues:
warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'GroupBy([u].UserType, [u])' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'Count()' could not be translated and will be evaluated locally.
warn: Microsoft.EntityFrameworkCore.Query[20500]
The LINQ expression 'Count()' could not be translated and will be evaluated locally.

[MaklaCof]

Same problem here:

var group = this.Set<DbMasterUser>().GroupBy(t => t.LanguageId).ToArray();

The LINQ expression 'GroupBy([t].LanguageId, [t])' could not be translated and will be evaluated locally.

with

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.2.3" />

[smitpatel]

@MaklaCof - Your groupby query lacks aggregate operator. It can never be translated to SQL GROUP BY clause.

[smitpatel]

@myjimmyzhou - Essentially you are counting number of groups. You can just do Distinct on grouping key column and Count on rather than using GroupBy.

[smitpatel]

Generated invalid SQL

SELECT COUNT(*)
FROM (
    SELECT [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
    FROM [Orders] AS [o]
    GROUP BY [o].[CustomerID]
) AS [t]

[smitpatel]

Work-around

public int Count()
        {
            return webUserDao.DbSet().Where(u => u.CreateTime < DateTime.Now).GroupBy(g => g.UserType).Select(g => g.Key).Count();
        }

[smitpatel]

Operators covered here

• Count
• LongCount
• Any
• All

[smitpatel]

Pulling this in 5.0