Why isn't this LINQ Group By aggregating the rows in vb.net?

entity-framework, linq, linq-to-entities, sql, vb.net

Solution

From CG In CGroup

This line calls Queryable.SelectMany and unpacks the groups. Don't unpack your groups!

Remove that line and end with:

Select New With {
     .ClientID = CGroup.Key.ClientID,
     .CredentialID = CGroup.Key.CredentialID,
     .MaxVersion = CGroup.Max(Function(p) p.Version)
}

Problem

I have a table that has a 2 part key; an ID column, and a version number. I'm trying to select 'only the latest version' of the records. I'm trying to use the expression style of LINQ, to achieve a GROUP BY, and pull out the aggregate MAX value of the Version. However no aggregation is taking place. Summarized Code: ``` From C In Credentials Group Join .... Group Join .... Group C.CredentialID, NE.ClientID, C.Version By NE.ClientID, C.CredentialID Into CGroup = Group From CG In CGroup Select New With { .ClientID = CG.ClientID, .CredentialID = CG.CredentialID, .MaxVersion = CGroup.Max(Function(p) p.Version) } ``` Actual Results: ``` ClientID CredentialID MaxVersion 1196 1 3 1196 1 3 1196 1 3 1196 2 1 ``` Desired Results: ``` ClientID CredentialID MaxVersion 1196 1 3 1196 2 1 ``` Also tried, same results: ``` Group C By Key = New With {Key NE.ClientID, Key C.CredentialID} Into CGroup = Group From CG In CGroup Select New With { .ClientID = Key.ClientID, .CredentialID = Key.CredentialID, .MaxVersion = CGroup.Max(Function(p) p.Version) } ``` I'm looking for a solution that does not involves creating custom classes with matching properties and custom sorting / grouping functions, and does not use the lambda expressions on the tail end of the query. Thanks!

Original source