Entity Framework Core 2.1 group by not working properly

Question

I'm using Entity framework Core 2.1.4 and I wrote an basic example query from C# like below.

var myList = context.HastaAdres.OrderBy(p => p.ID).Take(20).GroupBy(p => p.IlKodu).Select(d => d.FirstOrDefault()).Select(p => p.ID).ToList();

But in SQL profiler, running code like below. There is no group by in SQL and very different from classic entity framework. So, also result is different. I need only one column as a result. But first query, return all columns. Also row count is different from second query.

SQL Generated By Entity framework Core

SELECT [t].[ID], [t].[IlKodu], [t].[AcikAdres], [t].[BucakAdi], [t].[BucakKodu], [t].[BulvarKodu], [t].[CaddeKodu], [t].[CreatedBy], [t].[CreatedDate]
FROM (
    SELECT TOP(20) [p].[ID], [p].[IlKodu], [p].[AcikAdres], [p].[BucakAdi], [p].[BucakKodu], [p].[BulvarKodu], [p].[CaddeKodu], [p].[CreatedBy], [p].[CreatedDate]
    FROM [Ortak].[HastaAdres] AS [p]
    ORDER BY [p].[ID]
) AS [t]
ORDER BY [t].[IlKodu]

When I tried this method in Entity Framework, it's generating perfect code.

SQL Generated By Entity framework

SELECT 
    (SELECT TOP (1) 
        [Limit2].[ID] AS [ID]
        FROM ( SELECT TOP (20) [Extent2].[ID] AS [ID], [Extent2].[IlKodu] AS [IlKodu]
            FROM [Ortak].[HastaAdres] AS [Extent2]
            ORDER BY [Extent2].[ID] ASC
        )  AS [Limit2]
        WHERE ([Distinct1].[IlKodu] = [Limit2].[IlKodu]) OR (([Distinct1].[IlKodu] IS NULL) AND ([Limit2].[IlKodu] IS NULL))) AS [C1]
    FROM ( SELECT DISTINCT [distinct].[IlKodu] AS [IlKodu]
        FROM ( SELECT TOP (20) 
            [Extent1].[IlKodu] AS [IlKodu]
            FROM [Ortak].[HastaAdres] AS [Extent1]
            ORDER BY [Extent1].[ID] ASC
        )  AS [distinct]
    )  AS [Distinct1]

What can be the reason of this situation?

Are you getting the correct data? EF6 and EFCore are very different products and it's not surprising they generate different SQL. — DavidG
@DavidG is asking about the materialized result (in myList), not the SQL query. EF Core uses mixed approach, so the actual result != SQL query — Ivan Stoev
I see. That's a different story and unfortunately EF Core still can't translate many constructs to SQL and evaluates them locally. It's strange though - even with client evaluation taking 20 records should not take 10 min. What if you pre-select the fields that you need, e.g. context.HastaAdres.Select(p => new { p.ID, p.IlKodu }).OrderBy(…)...? — Ivan Stoev
@realist Why? I guess the usual factors - schedule, lack of time / resources. It's not final - improving over the time, probably will be optimized some day. The problem is that this is how it is now. Unfortunately I see no way to let them generate the desired SQL query. Have you tried my suggesting - pre select the 2 fields, then do order by and take - this should limit the SQL query to 20 records with 2 fields. — Ivan Stoev
It's as @IvanStoev said. EF Core still can't translate Linq GroupBy to SQL GroubBy. They just added "some" translations if you use aggregation function like Sum. You can check it out here: docs.microsoft.com/en-us/ef/core/what-is-new/…. — jpgrassi

realist realist · Accepted Answer · 2019-05-08T09:22:51

I learnt that EF Core not supporting database level Group By and take element. But, by EF Core 2.1, came support for group by then sum,min,max,average at databse level.(You can see from this link https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.1#linq-groupby-translation from commented by @jpgrassi)

So, I changed my query code as take min value and then get that row from database. So, this solved my isuue. But, I hope, most recent time, Microsoft supports group by at database level.

Entity Framework Core 2.1 group by not working properly

1 Answers