CODEWITHSUNDEEP
vb.netlinqlistgroup-by
Joss57
Joss57

Reputation: 467

LINQ Group by with multiple properties in VB.Net

I spent a lot of time on this problem. I am able to do simple Group By LINQ queries (on one property) but for multiple fields I'm a little stuck... Here is a LINQPad sample of what I want to do :

dim lFinal={new with {.Year=2010, .Month=6, .Value1=0, .Value2=0}, 
            new with {.Year=2010, .Month=6, .Value1=2, .Value2=1},
            new with {.Year=2010, .Month=7, .Value1=3, .Value2=4},
            new with {.Year=2010, .Month=8, .Value1=0, .Value2=1},
            new with {.Year=2011, .Month=1, .Value1=2, .Value2=2},
            new with {.Year=2011, .Month=1, .Value1=0, .Value2=0}}

Dim lFinal2 = From el In lFinal
              Group el By Key = new with {el.Year,el.Month}
              Into Group
              Select New With {.Year = Key.Year, .Month=Key.Month, .Value1 = Group.Sum(Function(x) x.Value1), .Value2 = Group.Sum(Function(x) x.Value2)}

lFinal.Dump()
lFinal2.Dump()

The lFinal list has 6 items, I want the lFinal2 to have 4 items : 2010-6 and 2011-1 should group.

Thanks in advance.

Upvotes: 9

Views: 22228

Answers (3)

soyka
soyka

Reputation: 191

Make the properties in the anonymous type immutable using the Key keyword and then they will be used for comparisons 

    Dim lFinal2 = From el In lFinal               
    Group el By Key = new with {key el.Year, key el.Month} 
    Into Group
    Select New With {
          .Year = Key.Year, 
          .Month = Key.Month, 
          .Value1 = Group.Sum(Function(x) x.Value1),
          .Value2 = Group.Sum(Function(x) x.Value2)
    }

Upvotes: 19

Joss57
Joss57

Reputation: 467

Thanks ! But I noticed I needed to also write the GetHashCode function to make it works. I provide the VB.Net translation of the final class + LINQ GroupBy :

Class :

Public Class YearMonth
    Implements IEquatable(Of YearMonth)

    Public Property Year As Integer
    Public Property Month As Integer

    Public Function Equals1(other As YearMonth) As Boolean Implements System.IEquatable(Of YearMonth).Equals
        Return other.Year = Me.Year And other.Month = Me.Month
    End Function

    Public Overrides Function GetHashCode() As Integer
        Return Me.Year * 1000 + Me.Month * 100
    End Function
End Class

And the LINQ query :

Dim lFinal2 = From el In lFinal
              Group el By Key = New YearMonth With {.Year = el.Year, .Month = el.Month}
              Into Group
              Select New ItemsByDates With {.Year = Key.Year,
                                            .Month = Key.Month,
                                            .Value1 = Group.Sum(Function(x) x.Value1),
                                            .Value2 = Group.Sum(Function(x) x.Value2)}

Upvotes: 6

Adam
Adam

Reputation: 1212

Not 100% sure but group by probably uses the Equals() and/or GetHashCode implementation, so when you do the implicit creation:

= Group el By Key = new with {el.Year,el.Month}

the implicit object doesn't know to check both year and month (just because it has the properties doesn't mean it checks them when comparing to other objects).

So you'll probably need to do something more like this:

= Group el By Key = new CustomKey() { Year = el.Year, Month = el.Month };

public class CustomKey{
    int Year { get; set; }
    int Month { get; set; }

    public override bool Equals(obj A) {
        var key (CustomKey)A;
        return key.Year == this.Year && key.Month == this.Month;
    }
}

Upvotes: 3

Related Questions