slide1 n.
Download
Skip this Video
Download Presentation
LINQ 全面透析 黃忠成資訊工作室

Loading in 2 Seconds...

play fullscreen
1 / 80

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


  • 194 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


Download Now 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
slide1
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
slide3
什麼是LINQ
  • LINQ全名為
    • Language Integrated Query
      • Language指的是程式語言
      • Integrated指的是整合
      • Query指的是查詢
    • 因此,LINQ就是[整合至程式語言中的查詢敘述語法]
  • 那些程式語言支援LINQ
    • C# 2008
    • VB.NET 2008
    • Ruby
    • Phyton
slide4
為什麼我們需要LINQ
  • 你是否需要在陣列中尋找一元素?
  • 你是否需要在陣列中尋找多個符合條件的元素
  • 在LINQ前,你是怎麼做?
    • 迴圈與列舉一一比對是唯一方法
    • 更好的方法,使用Delegate.
  • 在LINQ後,你能怎麼做?
    • 使用SQL LIKE語法,像查詢SQL資料庫般查詢陣列
    • 像查詢SQL資料庫般,LINQ允許你對多個陣列進行JOIN並查詢
slide6
一個簡單的例子

傳統寫法

使用LINQ

slide7
LINQ的設計初衷
  • 提供一組常用的運算模組,例如搜尋,聯集(Join)搜尋,協助設計師完成日常的運算工作
  • 透過程式語言與編譯器的協助,提供新的程式語法,將使用這個運算模組的動作,變成程式語言的一部份.
slide9
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")

slide11
常用術語解釋
  • 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)
  • 類別函式
  • 類別靜態函式
where1
Where語句於執行時期
  • Where語句後的程式碼,會被編成一函式
  • 當我們列舉查詢式回傳集中元素時(以For Each,For),每個元素都會被傳入此函式比對,以傳回值判定是否取回該元素或是跳過進行下一個元素之比對.
  • LINQ To Objects的重要觀念,有Where條件時
    • 在開始列舉時,每一個元素都會被送往某一函式比對
    • 模式與你用迴圈列舉相同.
slide17
排序
  • 正向排序
  • 倒序
  • 多重排序
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()
slide21
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
slide24
常用語句
  • Take
  • Skip
  • Any
  • All
slide25
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就會結束,即使第二三個元素符合條件.
slide26
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..相同
slide31
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)

slide34
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

slide39
查詢語句的回傳值
  • 查詢語句的回傳值,多數是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的結合
slide41
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等等
slide44
漸進式查詢
  • 查詢式何時執行?
    • 當程式嘗試取出元素時
      • For Each等列舉式,First等函式
  • 實作漸進式查詢
    • 要點1 使用IEnumerable(Of T)為參數型別
    • 要點2 所有Query回傳值皆為IEnumerable(Of T)
    • 要點3 傳遞前不要進行列舉動作,否則查詢式會先執行,接下來的動作為疊加式查詢,對效能會有影響
linq to xml1
LINQ To XML的特色
  • 更直覺的XML建立語法
  • 與LINQ查詢語句結合的XML查詢式
  • 更方便的XML文件操作
slide47
建立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>

slide51
新增為子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>

slide52
<%=...%>語句
  • 透過<%=...%>將變數與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>

slide55
常用類別示意圖

XDocument

XNamespace

XCData

XElement

XAttribute

linq to xml3
LINQ To XML查詢式
  • 一個簡單的例子

<Customers>

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

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

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

</Customers>

take skip
Take,Skip
  • Take
  • Skip
grouping1
Grouping
  • Using Grouping
slide61
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與元素描述略為不同
slide67
<…>
  • <…>
    • 取得特定的子Element
    • 此法無法取得子Element的Element

正確

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

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

slide68
…<…>
  • …<…>取得所有符合的Element
    • 與<…>不同,此法將可取得所有符合條件的Element
1 rss
查詢實例1 - RSS
  • Load RSS
  • Find in RSS
linq to dataset1
LINQ To DataSet的設計初衷
  • 將LINQ語法套用到DataSet與DataTable
  • 為DataTable提供更強大的查詢功能
slide73
開始查詢
  • Field(Of T)的意義
    • 當值非DBNull,回傳值
    • 當值無法轉為指定型別,產生例外
    • 當值為DBNull,回傳Nothing
row version
存取Row Version
  • 使用RowVersion來查詢與取得資料
group1
Group
  • 運用Group
slide77
運用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,元素描述
  • 相同的語法規則