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?
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.