orderby not working before groupby in entity framework core

Question

I want to order users, then grouped by GroupCode and get first item of group I have to use take because the number of users is large I use this code, it's work fine but OrderBy not working.

public class User
{
    public int Id { get; set; }
    public int GroupCode { get; set; }
    public DateTime CreatedDateTime { get; set; }
}

var query = _context.Users
    .OrderByDescending(s => s.CreatedDateTime)
    .GroupBy(s => s.GroupCode)
    .Select(g => g.First())
    .Take(10)
    .ToListAsync()

Does this answer your question? [orderby not working before groupby in asp net](https://stackoverflow.com/questions/75323391/orderby-not-working-before-groupby-in-asp-net) — Mark Benningfield, Feb 05 '23 at 13:16
@MarkBenningfield no one answered it, but it is the same question — Ali, Feb 05 '23 at 13:22
Taking top 1 item of a group is subset of a more general taking top N items of a group, explained in the duplicate. Just for single top 1 item you would use `First` instead of `Take(N)`. — Ivan Stoev, Feb 05 '23 at 13:57

Olivier Jacot-Descombes · Accepted Answer · 2023-02-05T14:45:08.920

EF Core translates the query to SQL and in SQL ORDER BY cannot precede GROUP BY.

Example:

SELECT *
FROM t
ORDER BY a
GROUP BY b

Oracle: SQL Error: ORA-00933: SQL command not properly ended (Line 4, Column 1)

SQL Server: Incorrect syntax near the keyword 'group'.

You can do this in LINQ-to-Objects, so. The documentation says:

The IGrouping<TKey,TElement> objects are yielded in an order based on the order of the elements in source that produced the first key of each IGrouping<TKey,TElement>. Elements in a grouping are yielded in the order that the elements that produced them appear in source.

As @Ivan Stove points out: move OrderBy before First():

List<User> query = await _context.Users
    .GroupBy(s => s.GroupCode)
    .OrderBy(g => g.Key)
    .Take(10)
    .Select(g => g.OrderByDescending(s => s.CreatedDateTime).First())
    .ToList();

I also think that you need to order twice. Once to get the groups in the right order so that Take(10) returns the first 10 GroupCodes and another one to get the User in each group with the last CreatedDateTime. Unless you need something else. But then please explain exactly what you need.

Check this please https://github.com/dotnet/efcore/issues/18494 — Ali, Feb 05 '23 at 13:26
@Ali This link refers to an old issue, starting with EF Core 6.0 you can use `OrderBy` on `GroupBy` result. In your code, just move `OrderBy{Descending}` before `First()` — Ivan Stoev, Feb 05 '23 at 13:47
@IvanStoev OrderBy in GroupBy just ordered items in selected Group , not all item in table. I think there is no way to solve this. — Ali, Feb 05 '23 at 17:26

score 0 · Answer 2 · answered Feb 05 '23 at 13:49

0

Your query has no analogue in the SQL world, ORDER BY followed by grouping is prohibited. While it is working with LINQ to Objects, with EF Core you have to follow it's rules.

Rewrite this query in the following way:

var query = await _context.Users
    .GroupBy(s => s.GroupCode)
    .Select(g => g.OrderByDescending(s => s.CreatedDateTime).First())
    .Take(10)
    .ToListAsync();

answered Feb 05 '23 at 13:49

Svyatoslav Danyliv

21,911
3
16
32

Don't you get tired to post one and the same answer (just adjusted for the concrete model). Why don't you just select one of your answers and use it as duplicate target. – Ivan Stoev Feb 05 '23 at 14:00
Agree with you, but usually I fail with search my in my answers and just typing several lines of code saves my time. – Svyatoslav Danyliv Feb 05 '23 at 14:10
@IvanStoev OrderBy in GroupBy just ordered items in selected Group , not all item in table. I think there is no way to solve this. – Ali Feb 05 '23 at 17:26
@Ali, just another OrderBy after Select. This query retrieves first record in group. – Svyatoslav Danyliv Feb 05 '23 at 17:35
@SvyatoslavDanyliv not working, Error: KeyNotFoundException: The given key 'EmptyProjectionMember' was not present in the dictionary. – Ali Feb 06 '23 at 07:28

orderby not working before groupby in entity framework core

2 Answers2