LINQ
Download
1 / 80

LINQ 全面透析 黃忠成資訊工作室 - PowerPoint PPT Presentation


  • 190 Views
  • Uploaded on

LINQ 全面透析 黃忠成資訊工作室. LINQ To Objects LINQ To XML LINQ To DataSet. LINQ To CLR. SQL Server Support O/R Mapping SQL Injection Prevent. LINQ. LINQ To SQL. Multi-Database Support O/R Mapping EDM SQL Injection Prevent. LINQ To Entities. VB.NET 2008 新語法. Partial Class

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' LINQ 全面透析 黃忠成資訊工作室' - kyrie


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
LINQ 全面透析 黃忠成資訊工作室

  • LINQ To Objects

  • LINQ To XML

  • LINQ To DataSet

LINQ To CLR

  • SQL Server Support

  • O/R Mapping

  • SQL Injection Prevent

LINQ

LINQ To SQL

  • Multi-Database Support

  • O/R Mapping

  • EDM

  • SQL Injection Prevent

LINQ To Entities


Vb net 2008
VB.NET 2008新語法

  • Partial Class

  • Partial Method

  • Anonymous Function Expression

  • Object Initialize


什麼是LINQ

  • LINQ全名為

    • Language Integrated Query

      • Language指的是程式語言

      • Integrated指的是整合

      • Query指的是查詢

    • 因此,LINQ就是[整合至程式語言中的查詢敘述語法]

  • 那些程式語言支援LINQ

    • C# 2008

    • VB.NET 2008

    • Ruby

    • Phyton


為什麼我們需要LINQ

  • 你是否需要在陣列中尋找一元素?

  • 你是否需要在陣列中尋找多個符合條件的元素

  • 在LINQ前,你是怎麼做?

    • 迴圈與列舉一一比對是唯一方法

    • 更好的方法,使用Delegate.

  • 在LINQ後,你能怎麼做?

    • 使用SQL LIKE語法,像查詢SQL資料庫般查詢陣列

    • 像查詢SQL資料庫般,LINQ允許你對多個陣列進行JOIN並查詢



一個簡單的例子

傳統寫法

使用LINQ


LINQ的設計初衷

  • 提供一組常用的運算模組,例如搜尋,聯集(Join)搜尋,協助設計師完成日常的運算工作

  • 透過程式語言與編譯器的協助,提供新的程式語法,將使用這個運算模組的動作,變成程式語言的一部份.


LINQ的架構


LINQ語法規則基礎

Alias

Where 語句

查詢條件

FROM語句

DataSource


Linq data source
LINQ的資料來源(Data Source)

  • 必須是實作IEnumerable(Of T)的物件

  • 陣列

    • Dim List As String() = {“code6421”, “tom”, “mary”}

  • List(Of T)

    • Dim Data = New List(Of String)()

  • 與.NET Framework結合應用

    • Dim List = From S1 In Directory.GetFiles("C:\Windows") Where

      S1.Contains("w")


常用術語解釋

  • Data Source,資料來源,元素集

    • 一個實作IEnumerable(Of T)的物件

  • 查詢回傳集

    • 由查詢式回傳的物件

  • 元素

    • 回傳集,元素集中的某一元素

  • 查詢式

    • LINQ 語句


Where
了解Where語句

  • Where語句後?

    • 在LINQ To Objects中,Where語句後是一段程式碼

      • Directory.GetFiles("C:\Windows") Where

        S1.Contains("w")

        S1.Contains(“w”)是一段回傳Boolean值的程式碼

    • 同理,下面的寫法也是允許的

  • AND

    • From s1 In List Where s1 Mod 5 = 0 And s1 > 20

  • OR

    • From s1 In List Where s1 Mod 5 = 0 Or s1 > 20


Contains
Contains函式的秘密

  • From s1 In Data Where s1.Name.Contains(likeName)

    • s1 = String

    • String擁有Contains函式

    • 所以,可以用s1.Contains來判別該元素是否擁有特定字元

    • 同理

    • From s1 In Data Where s1.Name.StartsWith(likeName)

    • 可以判定該元素是否以特定字元開始

    • Contains的簡化式

      • Dim result = From s1 In Data Where s1.Name Like "*V*"

  • Dim List As Integer() = {15, 20, 25, 30, 34}

    Dim Result = From s1 In List Where s1 Mod 5 = 0


Where 2
了解Where語句2

  • 定義函式

    • From s1 In List Where Filter(s1)

  • 類別函式

  • 類別靜態函式


In查詢式


Where1
Where語句於執行時期

  • Where語句後的程式碼,會被編成一函式

  • 當我們列舉查詢式回傳集中元素時(以For Each,For),每個元素都會被傳入此函式比對,以傳回值判定是否取回該元素或是跳過進行下一個元素之比對.

  • LINQ To Objects的重要觀念,有Where條件時

    • 在開始列舉時,每一個元素都會被送往某一函式比對

    • 模式與你用迴圈列舉相同.


排序

  • 正向排序

  • 倒序

  • 多重排序


Inner query
Inner Query

  • Query 中可包含另一Query

  • Query As Data Source


Select
了解Select語句

  • Default Select

    • From s1 In Data

  • Explicit Select

    • From s1 In Data Select s1

  • Select New

    • From s1 In Data Select Name = s1.Name + "," + s1.Age.ToString()



JOIN的模式

  • 預設為INNER JOIN,在JOIN時,左邊找到的,右邊也要找到,否則就不列出

  • GROUP JOIN

  • 以GROUP JOIN模擬LEFT JOIN

  • 以GROUP JOIN模擬FULL OUTER JOIN


Grouping
Grouping

  • Simple Group

  • [Group <Data Source> By <key variable> = <key from Data Source> Into <Alias Variable> = Group Select <Anonymous Type>


Group
另一個Group例子

  • Group Alias Variable

  • Multi-Key Grouping


常用語句

  • Take

  • Skip

  • Any

  • All


Take

  • Take

    • 由查詢回傳集中取出特定元素

  • TakeWhile

    • 由查詢回傳集中以特定條件比對,取出元素

From s1 In persons Take 2

From s1 In persons Take While s1.Age = 18 Or s1.Age = 28

  • 注意! Take While是比對指定條件,當有元素不符合時,Take While就會結束,換句話說,當第一個元素之age不等於18或28時,Take While就會結束,即使第二三個元素符合條件.


Skip

  • Skip

    • 跳過查詢回傳集中特定數量元素後開始取得元素

  • Skip While

    • 跳過查詢回傳集中符合特定條件之元素後開始取得元素

From s1 In persons Skip 1

From s1 In persons Skip While s1.Age = 18

  • 注意! Skip While是比對指定條件,當有元素不符合時,Ski While就會結束,換句話說,當第一個元素之age不等於18時,Skip While就會結束,列出所有元素,即使第二三個元素符合條件.


Distinct
Distinct

  • Distinct

    • 惕除查詢回傳集重複元素

Dim nums() = {1, 1, 3, 4, 5}

Dim Result = From s1 In nums Distinct

For Each item In Result

Console.WriteLine(item)

Next


Aggregate
Aggregate 語句

  • Aggregate 語法規則

  • Any的例子(判斷元素集中是否包含符合條件的元素)

  • Any後的語句與Where後語句相同,是一段程式碼

Aggregate <alias> In <datasource> <Where> <Where Expression) Into <expression>

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 18, "002"))

persons.Add(New Person("mary", 18, "002"))

Dim Result = Aggregate s1 In persons Into p1 = Any(s1.Age = 18)

Console.WriteLine(Result)


Aggregate where
Aggregate語句結合Where

  • Aggregate也可以與Where整合

  • Aggregate也可以與Join整合


Aggregate1
Aggregate敘述重要觀念

  • Aggregate通常傳回單值,例如Boolean,數值

  • Aggregate後的語法與From…語句幾乎相同

  • Aggregate可包含Where , Join , Group 等語句,與From..相同


All

  • All的例子(判斷元素集中是否全數符合條件)

  • All後的語句與Where後語句相同,是一段程式碼

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 18, "002"))

persons.Add(New Person("mary", 18, "002"))

Dim Result = Aggregate s1 In persons Into p1 = Any(s1.Age = 18)

Console.WriteLine(Result)


Average count
Average,Count

  • Average的例子(取平均值)

  • Count(取元素數)

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 28, "002"))

persons.Add(New Person("mary", 19, "002"))

Dim Result = Aggregate s1 In persons Into p1 = Average(s1.Age)

Console.WriteLine(Result)

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 28, "002"))

persons.Add(New Person("mary", 19, "002"))

Dim Result = Aggregate s1 In persons Into p1 = Count(s1.Age >= 19)

Console.WriteLine(Result)


Min max
Min,Max

  • Min(取最小值)

  • Min(取最大值)

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 28, "002"))

persons.Add(New Person("mary", 19, "002"))

Dim Result = Aggregate s1 In persons Into p1 = Max(s1.Age)

Console.WriteLine(Result)

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 28, "002"))

persons.Add(New Person("mary", 19, "002"))

Dim Result = Aggregate s1 In persons Into p1 = Min(s1.Age)

Console.WriteLine(Result)


Sum

  • Sum(加總)

  • Using Sum With Build-Type

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 28, "002"))

persons.Add(New Person("mary", 19, "002"))

Dim Result = Aggregate s1 In persons Into p1 = Sum(s1.Age)

Console.WriteLine(Result)

Dim nums() As Integer = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}

Dim Result = Aggregate s1 In nums Into p1 = Sum(s1)

Console.WriteLine(Result)


Group sum
Group結合Sum運算

  • Aggregate是傳回單值

  • 如需傳回元素集,可使用Group語句進行

  • 寫法與Aggregate後面敘述幾乎相同


First
First

  • First

    • 取得第一個元素

  • 如果沒有元素符合,First將會拋出例外

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 28, "002"))

persons.Add(New Person("mary", 19, "002"))

Dim Result = (From s1 In persons Where s1.Age >= 18).First()

Console.WriteLine(Result.Age)


Firstordefault
FirstOrDefault

  • FirstOrDefault

    • 當沒有元素時,回傳Nothing,但不引發例外

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 28, "002"))

persons.Add(New Person("mary", 19, "002"))

Dim Result = (From s1 In persons Where s1.Age >= 50).FirstOrDefault()

If Not Result Is Nothing Then

Console.WriteLine(Result.Age)

End If


Last lastordefault
Last,LastOrDefault

  • Last

    • 取得查詢集的最後一個元素

  • LastOrDefault

    • 同Last,但如果查詢集為空,那將回傳Nothing

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 28, "002"))

persons.Add(New Person("mary", 19, "002"))

Dim Result = (From s1 In persons Where s1.Age >= 18).Last()

Console.WriteLine(Result.Age)

Dim persons = New List(Of Person)

persons.Add(New Person("code6421", 18, "001"))

persons.Add(New Person("tom", 28, "002"))

persons.Add(New Person("mary", 19, "002"))

Dim Result = (From s1 In persons Where s1.Age >= 50).LastOrDefault()

If Not Result Is Nothing Then

Console.WriteLine(Result.Age)

End If


查詢語句的回傳值

  • 查詢語句的回傳值,多數是IEnumerable(Of T)型別

  • IEnumerable(Of T)定義了First函式

  • 一個關鍵例子

  • Array並不是IEnumerable(Of T),為何有First函式?

Dim nums() = {1, 2, 3, 4, 5}

Dim Result = nums.First()


Extension method
Extension Method

  • 什麼是Extension Method

  • Extension Method必須定義在Module中

  • 一個簡單的例子

  • LINQ Framework就是一群Extension Method的結合


LINQ與編譯器

  • 使用Reflector 看我們的程式.

  • Extension Method是LINQ Framework的底層架構


From linq expression to direct call extension method
From LINQ Expression To Direct Call Extension Method

  • Function Expression 與 LINQ Extension Method

  • Distinct

  • Sum

  • Average

  • Min

  • Max

  • SkipWhile,TakeWhile


Where and why linq expression vs direct call
Where And WhyLINQ Expression .VS. Direct Call

  • LINQ Expression的好處

    • 直覺,與SQL語法類似

    • 易讀

  • LINQ Expression的缺點

    • 敘述過長

    • 受限於語法規則,無法達到Direct Call的相同效果

  • 何時使用Direct Call

    • 對一元素集進行單一運算,例如Sum,Average等等

    • 當需要進階查詢時,例如Distinct,TakeWhile,SkipWhile等等


漸進式查詢

  • 查詢式何時執行?

    • 當程式嘗試取出元素時

      • For Each等列舉式,First等函式

  • 實作漸進式查詢

    • 要點1 使用IEnumerable(Of T)為參數型別

    • 要點2 所有Query回傳值皆為IEnumerable(Of T)

    • 要點3 傳遞前不要進行列舉動作,否則查詢式會先執行,接下來的動作為疊加式查詢,對效能會有影響



Linq to xml1
LINQ To XML的特色

  • 更直覺的XML建立語法

  • 與LINQ查詢語句結合的XML查詢式

  • 更方便的XML文件操作


建立XML文件

  • VB.NET 2008的新XML建立語法

Dim xmlDoc = _

<Customers>

<Customer Name="code6421" Age="18"/>

<Customer Name="tom" Age="28"/>

<Customer Name="david" Age="38"/>

</Customers>

xmlDoc.Save("C:\Test.xml")

Test.xml

<?xml version="1.0" encoding="utf-8"?>

<Customers>

<Customer Name="code6421" Age="18" />

<Customer Name="tom" Age="28" />

<Customer Name="david" Age="38" />

</Customers>


Xml node
新增XML Node

  • 新增Node至尾端

Dim xmlDoc = XDocument.Load("C:\Test.xml")

xmlDoc.<Customers>(0).Add(<Customer Name="Hung" Age="16"/>)

xmlDoc.Save("C:\Test2.xml")

<?xml version="1.0" encoding="utf-8"?>

<Customers>

<Customer Name="code6421" Age="18" />

<Customer Name="tom" Age="28" />

<Customer Name="david" Age="38" />

<Customer Name="Hung" Age="16" />

</Customers>


Xml node1
新增XML Node

  • 新增Node至最前

Dim xmlDoc = XDocument.Load("C:\Test.xml")

xmlDoc.<Customers>(0).AddFirst(<Customer Name="Hung" Age="16"/>)

xmlDoc.Save("C:\Test2.xml")

<?xml version="1.0" encoding="utf-8"?>

<Customers>

<Customer Name="Hung" Age="16" />

<Customer Name="code6421" Age="18" />

<Customer Name="tom" Age="28" />

<Customer Name="david" Age="38" />

</Customers>


Attribute node
新增Attribute至既定Node

  • Add Attribute

Dim xmlDoc = XDocument.Load("C:\Test.xml")

xmlDoc.<Customers>(0).<Customer>(0).Add(New XAttribute("Tag", "Test"))

xmlDoc.Save("C:\Test2.xml")

<?xml version="1.0" encoding="utf-8"?>

<Customers>

<Customer Name="code6421" Age="18" Tag="Test" />

<Customer Name="tom" Age="28" />

<Customer Name="david" Age="38" />

</Customers>


新增為子Node

  • 新增為子Node

Dim xmlDoc = XDocument.Load("C:\Test.xml")

Dim child = <Order ID="001"/>

xmlDoc.<Customers>(0).<Customer>(0).Add(child)

xmlDoc.Save("C:\Test2.xml")

<?xml version="1.0" encoding="utf-8"?>

<Customers>

<Customer Name="code6421" Age="18">

<Order ID="001" />

</Customer>

<Customer Name="tom" Age="28" />

<Customer Name="david" Age="38" />

</Customers>


<%=...%>語句

  • 透過<%=...%>將變數與XML文件結合

Dim xmlDoc = _

<Customers>

</Customers>

For Each item In data

xmlDoc.Add(<Customer Name=<%= item %>></Customer>)

Next

xmlDoc.Save("C:\Test3.xml")

<?xml version="1.0" encoding="utf-8"?>

<Customers>

<Customer Name="code6421"></Customer>

<Customer Name="tom"></Customer>

</Customers>


Linq to xml2
LINQ To XML的架構圖



常用類別示意圖

XDocument

XNamespace

XCData

XElement

XAttribute


Linq xml framework xml
運用LINQ XML Framework類別來建立XML文件


Linq to xml3
LINQ To XML查詢式

  • 一個簡單的例子

<Customers>

<Customer Name="code6421" Age="18"/>

<Customer Name="tom" Age="28"/>

<Customer Name="david" Age="38"/>

</Customers>


Order by
使用Order By

  • Order By


Take skip
Take,Skip

  • Take

  • Skip


Grouping1
Grouping

  • Using Grouping


Join

  • Use Join


Group join and sum
Group Join and Sum

  • Group Join And Sum


Group join and average
Group Join And Average

  • Group Join And Average


Min max count
Min,Max,Count

  • Min函式

  • Max函式

  • Count函式


Linq to xml aggregate
LINQ To XML 與Aggregate語句

  • 與LINQ To Objects相同,LINQ To XML也可以用Aggregate


Linq to xml linq to objects
LINQ To XML與LINQ To Objects

  • 同樣的查詢語句

  • Data Source與元素描述略為不同


<…>

  • <…>

    • 取得特定的子Element

    • 此法無法取得子Element的Element

正確

錯誤,Test不是Orders(Root)的子Element

正確寫法: From cust In xmlDoc.<Order>.<Test>


…<…>

  • …<…>取得所有符合的Element

    • 與<…>不同,此法將可取得所有符合條件的Element


1 rss
查詢實例1 - RSS

  • Load RSS

  • Find in RSS



Linq to dataset1
LINQ To DataSet的設計初衷

  • 將LINQ語法套用到DataSet與DataTable

  • 為DataTable提供更強大的查詢功能



開始查詢

  • Field(Of T)的意義

    • 當值非DBNull,回傳值

    • 當值無法轉為指定型別,產生例外

    • 當值為DBNull,回傳Nothing


具條件的查詢

  • 使用Where


Row version
存取Row Version

  • 使用RowVersion來查詢與取得資料


Group1
Group

  • 運用Group


運用Join

  • 運用Join


Skip take
Skip,Take

  • Using Skip

  • Using Take


Sum min max average
Sum,Min,Max,Average

  • Using Group Join With Sum

  • Using Min

  • Using Max

  • Using Average


Linq to dataset linq
LINQ To DataSet與LINQ

  • 不同的Data Source,元素描述

  • 相同的語法規則


ad