How to craft a linq query with a multi-column "Group By"

Question

How might I convert the following into a LINQ expression? I have an active EntityFramework context for the database model, I just get lost looking at the LINQ "group" docs.

SELECT TOP 1
    ContributorId,
    MAX(Version) 'Version',
    CASE
        WHEN CONVERT(varchar, max(CreateTS), 23) >= CONVERT(varchar, max(UpdateTS), 23) 
            THEN CONVERT(varchar, max(CreateTS), 23)
        ELSE CONVERT(varchar, max(UpdateTS), 23) 
    END 'Date'
FROM [MyDB].[dbo].[Contributions]
WHERE ContributorId = '08db4b393b1f'
GROUP BY 
    ContributorId,
    Version,
    CONVERT(varchar, CreateTS, 23),
    CONVERT(varchar, UpdateTS, 23)
ORDER BY 
    Version DESC

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, May 02 '23 at 17:55
It doesn't seem like you need to group on `Version`, `CrateTS`, and `UpdateTS` since they are all used in aggregate funcitons. Or you don't need to use them in the functions since you are grouping on them. — juharr, May 02 '23 at 18:11

score 1 · Accepted Answer · answered May 03 '23 at 07:03

var result = dbContext.Contributions
    .Where(c => c.ContributorId == "08db4b393b1f")
    .GroupBy(c => new { c.ContributorId, c.Version, Date = (c.CreateTS >= c.UpdateTS ? c.CreateTS.Date : c.UpdateTS.Date) })
    .Select(g => new {
        g.Key.ContributorId,
        Version = g.Max(c => c.Version),
        Date = g.Max(c => (c.CreateTS >= c.UpdateTS ? c.CreateTS.Date : c.UpdateTS.Date))
    })
    .OrderByDescending(r => r.Version)
    .Take(1)
    .SingleOrDefault();

I think this should be it.

How to craft a linq query with a multi-column "Group By"

1 Answers1