GroupBy with EF Core 6.0 and SQL Server

Question

I have a Blazor Web Application that has been working and in the field for a few months. I want to extend the DB querying to the group of similar "Detections".

It was written starting with .NET 5, and just today was updated to .NET 6 trying and get this working.

I would like to know how to get the results ordered by TimeStamp (a DateTime property). I have a working example with an in-memory DB, but production will be in SQL Server. I am not that great in SQL, but I have played around with it for a while in Management Studio with no luck.

Commenting out the OrderByDescending() groups things properly, but the results are not in the correct order. It seems the EF translation process is completely removing that line, it makes no difference in the generated query or the result set.

var results = context.Detections
//Line below makes no change ignored by SQL Server. Works when using in memory DB.
    //.OrderByDescending(det => det.TimeStamp) 
    .GroupBy(det => new
    {
        Year = det.TimeStamp.Year,
        Month = det.TimeStamp.Month,
        Day = det.TimeStamp.Day,
        Hour = det.TimeStamp.Hour,
    })
    .Select(grp => new
    {
        Count = grp.Count(),
        Detection = grp.OrderByDescending(det => det.TimeStamp).First(),
    })
//The following line will not translate
    //.OrderByDescending(det => det.Detection.TimeStamp)
    .ToList();

If any of this matters:

• Visual Studio 2022 (4.8.04084)
• .Net 6.0
• SQL Server 2019 (15.0.2080.9)
• *All NuGet packages related to EF have been updated to 6.0

Edit for clarification
The above code segment produces the following SQL query.

SELECT [t].[c], [t0].[Id], [t0].[TimeStamp]
FROM (
    SELECT COUNT(*) AS [c], DATEPART(year, [d].[TimeStamp]) AS [c0], DATEPART(month, [d].[TimeStamp]) AS [c1], DATEPART(day, [d].[TimeStamp]) AS [c2], DATEPART(hour, [d].[TimeStamp]) AS [c3]
    FROM [Detections] AS [d]
    WHERE [d].[TimeStamp] > DATEADD(day, CAST(-16.0E0 AS int), GETUTCDATE())
    GROUP BY DATEPART(year, [d].[TimeStamp]), DATEPART(month, [d].[TimeStamp]), DATEPART(day, [d].[TimeStamp]), DATEPART(hour, [d].[TimeStamp])
) AS [t]
OUTER APPLY (
    SELECT TOP(1) [d0].[Id], [d0].[TimeStamp]
    FROM [Detections] AS [d0]
    WHERE ([d0].[TimeStamp] > DATEADD(day, CAST(-30.0E0 AS int), GETUTCDATE())) AND (((([t].[c0] = DATEPART(year, [d0].[TimeStamp])) AND ([t].[c1] = DATEPART(month, [d0].[TimeStamp]))) AND ([t].[c2] = DATEPART(day, [d0].[TimeStamp]))) AND ([t].[c3] = DATEPART(hour, [d0].[TimeStamp])))
    ORDER BY [d0].[TimeStamp] DESC
) AS [t0]

It produces results similar to the following. Notice not sorted by time.

1   628591  2021-11-02 14:34:06.0442966  
10  628601  2021-11-12 05:43:27.7015291  
150 628821  2021-11-12 21:59:27.6444236  
20  628621  2021-11-12 06:17:13.7798282  
50  628671  2021-11-12 15:17:23.8893856  

If I add ORDER BY [t0].TimeStamp DESC at the end of that SQL query in Management Studio I get the results I am looking for (see below). I just need to know how to write that in LINQ.

150 628821  2021-11-12 21:59:27.6444236  
50  628671  2021-11-12 15:17:23.8893856  
20  628621  2021-11-12 06:17:13.7798282  
10  628601  2021-11-12 05:43:27.7015291  
1   628591  2021-11-02 14:34:06.0442966  

Adding .OrderByDescending(det => det.Detection.TimeStamp) at the end before ToList() was my first thought, but that "could not be translated". I will need to do some pagination with these results so I would really like to do the sorting in SQL.

Answer 1

GroupBy has to do its own Ordering so that 'ignores' is not totally unexpected.

Move it to below the grouping:

var results = context.Detections
    //.OrderByDescending(det => det.TimeStamp) 
    .GroupBy(det => new
    {
        Year = det.TimeStamp.Year,
        Month = det.TimeStamp.Month,
        Day = det.TimeStamp.Day,
        Hour = det.TimeStamp.Hour,
    })
//    .OrderByDescending(grp => grp.Key)  // may have to split into y/m/d/h again
    .OrderByDescending(grp => grp.Key.Year)
       .ThenByDescending( grp => grp.Key.Month)
       .ThenByDescending( grp => grp.Key.Day)
       .ThenByDescending( grp => grp.Key.Hour)
    .Select(grp => new
    {
        Count = grp.Count(),
        Detection = grp.OrderByDescending(det => det.TimeStamp).First(),
    })
    .ToList();

When EF supports it, the Ordering and Grouping might become a little easier with

.GroupBy(det => new
{
    Date = det.TimeStamp.Date,
    Hour = det.TimeStamp.Hour,
})