Efcore: Cannot combine Take with GroupBy/OrderBy

[denious]

We are trying to use the following in the same query: Take + GroupBy + OrderBy.
None of the combinations we tried results in the correct combination.

Steps to reproduce

Code:
``` C#
await _orderContext.OrderDetail
.Where(o => o.OrderHeader.WebUserID == user.WebUserID)
.Select(o => new OrderDetail { PartNumber = o.PartNumber, CreatedOn = o.CreatedOn })
.GroupBy(o => new { o.PartNumber, o.CreatedOn })
.OrderBy(o => o.Key.CreatedOn)
.Take(5)
.ToListAsync();

Expected:
```SQL
SELECT TOP 5 [o].[PartNumber], [o].[CreatedOn]
FROM [Order].[OrderDetail] AS [o]
INNER JOIN [Order].[OrderHeader] AS [o.OrderHeader] ON [o].[OrderHeaderID] = [o.OrderHeader].[OrderHeaderID]
WHERE [o.OrderHeader].[WebUserID] = @__user_WebUserID_0
GROUP BY [o].[PartNumber], [o].[CreatedOn]
ORDER BY [o].[CreatedOn]

Tracing result:

SELECT [o].[PartNumber], [o].[CreatedOn]
FROM [Order].[OrderDetail] AS [o]
INNER JOIN [Order].[OrderHeader] AS [o.OrderHeader] ON [o].[OrderHeaderID] = [o.OrderHeader].[OrderHeaderID]
WHERE [o.OrderHeader].[WebUserID] = @__user_WebUserID_0
ORDER BY [o].[PartNumber], [o].[CreatedOn]

Further technical details

EF Core version: 2.2.6
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 2.2
Operating system: Win 10
IDE: Visual Studio 2017/2019

[smitpatel]

You are not applying any aggregate operator after GroupBy on client side. Client side requires to generate IGrouping<a', OrderDetail>. It just happens to be in your case that your grouping key parts are sufficient to generate OrderDetail. (If there are additional columns needed then they cannot be projected out once you apply SQL GROUP BY.).

So to summarize,
We don't translate GroupBy without aggregate to SQL GROUP BY clause. (Instead client side grouping is performed in version before 3.0). Hence everything after LINQ GroupBy is evaluated on client as reflected in SQL. For this special case, we do not plan to make a fix in 2.2.

[denious]

@smitpatel Thanks for your answer!
I'm not sure I understand how to change the code to make EFCore return my result set.

If what I am looking for is possible in 3.0, we have no problem upgrading.

[smitpatel]

Easy way to make it work in 3.0 would be to call do explicit client side evaluation in 3.0 before GroupBy method.

If you are looking to rewrite the query to evaluate on server side then it would depend on data. Especially, is PartNumber & CreatedOn going to be repeated (at which point a group can have more than 1 element in each). If yes then what are you exactly getting out of the query since you are not actually utilizing the data. If they are not repeated then just remove GroupBy, if they are repeated then perhaps do g.Count().

[denious]

@smitpatel Doing the take/count on the client-side won't allow me to execute a fast query against a large table when combined with distinct values.

Consider the following code and its requirement.
Is it impossible to achieve this with EFCore in one server-side query?

Query 3 most recent distinct numbers.
Expected: 1, 4, 2
```C#
var data = new[]
{
new {Number = 1, Date = new DateTime(2019, 3, 21), Condition = string.Empty},
new {Number = 1, Date = new DateTime(2019, 10, 21), Condition = string.Empty},
new {Number = 2, Date = new DateTime(2019, 9, 21), Condition = string.Empty},
new {Number = 3, Date = new DateTime(2019, 5, 21), Condition = string.Empty},
new {Number = 4, Date = new DateTime(2019, 6, 21), Condition = string.Empty},
new {Number = 4, Date = new DateTime(2019, 10, 21), Condition = string.Empty},
new {Number = 4, Date = new DateTime(2019, 10, 21), Condition = string.Empty},
new {Number = 5, Date = new DateTime(2019, 3, 21), Condition = string.Empty},
new {Number = 6, Date = new DateTime(2019, 9, 21), Condition = string.Empty}
};

var mostRecent3 = data.AsQueryable()
.Where(o => string.IsNullOrWhiteSpace(o.Condition))
.Select(o => new { o.Number, o.Date })
.GroupBy(o => new { o.Number, o.Date })
.OrderByDescending(o => o.Key.Date)
.Take(3)
.Select(o => o.Key)
.ToList();

// mostRecent3 =
// { Number = 1, Date = {2019-10-21} }
// { Number = 4, Date = {2019-10-21} }
// { Number = 2, Date = {2019-09-21} }
```

[roji]

@denious your operation doesn't appear to actually require any grouping. In other words, I think what you want can be simplified to the following:

c# var mostRecent3 = data .Where(o => string.IsNullOrWhiteSpace(o.Condition)) .OrderByDescending(o => o.Date) .Take(3) .Select(o => new { o.Number, o.Date });

[smitpatel]

Just move Select to happen before take and it should work as SQL GROUP BY.

C# var mostRecent3 = data.AsQueryable() .Where(o => string.IsNullOrWhiteSpace(o.Condition)) .Select(o => new { o.Number, o.Date }) .GroupBy(o => new { o.Number, o.Date }) .OrderByDescending(o => o.Key.Date) .Select(o => o.Key) .Take(3) .ToList();

[denious]

@roji This would return duplicate numbers 4s.

@smitpatel How is your answer different from the original code that I posted?

[smitpatel]

This would be detailed article about GroupBy support https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators#groupby

In brief, after you apply GroupBy in Linq, you need to apply Select which is using grouping key or aggregate operator over grouping so that after Select you no longer have IGrouping in result type. We allow OrderBy & Where after GroupBy and before Select as supported by SQL. So I moved Select(o => o.Key) which selects the key only before take operation. The query in first post does not have Select(o => o.Key) hence result would be still IGrouping type.

[denious]

@smitpatel Gotcha now, makes perfect sense! Thank you so much for your support and for the link to the docs, I will read it thoroughly!