Why is this LINQ group not concatenating strings in vb.net?

Question

I have a table with 2 keys; ID column and version number. I am trying to select "only the latest version" of entries.

I am trying to use the LINQ expression style to get GROUP BY, and pull the total value of MAX for Version. However, aggregation does not occur .

Generalized 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 the 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 am looking for a solution that does not include creating custom classes with the appropriate properties and custom sort / group functions and does not use lambda expressions in the tail of the request.

Thanks!

Answer 1

 From CG In CGroup 

This line calls Queryable.SelectMany and unpacks the groups. Do not unpack your groups!

Delete this 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 as you like. Instead, you can use 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) }