LINQ首部曲： LINQ To Object Part 2 - Using VB.NET
文/黃忠成
續前文，這一篇一樣是發表於Run! PC雜誌，同時收錄於【極意之道-.NET Framework 3.5資料庫開發聖典-ASP.NET篇】一書中，於此我針對VB.NET做了調整。
回到起點: LINQ 語法規則
任何語言皆有其基本語法規則存在，LINQ雖然被打造成一個與編譯器無關的Framework，但為了讓設計師能更方便、更直覺的使用LINQ Expression，目前已知的C#、VB.NET都定義了一套專屬的LINQ Expression語法，雖然各個編譯器所提供的語法略有差異，但基本上差距不遠，本節以VB.NET所提供的LINQ Expression為基準，為讀者們介紹LINQ Expression的語法規則及各式各樣的應用，表1是筆者所整理出來的VB.NET LINQ Expression語法規則。
[表1]
|
u query-expression
From <alias> In <source|query-expression> <<where-expression>|<group-by expression>|<join expression>|<order-by-expression>> <select-expression>
u where-expression
left-variable <operand> right-variable
n operand
(= | <> | < | > | >= | <=)
left-variable <operand> query-expression(single value result)
query-expression(single value result) <operand> left-variable
query-expression(single value result) <operand> query-expression(single value result)
variable.<function> (Boolean result)
variable.<function> operand right-variable
variable.<function> operand variable<function>
<shared function> operand right-varaible
<shared function> operand <shared function>
<function> operand right-varaible
<function> operand <shared function>
<VB.NET Expression single line> (Boolean result)
<VB.NET Expression single line> operand right-variable
<VB.NET Expression single line> operand <VB.NET Expression single line>
where-expression < And | Or | AndAlso | OrElse>, where-expression <*>
u group-by expression
group <alias | query-expression> by <alias.member|query-expression(single value result)> into <variable>
group <alias | query-expression> by <alias.member| query-expression(single value result)>,<alias.member| query-expression(single value result)> into <variable>
u join-expression
join <alias > in <source|queryt-expression> on <where-expression(equal only)>
join <alias > in <source|queryt-expression> on <where-expression(equal only)> into <variable>
u order-by-expression
order by <alias-member> <asc|desc>
u select-expression
Select <alias member>
Select new With {anonymous type declaration}
|
乍看之下，此表似乎相當複雜，其實她還蠻簡單的，以此表搭配下文的語法分類解說，應能讓讀者們快速的掌握VB.NET中的LINQ Expression語法。
Query-Expression
LINQ Expression中至少會包含一個query-expression，表一中對query-expression的定義如下。
|
From <alias> In <source|query-expression> <<where-expression>|<group-by expression>|<join expression>|<order-by-expression>> <select-expression>
|
<alias>指的是欲在此query-expression中使用的別名，更明確說是一個變數，編譯器會將In後面的運算式所產生的結果放到此變數中，此處有一點必須特別指出，In後面的<source|query-exprssion>代表著來源，<soruce>可以是一個IEnumerable(Of T)類型的物件，法則上<source>也可以由另一個<query-exprssion>所取代，我們將此稱為Nested query-expression(巢狀Query)。Query-expression中可以包含Where語句<where-expression>、Group By語句<group expression>、Join語句<join expression>、Order By修飾句<order-by-exprssion>及Select語句<select-expression>，因應不同的語句，其內或允許query-exprssion的出現，下例是一個簡單的query-expression。
|
Dim list() As String = {"1111", "2222", "3333"}
Dim p = From o In list Select o
|
Where-expression
Query-expression中可以包含where-expression，用來對物件作查詢動作，下例是一個簡單的例子。
|
Dim list() As String = {"1111", "2222", "3333"}
Dim p = From o In list Where o = "2222" Select o
|
如表一所示，where-expression的left-expression及 right-expression也可以由query-expression所取代，前提是這個query-expression必須傳回單一值，如程式1。
[程式1]
|
Sub TestComplexWhere2()
Dim p1() = { _
New With {.Name = "code6421", .Address = "Taipai"}, _
New With {.Name = "tom", .Address = "Taipai"}, _
New With {.Name = "jeffray", .Address = "NY"} _
}
Dim p2() = { _
New With {.Name = "code6421", .Title = "Manager"}, _
New With {.Name = "tom", .Title = "Director"}, _
New With {.Name = "jeffray", .Title = "Programmer"} _
}
Dim p3() = { _
New With {.Name = "code6421", .Hand = "Manager", .Address = "Taipai"}, _
New With {.Name = "tom", .Hand = "Director", .Address = "Taipai"}, _
New With {.Name = "jeffray", .Hand = "Programmer", .Address = "Taipai"} _
}
Dim result = From s In p2 _
Where (From s1 In p1 Where s1.Name = s.Name Select s1.Address).ToArray()(0) = _
(From s2 In p3 Where s2.Name = s.Name Select s2.Address).ToArray()(0) Select s
For Each item In result
Console.WriteLine(item.Name)
Next
Console.ReadLine()
End Sub
|
ToArray函式是LINQ To Object Framework所提供的函式，用途是將query-expression的結果轉成陣列，一旦轉成陣列後就可以透過()陣列元素存取值去取得單值。
當然，基於LINQ Expression的轉譯規則，你也可以使用傳回Boolean值的函式來協助比對。
|
Sub TestWhereWithFunction()
Dim list() As String = {"code6421", "tom", "cathy"}
Dim result = From s1 In list Where MyExpressionFunc(s1) Select s1
End Sub
Function MyExpressionFunc(ByVal s As String) As Boolean
Return IIf(s = "code6421", True, False)
End Function
|
這個函式可以是成員函式(需要有物件、靜態函式，或是位於Module中的函式皆可。
Group-expression
Query-expression中允許含有Group-expression，用於將資料分類用，如程式2。
[程式2]
|
Sub TestGroupByLinq()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
New With {.Name = "catch", .Age = 18, .Address = "USA"}, _
New With {.Name = "joe", .Age = 18, .Address = "NY"} _
}
Dim result = From o In persons Group o By o.Address Into g = Group _
Select New With {.Address = Address, .Persons = g}
For Each item In result
Console.WriteLine("Group : {0}", item.Address)
For Each detailItem In item.Persons
Console.WriteLine("{0}", detailItem.Name)
Next
Next
Console.ReadLine()
End Sub
|
此例執行結果如圖1。
[圖1]
|
Group : Taipai
code6421
Group : USA
jeffray
catch
Group : NY
joe
|
此處有兩個尚未提及的指令，Into是將Group後的結果放到g變數中，於VB.NET中，你必須指定g的值為Group關鍵字，此處Group關鍵字的內涵值為Group後的結果，是一個IEnumerable(Of Object)的物件。Select New With則是應用了VB.NET 2008的Anonymous Type技巧，建立一個簡單的物件，其中含有Address及Persons兩個屬性，Address的值來自於Address，也就是Group o By o.Address的鍵值，那就是Address的值。使用Select New With時，記得屬性名稱需以【.】作為前導字，如下：
|
Select New With {.Name = "code6421", .Age = 18, .Address = "Taipai"},
|
New With除了可放在Select之後，也可直接用來建立Anonymous Type，如下所示：
|
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}
|
而g本身是一個分類後的IEnumerable(Of Object)類型物件，其內所含的就是以Address值所分類後的Persons物件。與where-expression一樣，group-expression中也可以包含query-expression，如程式4。
[程式4]
|
Sub TestComplexGroupBy2()
Dim p2() = { _
New With {.Name = "code6421", .Hand = "Manager", .Address = "Taipai"}, _
New With {.Name = "tom", .Hand = "Director", .Address = "Taipai"}, _
New With {.Name = "jeffray", .Hand = "Programmer", .Address = "NT"} _
}
Dim result = From s In p2 Group s By Address = (From s1 In p2 Where _
s.Name = s1.Name Select s1.Address).ToArray()(0) Into g = Group _
Select New With {.Address = Address, .Persons = g}
For Each item In result
Console.WriteLine("Group : {0}", item.Address)
For Each detailItem In item.Persons
Console.WriteLine("{0}", detailItem.Name)
Next
Next
Console.ReadLine()
End Sub
|
執行結果如圖2。
[圖2]
|
Group : Taipai
code6421
tom
Group : NT
jeffray
|
Join-expression
LINQ Expression也支援SQL中常用的Join指令，如程式5。
[程式5]
|
Sub TestJoin()
Dim p1() = { _
New With {.Name = "code6421", .Address = "Taipai"}, _
New With {.Name = "tom", .Address = "Taipai"}, _
New With {.Name = "jeffray", .Address = "NY"} _
}
Dim p2() = { _
New With {.Name = "code6421", .Title = "Manager"}, _
New With {.Name = "tom", .Title = "Director"}, _
New With {.Name = "jeffray", .Title = "Programmer"} _
}
Dim result = From s In p1 _
Join s1 In p2 On s.Name Equals s1.Name _
Select New With {.Name = s.Name, .Address = s.Address, .Title = s1.Title}
For Each item In result
Console.WriteLine("Name : {0}, Address : {1}, Title : {2}", _
item.Name, item.Address, item.Title)
Next
Console.ReadLine()
End Sub
|
圖3是執行結果。
[圖3]
|
Name : code6421, Address : Taipai, Title : Manager
Name : tom, Address : Taipai, Title : Director
Name : jeffray, Address : NY, Title : Programmer
|
程式6是運用一個以上join的例子。
[程式6]
|
Sub TestJoin2()
Dim p1() = { _
New With {.Name = "code6421", .Address = "Taipai"}, _
New With {.Name = "tom", .Address = "Taipai"}, _
New With {.Name = "jeffray", .Address = "NY"} _
}
Dim p2() = { _
New With {.Name = "code6421", .Title = "Manager"}, _
New With {.Name = "tom", .Title = "Director"}, _
New With {.Name = "jeffray", .Title = "Programmer"} _
}
Dim p3() = { _
New With {.Name = "code6421", .Hand = "Left"}, _
New With {.Name = "tom", .Hand = "Left"}, _
New With {.Name = "jeffray", .Hand = "Right"} _
}
Dim result = From s In p1 _
Join s1 In p2 On s.Name Equals s1.Name _
Join s2 In p3 On s.Name Equals s2.Name _
Select New With {.Name = s.Name, .Address = s.Address, _
.Title = s1.Title, .Hand = s2.Hand}
For Each item In result
Console.WriteLine("Name : {0}, Address : {1}, Title : {2}, Hand : {3}", _
item.Name, item.Address, item.Title, item.Hand)
Next
Console.ReadLine()
End Sub
|
執行結果如圖4。
[圖4]
|
Name : code6421, Address : Taipai, Title : Manager, Hand : Left
Name : tom, Address : Taipai, Title : Director, Hand : Left
Name : jeffray, Address : NY, Title : Programmer, Hand : Right
|
Order-by-expression
Order-by-expression用於將query-expression的結果集排序，如程式7。
[程式7]
|
Sub TestOrderLinq()
Dim p() = {"1111", "3333", "2222"}
Dim result = From s1 In p Order By s1 Descending Select s1
For Each item In result
Console.WriteLine(item)
Next
Console.ReadLine()
End Sub
|
圖5是執行結果。
[圖5]
Descending指的是倒序，Ascending則是正序，未加上Descending也未加上Ascending，則預設為Ascending，Order By可以有多個，彼此以 , 分接，每個都能有正倒序之修飾字，如下所示。
|
Sub TestOrderLinq2()
Dim p1() = { _
New With {.Name = "code6421", .Address = "Taipai"}, _
New With {.Name = "tom", .Address = "Taipai"}, _
New With {.Name = "jeffray", .Address = "NY"} _
}
Dim result = From s1 In p1 Order By s1.Name, s1.Address Descending Select s1
For Each item In result
Console.WriteLine(item.Name)
Next
Console.ReadLine()
End Sub
|
Select-expresssion
Select-expression出現於query-expression的尾端，涵意是由結果集中取出資料，其中較特別的是，有時我們會使用Select New With語句由結果集中取出需要的欄位，建構出另一個型態的回傳物件，如同於join範例中的片段。
|
Dim result = From s In p1 _
Join s1 In p2 On s.Name Equals s1.Name _
Join s2 In p3 On s.Name Equals s2.Name _
Select New With _
{.Name = s.Name, .Address = s.Address, .Title = s1.Title, .Hand = s2.Hand}
|
這是運用了VB.NET 2008的Anonymouse Type功能所達到的。
Distinct
如同SQL中的Distinct一樣，LINQ To Object Framework中的Distinct函式允許設計師將一陣列中特定欄位值相同的部份，僅擷取出一代表值，見程式8。
[程式8]
|
Sub TestSelectDistinct()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
New With {.Name = "catch", .Age = 18, .Address = "USA"}, _
New With {.Name = "joe", .Age = 18, .Address = "NY"} _
}
Dim result = (From s1 In persons Select s1.Address).Distinct()
For Each item In result
Console.WriteLine(item)
Next
Console.ReadLine()
End Sub
|
此例的執行結果如圖6。
[圖6]
此例僅列出一個Taipai，這就是Distinct函式的能力，相同值者僅取一代表值。
Select Many 功能
截至目前為止的例子皆以一個query-expression作為開始，事實上LINQ Expression支援query-expression的串接，名為Select Many功能，見程式9。
[程式9]
|
Sub TestSelectMany()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
New With {.Name = "catch", .Age = 18, .Address = "USA"}, _
New With {.Name = "joe", .Age = 18, .Address = "NY"} _
}
Dim p2() = { _
New With {.Title = "Director"}, _
New With {.Title = "Programmer"}, _
New With {.Title = "Manager"} _
}
Dim result = From s1 In persons _
From s2 In p2 _
Select New With {.Name = s1.Name, .Title = s2.Title}
For Each item In result
Console.WriteLine("Name : {0}, Title : {1}", item.Name, item.Title)
Next
Console.ReadLine()
End Sub
|
此例的執行結果如圖7。
[圖7]
|
Name : code6421, Title : Director
Name : code6421, Title : Programmer
Name : code6421, Title : Manager
Name : jeffray, Title : Director
Name : jeffray, Title : Programmer
Name : jeffray, Title : Manager
Name : catch, Title : Director
Name : catch, Title : Programmer
Name : catch, Title : Manager
Name : joe, Title : Director
Name : joe, Title : Programmer
Name : joe, Title : Manager
|
Index
LINQ To Object Framework的LINQ Expression允許指定Index，什麼是Index呢 ？請看程式10。
[程式10]
|
Sub TestIndex()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
New With {.Name = "catch", .Age = 18, .Address = "USA"}, _
New With {.Name = "joe", .Age = 18, .Address = "NY"} _
}
Dim result = From s1 In persons Select s1.Name(0)
For Each item In result
Console.WriteLine(item)
Next
Console.ReadLine()
End Sub
|
此例會列出陣列中Name屬性值的第一個字元，事實上這是當然的結果，因為where後面的語句，會被VB.NET編譯器當成Lambda Expression處理，而Name屬性是string型態，自然也能對其使用()陣列存取子了，同時也因為是string型別，而且會被轉為Lambda Expression，自然能呼叫屬於string型別的Contains函式了。
Take、TakeWhile
Take函式可以讓設計師由一個IEnumerable<T>物件中取出指定的元素數量，請見程式11。
[程式11]
|
Sub TestTake()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
New With {.Name = "catch", .Age = 18, .Address = "USA"}, _
New With {.Name = "joe", .Age = 18, .Address = "NY"} _
}
Dim result = persons.Take(2)
For Each item In result
Console.WriteLine(item.Name)
Next
Console.ReadLine()
End Sub
|
此例會從p1中取出前兩個元素，也就是code6421及jeffray，Take函式的功能有點像是SQL Server的Select TOP。另一個TakeWhile函式則可以讓設計師以while的方式來取出IEnumerable<T>中的元素，請見程式12。
[程式12]
|
Sub TestTakeWhile()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
New With {.Name = "catch", .Age = 18, .Address = "USA"}, _
New With {.Name = "joe", .Age = 18, .Address = "NY"} _
}
Dim result = persons.TakeWhile(Function(p) p.Address = "Taipai")
For Each item In result
Console.WriteLine(item.Name)
Next
Console.ReadLine()
End Sub
|
此例的執行結果與Take相同，不同之處在於TakeWhile會一直取出元素，直到某個元素不符合所指定的Lambda Expression為止，這意味著假如指定Address == “USA”時，將不會有任何元素列出，因為陣列中的第一個元素就已經不符合條件了，所以While動作就結束了。
Skip、SkipWhile
相對於Take，Skip允許設計師略過指定的元素數，如程式13。
[程式13]
|
Sub TestSkip()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
New With {.Name = "catch", .Age = 18, .Address = "USA"}, _
New With {.Name = "joe", .Age = 18, .Address = "NY"} _
}
Dim result = (From s1 In persons Select s1).Skip(2)
For Each item In result
Console.WriteLine(item.Name)
Next
Console.ReadLine()
End Sub
|
此例會由catch的元素開始列出。另一個SkipWhile函式與TakeWhile的概念相同，SkipWhile也是以While的觀念執行Skip動作，如程式14。
[程式14]
|
Sub TestSkipWhile()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
New With {.Name = "catch", .Age = 18, .Address = "USA"}, _
New With {.Name = "joe", .Age = 18, .Address = "NY"} _
}
Dim result = (From s1 In persons Select s1).SkipWhile(Function(p) p.Address = "Taipai")
For Each item In result
Console.WriteLine(item.Name)
Next
Console.ReadLine()
End Sub
|
與TakeWhile一樣，若指定Address == “USA”時，那麼將會列出所有元素，因為在第一個元素時，While就已經結束了。
First、FirstOrDefault
First函式允許設計師指定一個Lambda Expression條件式， 她將以此對IEnumerable(Of T)中的元素查詢，並傳回第一個符合條件的元素，當不指定條件式時，First會傳回第一個元素。與Where不同，First只會傳回符合資料的第一個元素，而非內含所有符合條件元素的IEnumerable(Of T)結果集，見程式15。
[程式15]
|
Sub TestFirst()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
New With {.Name = "catch", .Age = 18, .Address = "USA"}, _
New With {.Name = "joe", .Age = 18, .Address = "NY"} _
}
Dim result = (From s1 In persons Select s1).First()
Console.WriteLine(result.Name)
Console.ReadLine()
End Sub
|
下例則是使用條件式的寫法。
|
Sub TestFirst2()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
New With {.Name = "catch", .Age = 18, .Address = "USA"}, _
New With {.Name = "joe", .Age = 18, .Address = "NY"} _
}
Dim result = (From s1 In persons Select s1).First(Function(p) p.Address = "USA")
Console.WriteLine(result.Name)
Console.ReadLine()
End Sub
|
當IEnumerable(Of T)中未含有符合條件的元素時，First將會引發例外。另一個FirstOrDefault函式與First函式類似，唯一不同之處是當IEnumerable(Of T)中未含有符合條件之元素時，她將回傳元素之型別的預設值(object為null、數值為0)。與First相對的是Last函式，可取得最後一個元素，她也有另一個同型函式為LastOrDefault，設計理念與FirstOrDefault相同。
ElementAt、ElementAtOrDefault
ElementAt函式可以取得一個物件集中特定位置的元素，如下所示：
|
Sub TestElementAt()
Dim p1() As String = {"code6421", "tom", "cathy"}
Dim result = p1.ElementAt(1)
Console.WriteLine(result)
Console.ReadLine()
End Sub
|
此例會列出tom這個元素，當指定位置超出物件集大小時，ElementAt會拋出例外。如同FirstOrDefault一般，ElementAt有另一個同型函式：ElementAtOrDefault，當使用此函式時指定位置超出物件集大小時，不會觸發例外，而是直接回傳預設值(object為null，數值為0)。
ToArray、ToList、ToDictionary
這三個函式會將IEnuermable(Of T)轉成Array、List或是Dictionary型別，預設情況下，當我們對某個IEnumerable(Of T)下達Where等條件式時，所取得的結果會是一個IEnumerable(Of T)物件，此時所有條件都尚未執行比對的動作，當對這個IEnumerable(Of T)物件下達MoveNext(For Each會觸發此函式)時，該物件才會進行條件比對。ToArray等函式可以改變此行為模式，當我們對IEnumerable(Of T)物件呼叫這些函式時，其將會以For Each一一巡覽IEnumerable(Of T)物件中的元素並進行條件比對，然後放到另一個結果值後傳回，這也就是說，呼叫此函式所傳回的結果值，將會是已經完成比對後的結果值，操作這個結果集自然比直接操控具條件的結果集來得有效率，程式16是一個使用ToDictionary函式的例子。
[程式16]
|
Sub TestToDictionary()
Dim persons() = { _
New With {.Name = "code6421", .Age = 18, .Address = "Taipai"}, _
New With {.Name = "jeffray", .Age = 18, .Address = "USA"}, _
&nb |