Linq Grouping - aggregate, outside of a group by

Question

hello, 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};

Any ideas would be appreciated, cheers.

Dave

Answer 1

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.)

Answer 2

Jon's answer is good, I just want to elaborate a little on why:

The issue is, that as soon as i apply my grouping, I cannot access the non-grouped columns

r went out of scope... why is that?

The two ways of ending a query are select or group by clauses. When you add the query continuation clause into g to the group by, you are saying - the top level elements of the query are g and all variables introduced in the query up to this point are removed from scope.

If you search this msdn article for the word splice, you can see samples.

Don't confuse this use of into with join on equals into, which is a group join, not a query continuation. Group join does not remove previous variables from scope.