Search code examples
sqllinqgroup-bylinq-group

Linq Grouping - aggregate, outside of a group by

I've got a SQL query, that works as follows:

SELECT TOP 100 
    Max(Table_ID) as Max_ID, 
Col1,
Col2,
Col3,
COUNT(*) AS Occurences
FROM myTable
GROUP BY Col1, Col2, Col3
ORDER BY Occurences DESC

How can I write an identical Linq query?

The issue is, that as soon as I apply my grouping, I cannot access the non-grouped columns Table_ID in my case.

var errors = from r in MyTable
    group e by new {e.Col1, e.Col2} into g
    orderby g.Count() descending
    select new {MaxId = ???, Count =  g.Count(), g.Key.Col1, g.Key.Col2};
Solution

  • Use g.Max(x => x.TableID):

    var errors = from r in MyTable
    group e by new {e.Col1, e.Col2} into g
    orderby g.Count() descending
    select new {MaxId = g.Max(x => x.TableID), 
                Count =  g.Count(), g.Key.Col1, g.Key.Col2};

    (Assuming you want the maximum within each group, of course.)