【黃忠成】LINQ首部曲： LINQ To Object Part 2 - Using VB.NET

iDotNetSpace發表於2008-05-19
Object

這一篇一樣是發表於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 In <|||> <source>
u   where-expression
 left-variable  right-variable
n   operand
(= | <> | < | > | >= | <=)
 left-variable  query-expression(single value result)
 query-expression(single value result)    left-variable
query-expression(single value result)    query-expression(single value result)
 variable. (Boolean result)
 variable. operand right-variable
 variable. operand variable
  operand right-varaible
  operand
  operand right-varaible
  operand
  (Boolean result)
  operand right-variable
  operand
 where-expression < And | Or | AndAlso | OrElse>, where-expression
u   group-by expression
 group by  into
group by , into
u   join-expression
 join in on <source>
 join in on  into <source>
u   order-by-expression
 order by  
 order by , 
u   select-expression
 Select
 Select new With {anonymous type declaration}
乍看之下，此表似乎相當複雜，其實她還蠻簡單的，以此表搭配下文的語法分類解說，應能讓讀者們快速的掌握VB.NET中的LINQ Expression語法。
 
Query-Expression
 
 LINQ Expression中至少會包含一個query-expression，表一中對query-expression的定義如下。

From In <|||> <source>
指的是欲在此query-expression中使用的別名，更明確說是一個變數，編譯器會將In後面的運算式所產生的結果放到此變數中，此處有一點必須特別指出，In後面的代表著來源，可以是一個IEnumerable(Of T)型別的物件，法則上也可以由另一個所取代，我們將此稱為Nested query-expression(巢狀Query)。Query-expression中可以包含Where語句、Group By語句、Join語句、Order By修飾句及Select語句，因應不同的語句，其內或允許query-exprssion的出現，下例是一個簡單的query-expression。<source><source>

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]

3333
2222
1111
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
USA
NY
此例僅列出一個Taipai，這就是Distinct函式的能力，相同值者僅取一代表值。
 
Select Many 功能
 
 截至目前為止的例子皆以一個query-expression作為開始，事實上LINQ Expression支援query-expression的串接，名為Select Many功能，見程式9。
 
 
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-277785/，如需轉載，請註明出處，否則將追究法律責任。

相關文章