EF Core 2,1 GroupBy with Where and take first item

Question

I seem to have the simplest problem but can't seem to get it right. I have a SQL table which looks something like this:

Code    | Period  | PeriodVersion | SourceId
Foo       201810    1               Source1
Foo       201810    2               Source1
Foo       201811    1               Source1
Bar       201810    1               Source1
Foo       201809    2               Source1
Foo       201809    1               Source1
Foo       201808    1               Source1

The query has the following requirements:

• Period should be grouped by 201809, 201810 and 201811 and only the highest PeriodVersion should be returned. (in some cases there are 6 periods as well)
• Code should be equal to Foo
• SourceId should be equal to Source1

If all works well I would like to have the following result:

Code    | Period  | PeriodVersion | SourceId
Foo       201810    2               Source1
Foo       201811    1               Source1
Foo       201809    2               Source1

I've tried the following:

        var query = from item in context.MyTable
                    orderby item.PeriodVersion descending
                    where item .Code== item.ISINCode &&
                          item .SourceID == "Source1" &&
                          (
                              "201810" == item.Period ||
                              "201811" == item.Period ||
                              "201819 == item.Period
                          )
                    group item by item.Period into g
                    select g.FirstOrDefault();

It translates to:

SELECT * // Selected columns here....
FROM [MyTable] AS [table]
WHERE ((([table].[Code] = 'Foo') AND ([table].[SourceID] = 'Source1'))) AND [table].[Period] IN ('201209', '201208', '201207')
ORDER BY [table].[Period], [table].[PeriodVersion] DESC

This will return the "correct" results, but it executes the groupby in memory which fetches all PeriodVersion from the database. In some cases I have >50 PeriodVersion for each Period which makes the query above very inefficient. Is there any way to make this more efficient?

I have also tried this based on this answer:

    var query = context.MyTable
        .GroupBy(x => x.Period)
        .Select(g => g
            .OrderByDescending(p =>
                p.PeriodVersion
            ).FirstOrDefault(x => x.Code == "Foo" &&
                                  x.SourceID == "Source1" &&
                                  (
                                   "201810" == item .Period ||
                                   "201811" == item .Period ||
                                   "201819 == item .Period
                                   )
        );

It gave an even worse result since it did execute the where in memory.

Select *
FROM MyTable AS [x]
ORDER BY [x].[Period]

My actual table has a lot more columns than the ones listed here. I'm using EF Core 2.1. I can upgrade to a newer version but it would require some major overhaul. According to the documentation groupby is supported. But when reading about it here on SO it seems to be tricky.

Answer 1

Maybe you can try something like this:

IList<string> list = new List<string>(){
 "201810",
 "201811",
 "201819"
};

context.MyTable
.Where(m=>list.Contains(m.Period) 
   && m.Code == "Foo"
   && m.Source = "Source1" )
.GroupBy(m=>m.Period)
.Select(g=>g.OrderByDesceding(s=>s.PeriodVersion).FirstOrDefault())
.ToList()

Group by in SQL can not return records you wanted, only aggregate (columns that you want to grop by) if you try to group by something and than select columns that are not part of group by clause, everything will be loaded in memory and evaluated there. That's how it worked unitl .Net Core 3.0 I think. After .NetCore 3.0 you need to explicitly call .ToList() before you call LINQ that can't be evaluated to SQL. Anyways this won't be fully evaluated to SQL so it will not be great with performance if you are still not satisfied i recomend trying raw sql query approach which will definitley perform much better.

Edit: I think i originaly misunderstood this question so I'm updateing my code.