VB.NET用のLINQサンプル

LINQ＋メソッド構文＋VB.NETは例が少ないので備忘録として文書化
忘れるのは毎回JOINやGROUPJOINやGROUPBY
EFを使うときはもうちょっと楽だけど基本は同じ

#入力データ
<details>
<summary>クラス</summary>
<div>

```VB
    Public Class SampleHeader
        Public Property HeaderId As Long
        Public Property HeaderName As String
        Public Property LastUpdate As Date

    End Class

    Public Class SampleDetail
        Public Property DetailId As Long
        Public Property HeaderId As Long
        Public Property ItemId As Long
        Public Property Quantity As Integer
        Public Property LastUpdate As Date
    End Class

    Public Class SampleItem
        Public Property ItemId As Long
        Public Property ItemName As String
        Public Property ItemTypeCode As String
        Public Property ItemPrice As Decimal
        Public Property LastUpdate As Date
    End Class

    Public Class SampleDetailWithItem
        Public Property DetailId As Long
        Public Property HeaderId As Long
        Public Property ItemId As Long
        Public Property Quantity As Integer
        Public Property ItemName As String
        Public Property ItemTypeCode As String
        Public Property ItemPrice As Decimal
    End Class
```
</div>
</details>

<details>
<summary>ダミーデータ</summary>
<div>

```VB
        Dim i_headers As New List(Of SampleHeader)
        Dim i_details As New List(Of SampleDetail)
        Dim i_items As New List(Of SampleItem)

        i_items.Add(New SampleItem With {.ItemId = 0, .ItemName = "ぱっくら", .ItemPrice = "100", .ItemTypeCode = "ACT", .LastUpdate = Now})
        i_items.Add(New SampleItem With {.ItemId = 1, .ItemName = "どらくえ", .ItemPrice = "400", .ItemTypeCode = "RPG", .LastUpdate = Now})
        i_items.Add(New SampleItem With {.ItemId = 2, .ItemName = "えふえふ", .ItemPrice = "500", .ItemTypeCode = "RPG", .LastUpdate = Now})
        i_items.Add(New SampleItem With {.ItemId = 3, .ItemName = "まりお", .ItemPrice = "300", .ItemTypeCode = "ACT", .LastUpdate = Now})
        i_items.Add(New SampleItem With {.ItemId = 4, .ItemName = "ぜびうす", .ItemPrice = "200", .ItemTypeCode = "STG", .LastUpdate = Now})

        i_headers.Add(New SampleHeader With {.HeaderId = 0, .HeaderName = "ぶたごりら", .LastUpdate = New Date(2017, 1, 1)})
        i_details.Add(New SampleDetail With {.DetailId = 0, .HeaderId = 0, .ItemId = 0, .Quantity = 1, .LastUpdate = Now})
        i_details.Add(New SampleDetail With {.DetailId = 1, .HeaderId = 3, .ItemId = 0, .Quantity = 1, .LastUpdate = Now})

        i_headers.Add(New SampleHeader With {.HeaderId = 1, .HeaderName = "とんがり", .LastUpdate = New Date(2018, 2, 1)})
        i_details.Add(New SampleDetail With {.DetailId = 2, .HeaderId = 1, .ItemId = 0, .Quantity = 3, .LastUpdate = Now})
        i_details.Add(New SampleDetail With {.DetailId = 3, .HeaderId = 1, .ItemId = 1, .Quantity = 3, .LastUpdate = Now})
        i_details.Add(New SampleDetail With {.DetailId = 4, .HeaderId = 1, .ItemId = 2, .Quantity = 3, .LastUpdate = Now})
        i_details.Add(New SampleDetail With {.DetailId = 5, .HeaderId = 1, .ItemId = 3, .Quantity = 3, .LastUpdate = Now})
        i_details.Add(New SampleDetail With {.DetailId = 6, .HeaderId = 1, .ItemId = 4, .Quantity = 3, .LastUpdate = Now})

        i_headers.Add(New SampleHeader With {.HeaderId = 2, .HeaderName = "きてれつ", .LastUpdate = New Date(2018, 1, 1)})
        i_details.Add(New SampleDetail With {.DetailId = 7, .HeaderId = 2, .ItemId = 0, .Quantity = 1, .LastUpdate = Now})

        i_headers.Add(New SampleHeader With {.HeaderId = 3, .HeaderName = "みよちゃん", .LastUpdate = New Date(2018, 1, 1)})
        i_details.Add(New SampleDetail With {.DetailId = 8, .HeaderId = 3, .ItemId = 0, .Quantity = 1, .LastUpdate = Now})
        i_details.Add(New SampleDetail With {.DetailId = 9, .HeaderId = 3, .ItemId = 1, .Quantity = 1, .LastUpdate = Now})
        i_details.Add(New SampleDetail With {.DetailId = 10, .HeaderId = 3, .ItemId = 2, .Quantity = 1, .LastUpdate = Now})
        i_details.Add(New SampleDetail With {.DetailId = 11, .HeaderId = 3, .ItemId = 3, .Quantity = 1, .LastUpdate = Now})
        i_details.Add(New SampleDetail With {.DetailId = 12, .HeaderId = 3, .ItemId = 4, .Quantity = 1, .LastUpdate = Now})

        i_headers.Add(New SampleHeader With {.HeaderId = 4, .HeaderName = "ころすけ", .LastUpdate = New Date(2018, 1, 1)})

```

