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

Question

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!

Answer 1

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

Answer 2

The problem is that the anonymous class defined for the key does not evaluate equality in the way you like. Instead, you can use a Tuple for the key:

From C In Credentials
... 
Group C.CredentialID, NE.ClientID, C.Version By key = 
       New Tuple(Of Integer, Integer)(NE.ClientID, C.CredentialID) 
       Into CGroup = Group 
Select New With {
       .ClientID = key.Item1, 
       .CredentialID = key.Item2, 
       .MaxVersion = CGroup.Max(Function(p) p.Version)
}