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を実装した時にうまく結合できない。
時間が無いので放置