Entity Framework Core 2.1 group by not working properly

.net asp.net-core c# entity-framework entity-framework-core

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?

1
2
5/7/2019 10:49:09 AM

Accepted Answer

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.

4
5/8/2019 9:22:51 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow