VB 2010 （65）查询表达式

查询表达式

分段	说明
Project	Select <expression>
Filter	Where <expression>,Distinct
Test	Any(<expression>), All(<expression>)
Join	<expression>  Join <expression> On <expression> Equals <expression>
Group	Group By <expression>, Into<expression>, <expression> Group Join <decision> On <expression> Equals<expression> Into<expression>
Aggregate	Count(<expression>), Sum(<expression>), MIn(<expression>), Max(<expression>), Avg(<expression>)
Partition	Skip[While]<expression>, Take[While]<expression>
Set	Union, Intersect, Except
Order	Order By <expression>, <expression>[Ascending|descending]

Imports System.Linq
Imports System.Data.SqlClient

Module Module1

    Sub Main()
        'testSimpeSelect()
        InsertTitle()
        UpdateTitle()

        DeleteTitle()


        Console.Read()
    End Sub

    Private Sub testSimpeSelect()
        Dim dc As New pubsDataContext
        Dim query1 = From p In dc.titles Select p
        For Each item In query1
            Console.WriteLine(item.title_id & ":" & item.title)
        Next
        Console.WriteLine("查询1-------------------------------------------")

        Dim query2 = From p In dc.titles
                     Where (p.title.StartsWith("S"))
                     Select p
        For Each item In query2
            Console.WriteLine(item.title_id & ":" & item.title)
        Next
        Console.WriteLine("查询2-------------------------------------------")

        Dim query3 = From p In dc.titles
                    Where p.title.StartsWith("S")
                    Where p.title.EndsWith("?")
        For Each item In query3
            Console.WriteLine(item.title_id & ":" & item.title)
        Next
        Console.WriteLine("查询3-------------------------------------------")

        Dim query4 = From t In dc.titles
                    Join s In dc.sales
                    On s.title_id Equals t.title_id
                    Join st In dc.stores
                    On st.stor_id Equals s.stor_id
                    Order By st.stor_id
                    Select st.stor_name, t.title, s.qty
        For Each item In query4
            Console.WriteLine("{0} sold{1} copies of '{2}'", item.stor_name, item.qty, item.title)
        Next
        Console.WriteLine("查询4-------------------------------------------")

        Dim query5 = From t In dc.titles
                    Join s In dc.sales
                    On s.title_id Equals t.title_id
                    Order By s.stores.state Ascending
                    Group s By Key = s.stores.state
                    Into Group
                    Select Key, Group
        For Each item In query5
            Console.WriteLine("Sales for {0}", item.Key)
            For Each s In item.Group
                Console.WriteLine("{0} - {1} copies", s.titles.title, s.qty)
            Next
        Next
        Console.WriteLine("查询5-------------------------------------------")


        Dim query6 = dc.GetAllAuthors
        For Each item In query6
            Console.WriteLine(item.au_fname & "  " & item.state)
        Next
        Console.WriteLine("查询6-------------------------------------------")

        Console.WriteLine("Press ENTER to exit")
    End Sub

    Private Sub InsertTitle()
        Dim dc As New pubsDataContext
        Console.WriteLine("Titles before insert:{0}", dc.titles.Count)
        Dim newTilte As New titles
        With newTilte
            .title_id = "NU1234"
            .title = "Some new title"
            .type = "test"
            .pubdate = New DateTime(2010, 1, 1)
            .notes = "Added via LINQ"
            .price = 50.0
        End With
        dc.titles.InsertOnSubmit(newTilte)

        Console.WriteLine("Titles after insert,but before sumit:{0}", dc.titles.Count)
        dc.SubmitChanges()
        Console.WriteLine("Titles after submit :{0}", dc.titles.Count)
    End Sub

    Private Sub UpdateTitle()
        Dim dc As New pubsDataContext
        Dim aTitle = (From t In dc.titles
                    Where t.title_id = "NU1234"
                    Select t).Single
        Console.WriteLine("Record befor update:{0}", aTitle.title)

        aTitle.title = "Update title"
        aTitle.price = 45.95
        dc.SubmitChanges()

        Console.WriteLine("Record after update:{0}", aTitle.title)
    End Sub

    Private Sub DeleteTitle()
        Dim dc As New pubsDataContext
        Dim theTitles = From t In dc.titles
                      Order By t.title_id Select t
        For Each t As titles In theTitles
            If t.title_id = "NU1234" Then
                dc.titles.DeleteOnSubmit(t)
            End If
        Next

        Console.WriteLine("Titles after delete,but before submit:{0}", dc.titles.Count)
        dc.SubmitChanges()
    End Sub
End Module