</div>
</details>

#サンプルコード

##１．JOIN

```

        'Join
        Dim detail_with_item = i_details.Join(i_items,
                                          Function(detail) detail.ItemId,
                                          Function(item) item.ItemId,
                                          Function(detail, item) New SampleDetailWithItem With {
                                              .DetailId = detail.DetailId,
                                              .HeaderId = detail.HeaderId,
                                              .ItemId = detail.ItemId,
                                              .ItemName = item.ItemName,
                                              .ItemPrice = item.ItemPrice,
                                              .ItemTypeCode = item.ItemTypeCode,
                                              .Quantity = detail.Quantity})

```

##２．GROUP JOIN

```
        'GroupJoin
        Dim header_with_details = i_headers.GroupJoin(detail_with_item,
                                               Function(header) header.HeaderId,
                                               Function(detail) detail.HeaderId,
                                               Function(header, details) New With {
                                                .header = header,
                                                .details = details})


        Debug.WriteLine("GROUP JOIN")
        For Each record In header_with_details.ToList
            Dim header = record.header
            If record.details.Any Then
                For Each detail In record.details
                    Debug.WriteLine(header.HeaderName + vbTab + detail.ItemName)
                Next
            Else
                Debug.WriteLine(header.HeaderName + vbTab + "")
            End If
        Next
```

###出力
```
ぶたごりら	ぱっくら
とんがり	ぱっくら
とんがり	どらくえ
とんがり	えふえふ
とんがり	まりお
とんがり	ぜびうす
きてれつ	ぱっくら
みよちゃん	ぱっくら
みよちゃん	ぱっくら
みよちゃん	どらくえ
みよちゃん	えふえふ
みよちゃん	まりお
みよちゃん	ぜびうす
ころすけ	
```


##３．GROUPJOIN + SELECTMANY
```
        'GroupJoin+SelectMany
        Dim header_with_details2 = i_headers.GroupJoin(detail_with_item,
                                                       Function(header) header.HeaderId,
                                                       Function(detail) detail.HeaderId,
                                                       Function(header, details) New With {
                                                                .header = header,
                                                                .details = details}).
                                             SelectMany(Function(x) x.details.DefaultIfEmpty,
                                                        Function(x, detail) New With {
                                                            .header = x.header,
                                                            .detail = detail})

        Debug.WriteLine("GROUP JOIN(SelectMay)")
        For Each record In header_with_details2.ToList
            Debug.WriteLine(record.header.HeaderName + vbTab + If(record.detail IsNot Nothing, record.detail.ItemName, ""))
        Next

```


###出力
```
ぶたごりら	ぱっくら
とんがり	ぱっくら
とんがり	どらくえ
とんがり	えふえふ
とんがり	まりお
とんがり	ぜびうす
きてれつ	ぱっくら
みよちゃん	ぱっくら
みよちゃん	ぱっくら
みよちゃん	どらくえ
みよちゃん	えふえふ
みよちゃん	まりお
みよちゃん	ぜびうす
ころすけ	
```
(出力後の扱いが違うだけでどっちでも同じ）


##４．GROUP BY
```

        'GroupBy
        Dim grouped_detail = header_with_details2.Where(Function(x) x.detail IsNot Nothing).
                                                GroupBy(Function(x) x.detail.ItemTypeCode).
                                                Select(Function(x) New With {
                                                    .TYPE = x.Key,
                                                    .COUNT = x.Count,
                                                    .SUM = x.Sum(Function(y) y.detail.ItemPrice)})

        Debug.WriteLine("GROUPBY")
        For Each record In grouped_detail.ToList
            Debug.WriteLine(record.TYPE + " count:" + record.COUNT.ToString + " sum:" + record.SUM.ToString)
        Next

```

###出力
```
ACT count:7 sum:1100
RPG count:4 sum:1800
STG count:2 sum:400
```

#複合キーでのJOIN
複合キーを匿名クラスで表記してやればいい
が！！！！！！！！！！！！！！！！！、独自comparerを実装した時にうまく結合できない。
時間が無いので放置