【VB】LINQ ～ GroupBy編 ～

VB.NET

■ サンプルに使用するクラス・データ

* クラス 
NotInheritable Class Company
    Public Property Id As String

    Public Property CountryCode As String

    Public Property FieldType As String

    Public Property Name As String

    Public Property Point As Integer
End Class
* データ 
Dim values As New List(Of Company) From
{
    New Company With {.Id = "X001", .CountryCode = "JP", .FieldType = "Car",
    .Name = "Toyota", .Point = 34},
    New Company With {.Id = "X002", .CountryCode = "JP", .FieldType = "Industry",
    .Name = "Hitachi", .Point = 33},
    New Company With {.Id = "Y001", .CountryCode = "US", .FieldType = "IT",
    .Name = "Google", .Point = 49},
    New Company With {.Id = "Y002", .CountryCode = "US", .FieldType = "IT",
    .Name = "Yahoo", .Point = 38},
    New Company With {.Id = "X003", .CountryCode = "JP", .FieldType = "Industry",
    .Name = "Mitsubishi", .Point = 29},
    New Company With {.Id = "X004", .CountryCode = "JP", .FieldType = "Industry",
    .Name = "Toshiba", .Point = 17},
    New Company With {.Id = "X005", .CountryCode = "US", .FieldType = "Industry",
    .Name = "Motorola", .Point = 34},
    New Company With {.Id = "Z001", .CountryCode = "EU", .FieldType = "IT",
    .Name = "SAP", .Point = 40},
    New Company With {.Id = "X006", .CountryCode = "US", .FieldType = "IT",
    .Name = "Microsoft", .Point = 45},
    New Company With {.Id = "Z002", .CountryCode = "EU", .FieldType = "IT",
    .Name = "Nokia", .Point = 24}
}

■ サンプル１： GroupByのみ

Dim coms = values.GroupBy(Function(x) x.CountryCode).ToList()
For Each comValue In coms
    Debug.WriteLine("{0} {1}件", comValue.Key, comValue.Count)
Next

出力結果

JP 4件
US 4件
EU 2件

■ サンプル２： GroupBy + Max

* クエリ構文(Into:グループ化した後に絞り込む事ができる。SQLのHaving的な) 
Dim coms = From com In values
           Group com By com.CountryCode Into groupCom = Group
           Select CountryCode, MaxPoint = groupCom.Max(Function(x) x.Point)

For Each comValue In coms
    Debug.WriteLine("{0} : {1}", comValue.CountryCode, comValue.MaxPoint)
Next
* メソッド構文 
Dim coms = values.GroupBy(Function(x) x.CountryCode) _
    .Select(Function(g) New With
                {
                .CountryCode = g.Key,
                .MaxPoint = g.Max(Function(y) y.Point)
                })

For Each comValue In coms
    Debug.WriteLine("{0} {1}件", comValue.CountryCode, comValue.MaxPoint)
Next

出力結果

JP 34件
US 49件
EU 40件

補足：Linq結果を値をDictionary化するには...

* 「ToDictionary(Function(x) x.【Key値】)」で行う 
Dim coms = From com In values
           Group com By com.CountryCode Into groupCom = Group
           Select New With
               {
               .CountryCode = CountryCode,
               .Point = groupCom.Max(Function(x) x.Point)
               }
' ★ここ★
Dim companies = coms.ToDictionary(Function(x) x.CountryCode)

For Each comValue In coms
    Debug.WriteLine("{0} {1}件", comValue.Key, comValue.Value)
Next

■ サンプル３： GroupBy(複合キー) + Sum

Dim companies =
   values.GroupBy(Function(group) New With
   {
      Key group.CountryCode,
      Key group.FieldType
   }) _
   .Select(Function(group) New Company() With
   {
      .CountryCode = group.Key.CountryCode,
      .FieldType = group.Key.FieldType,
      .Point = group.Sum(Function(x) x.Point)
   })
For Each companyValue In companies
   Debug.WriteLine("{0} {1} {2}",
      companyValue.CountryCode,
      companyValue.FieldType,
      companyValue.Point)
Next

出力結果

JP Car 34
JP Industry 79
US IT 132
US Industry 34
EU IT 64

参考文献

MSDN
https://msdn.microsoft.com/ja-jp/library/bb386922.aspx
https://msdn.microsoft.com/ja-jp/library/bb531412.aspx
MSDN / GroupBy(複合キー) + Sum
http://blogs.msmvps.com/deborahk/grouping-and-summing-with-lambda-expressions/
Dictionary化するのに参考にしたサイト
http://zawapro.com/?p=1321
http://qiita.com/RyotaMurohoshi/items/ba4ade6c6c9dc40b6217
その他の一般サイト
http://vb.xn--netde-rm4dun6em173aog4b.com/302.htm
http://jyuch.hatenablog.com/entry/2015/02/06/222306
https://karuakun.wordpress.com/2014/01/10/vb-net%E3%81%A7%E8%A4%87%E6%95%B0%E9%A0%85%E7%9B%AE%E3%81%AE%E3%82%B0%E3%83%AB%E3%83%BC%E3%83%94%E3%83%B3%E3%82%B0%E3%82%92%E8%A1%8C%E3%81%86%E6%99%82%E3%81%AFkey%E3%82%AD%E3%83%BC%E3%83%AF%E3%83%BC/
http://blog.livedoor.jp/ponpoko_saitoko/archives/25144630.html
http://ivis-mynikki.blogspot.jp/2014/04/vbnet-linqgroupby.html
http://www.atmarkit.co.jp/fdotnet/special/vblinq02/vblinq02_01.html

関連記事

C#との差異　(Linq・ラムダ式編)

http://blogs.yahoo.co.jp/dk521123/26758113.html

【VB】LINQ ～ 基本編 ～

http://blogs.yahoo.co.jp/dk521123/35757282.html