LINQ＋メソッド構文＋VB.NETは例が少ないので備忘録として文書化
忘れるのは毎回JOINやGROUPJOINやGROUPBY
EFを使うときはもうちょっと楽だけど基本は同じ
#入力データ
クラス
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
ダミーデータ
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)})
#サンプルコード
##１．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を実装した時にうまく結合できない。
時間が無いので放置