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.

Elvinaelvira

Answer 1

For anyone looking at this in the future.

I was able to make this work by declaring and populating a TimeStamp property and using the OrderByDescending() at the end. I am not sure if this is the best solution, but it did solve my problem.

var results = context.Detections
    .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(),
        TimeStamp = grp.OrderByDescending(det => det.TimeStamp).First().TimeStamp,
        Detection = grp.OrderByDescending(det => det.TimeStamp).First(),
    })
    .OrderByDescending(det => det.TimeStamp)
    .ToList();

Elvinaelvira

Answer 2

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,
})

Hernando

I thought about this. By default I am grouping by the hour. With this method minutes, seconds, and milliseconds will still be out of order. I can sort those in memory, but what if pagination leaves the hour split between 2 pages? – Elvinaelvira

I see you selecting a Count() and a First() from each group. How could that be split? And the grp.OrderByDescending before that First() should respect minutes and seconds. – Hernando

The goal was to display the latest Detection in the group and the number of Detections in that group. That part works fine and does respect minutes and seconds. My problem is the groups display in the wrong order, I want the latest to display first. The OrderByDecsending before grouping works with straight LING and the in memory DB. It seems to be more of a SQL Server limitation. – Elvinaelvira

You're still not clear. But .OrderByDescending(grp => grp.Key) can't really work, you'll have to go for the .OrderBy().ThenBy() option. – Hernando

I have added some edits for clarification. Sorry for confusion and thanks for the help. I am able to get results in SQL, just do not know how to get that to work in LINQ. – Elvinaelvira