共用方式為

查詢運算式 (F#)

  • 發行項

查詢運算式可以讓您查詢資料來源並將資料以一種所需格式。 查詢運算式為 LINQ 提供支援 F#。

query { expression }

備註

查詢運算式是計算運算式的型別類似序列運算式。 如同您透過提供程式碼指定序列在序列運算式，您可以提供程式碼指定一組資料在查詢運算式。 在序列運算式中， yield 關鍵字可識別要傳回的資料為結果序列的一部分。 在查詢運算式中， select 關鍵字執行相同的函式。 除了 select 關鍵字以外，也支援 F# 非常類似 SQL SELECT 陳述式的部分的大量查詢運算子。 這個簡單的查詢運算式的範例，其程式碼一起連接到 Northwind OData 來源。

// Use the OData type provider to create types that can be used to access the Northwind database.
// Add References to FSharp.Data.TypeProviders and System.Data.Services.Client
open Microsoft.FSharp.Data.TypeProviders

type Northwind = ODataService<"http://services.odata.org/Northwind/Northwind.svc">
let db = Northwind.GetDataContext()

// A query expression.
let query1 = query { for customer in db.Customers do
                     select customer }

query1
|> Seq.iter (fun customer -> printfn "Company: %s Contact: %s" customer.CompanyName customer.ContactName)

在上述程式碼範例中，查詢運算式在大括號。 程式碼的意義在運算式為，則傳回每位客戶資料庫中的 Customers 資料表中的查詢結果。 查詢運算式會傳回實作 IQueryable<T> 和 IEnumerable<T>的型別，和，以便重複使用 順序模組 ，在這個範例所示。

每個計算運算式型別從產生器類別中建立。 查詢計算運算式產生器類別是 QueryBuilder。 如需詳細資訊，請參閱計算運算式 (F#)與Linq.QueryBuilder 類別 (F#)。

查詢運算子

查詢運算子會傳回的記錄可讓您指定查詢的詳細資料，例如將 Standard 或指定結果排序順序。 查詢來源必須支援查詢運算子。 如果您嘗試使用不支援的查詢運算子， NotSupportedException 會擲回。

可以轉譯為 SQL 只提供的查詢運算式允許。 例如，在中，當您使用 where 查詢運算子時，函式呼叫在運算式中不允許。

表 1 說明可用的查詢運算子。 此外，請參閱 Table2 之後，比較 SQL 查詢和對等的 F# 查詢運算式本主題。 某些查詢運算子不為陣列型別提供者支援。 特別是， OData 型別提供者在查詢運算子會限制它支援由於提供者的限制。 如需詳細資訊，請參閱 ODataService 型別提供者 (F#)。

此表會假設資料庫以下列形式:

範例資料庫圖表

學生課程資料庫圖表

程式碼也會顯示在下表中假設下列資料庫連接程式碼。 專案應該加入至 System.Data、System.Data.Linq 和 FSharp.Data.TypeProviders 組件的參考。 建立這個資料庫的程式碼是包含在這個主題的結尾。

open System
open Microsoft.FSharp.Data.TypeProviders
open System.Data.Linq.SqlClient
open System.Linq
open Microsoft.FSharp.Linq



type schema = SqlDataConnection<"Data Source=SERVER\INSTANCE;Initial Catalog=MyDatabase;Integrated Security=SSPI;">

let db = schema.GetDataContext()

// Needed for some query operator examples:
let data = [ 1; 5; 7; 11; 18; 21]

表 1。查詢運算子

運算子

描述

contains

判斷選取的項目是否包含指定的項目。

let isStudent11 =
    query {
        for student in db.Student do
        select student.Age.Value
        contains 11
    }

count

傳回選取的項目數目。

let countOfStudents =
    query {
        for student in db.Student do
        select student
        count
    }

last

目前已選取之項目中的最後一個項目。

let number = 
    query {
        for number in data do
        last
    }

lastOrDefault

如果找不到，會選取目前選取之項目中的最後一個項目或預設值項目。

let number =
    query {
        for number in data do
        where (number < 0)
        lastOrDefault
    }

exactlyOne

會選取目前選取之唯一，特定項目。 如果有多個項目存在，便會擲回例外狀況。

let student =
    query {
        for student in db.Student do
        where (student.StudentID = 1)
        select student
        exactlyOne
    }

exactlyOneOrDefault

如果該項目，會選取目前選取之項目中的單一，特定項目或預設值。

let student =
    query {
        for student in db.Student do
        where (student.StudentID = 1)
        select student
        exactlyOneOrDefault
    }

headOrDefault

如果序列中沒有包含任何項目，會選取目前選取之項目中的第一個項目的預設值。

let student =
    query {
        for student in db.Student do
        select student
        headOrDefault
    }

select

到目前為止專案選取的每一個項目。

query {
    for student in db.Student do
    select student
    }

where

選取以指定之述詞的項目。

query {
    for student in db.Student do
    where (student.StudentID > 4)
    select student
    }

minBy

為選取的每個項目目前已選取值並傳回最小的結果值。

let student =
    query {
        for student in db.Student do
        minBy student.StudentID
    }

maxBy

為選取的每個項目目前已選取值並傳回最大的結果值。

let student =
    query {
        for student in db.Student do
        maxBy student.StudentID
    }

groupBy

到目前為止群組依據指定的索引鍵選取器所選取的項目。

query {
    for student in db.Student do
    groupBy student.Age into g
    select (g.Key, g.Count())
    }

sortBy

到目前為止依遞增順序排序選取的項目選取項目的索引鍵。

query {
    for student in db.Student do
    sortBy student.Name
    select student
}

sortByDescending

到目前為止遞減排序選取的項目選取項目的索引鍵。

query {
    for student in db.Student do
    sortByDescending student.Name
    select student
}

thenBy

依遞增順序到目前為止執行給定的排序鍵來選取項目的後續排序作業。 這個運算子只能在 sortBy、 sortByDescending、 thenBy或 thenByDescending之後才使用。

query {
    for student in db.Student do
    where student.Age.HasValue
    sortBy student.Age.Value
    thenBy student.Name
    select student
}

thenByDescending

依遞減順序到目前為止執行給定的排序鍵來選取項目的後續排序作業。 這個運算子只能在 sortBy、 sortByDescending、 thenBy或 thenByDescending之後才使用。

query {
    for student in db.Student do
    where student.Age.HasValue
    sortBy student.Age.Value
    thenByDescending student.Name
    select student
}

groupValBy

為選取的每個項目目前已選取值並由特定索引鍵分組項目。

query {
    for student in db.Student do
    groupValBy student.Name student.Age into g
    select (g, g.Key, g.Count())
    }

join

與關聯的根據相符的索引鍵兩組選取的值。 請注意索引鍵的順序周圍 = 簽署聯結運算式是很重要的。 總共聯結，否則，如果 -> 之後的符號之後分割，縮排必須縮排至少就關鍵字 for。

query {
    for student in db.Student do 
    join selection in db.CourseSelection on
          (student.StudentID = selection.StudentID)
    select (student, selection)
}

groupJoin

與關聯的符合的兩組選取的值按鍵並分組結果。 請注意索引鍵的順序周圍 = 簽署聯結運算式是很重要的。

query {
    for student in db.Student do
    groupJoin courseSelection in db.CourseSelection on
               (student.StudentID = courseSelection.StudentID) into g
    for courseSelection in g do
    join course in db.Course on (courseSelection.CourseID = course.CourseID)
    select (student.Name, course.CourseName)
    }

leftOuterJoin

與關聯的符合的兩組選取的值按鍵並分組結果。 如果任何群組是空的會改用具有單一預設值的群組。 請注意索引鍵的順序周圍 = 簽署聯結運算式是很重要的。

query {
    for student in db.Student do
    leftOuterJoin selection in db.CourseSelection on
                   (student.StudentID = selection.StudentID) into result
    for selection in result.DefaultIfEmpty() do
    select (student, selection)
    }

sumByNullable

為選取的每個項目目前已選取之值並傳回這些值的總和。 如果有任何可為 null 並沒有值，則會忽略它。

query {
    for student in db.Student do
    sumByNullable student.Age
}

minByNullable

為選取的每個項目目前已選取之值並傳回這些值的最小值。 如果有任何可為 null 並沒有值，則會忽略它。

query {
    for student in db.Student do
    minByNullable student.Age
}

maxByNullable

為選取的每個項目目前已選取之值並傳回這些值的最大值。 如果有任何可為 null 並沒有值，則會忽略它。

query {
    for student in db.Student do
    maxByNullable student.Age
    }

averageByNullable

為選取的每個項目目前已選取之值並傳回平均值這些值。 如果有任何可為 null 並沒有值，則會忽略它。

query {
    for student in db.Student do
    averageByNullable (Nullable.float student.Age)
    }

averageBy

為選取的每個項目目前已選取值並傳回平均值這些值。

query {
    for student in db.Student do
    averageBy (float student.StudentID)
}

distinct

目前已選取的項目從選取的項目。

query {
    for student in db.Student do
    join selection in db.CourseSelection on
          (student.StudentID = selection.StudentID)
    distinct        
}

exists

判斷選取的任何元素到目前為止是否符合條件。

query {
    for student in db.Student do
    where (query { for courseSelection in db.CourseSelection do
                   exists (courseSelection.StudentID = student.StudentID) })
    select student
}

find

選取目前已符合指定之條件的第一個選取的項目。

query {
    for student in db.Student do
    find (student.Name = "Abercrombie, Kim")
}

all

判斷選取的所有項目到目前為止是否符合條件。

query {
    for student in db.Student do
    all (SqlMethods.Like(student.Name, "%,%"))
}

head

選取目前選取的第一個項目從選取的項目。

query {
    for student in db.Student do
    head
}

nth

選取目前選取的項目在選取中的指定索引。

query {
    for numbers in data do
    nth 3
}

skip

到目前為止略過選取的項目上執行指定的數字然後選取其餘項目。

query {
    for student in db.Student do
    skip 1
}

skipWhile

略過序列中的項目，直到指定的條件為 true 並選取其餘項目。

query {
    for number in data do
    skipWhile (number < 3)
    select student
    }

sumBy

為選取的每個項目目前已選取值並傳回這些值的總和。

query {
   for student in db.Student do
   sumBy student.StudentID
   }

take

目前已選取指定的連續項目數目從選取的項目。

query {
   for student in db.Student do
   select student
   take 2
   }

takeWhile

選取項目序列，直到指定的條件為 true，然後略過其餘項目。

query {
    for number in data do
    takeWhile (number < 10)
    }

sortByNullable

到目前為止依遞增順序排序選取的項目選取項目 (指定的索引鍵。

query {
    for student in db.Student do
    sortByNullable student.Age
    select student
}

sortByNullableDescending

到目前為止遞減排序選取的項目選取項目 (指定的索引鍵。

query {
    for student in db.Student do
    sortByNullableDescending student.Age
    select student
}

thenByNullable

依遞增順序到目前為止執行可為 null 的排序索引鍵指定所選取項目的後續排序作業。 這個運算子可以在 sortBy之後只使用 sortByDescending、 thenBy或 thenByDescending，也不可為 null 的變數。

query {
    for student in db.Student do
    sortBy student.Name
    thenByNullable student.Age
    select student
}

thenByNullableDescending

依遞減順序到目前為止執行可為 null 的排序索引鍵指定所選取項目的後續排序作業。 這個運算子可以在 sortBy之後只使用 sortByDescending、 thenBy或 thenByDescending，也不可為 null 的變數。

query {
    for student in db.Student do
    sortBy student.Name
    thenByNullableDescending student.Age
    select student
}

Transact-SQL 和 F# 查詢運算式比較

下表顯示 F# 中一些常見的 Transact-SQL 查詢及其對等用法。 程式碼在這個資料表中也假設相同設定型別的上一個資料表和相同初始程式碼提供者的資料庫。

表 2。Transact-SQL 和 F# 查詢運算式

Transact-SQL (不區分大小寫)

F# 查詢運算式 (區分大小寫)

選取所有欄位資料表。

SELECT * FROM Student
 
// All students.
query {
    for student in db.Student do
    select student
}

計數會記錄在資料表中。

SELECT COUNT(*) FROM Student
 
// Count of students.
query {
    for student in db.Student do        
    count
}

EXISTS

SELECT * FROM Student
WHERE EXISTS 
(SELECT * FROM CourseSelection
WHERE CourseSelection.StudentID = Student.StudentID)
 
// Find students who have signed up at least one course.
query {
    for student in db.Student do
    where (query { for courseSelection in db.CourseSelection do
                   exists (courseSelection.StudentID = student.StudentID) })
    select student
}

群組

SELECT Student.Age, COUNT(*) FROM Student
GROUP BY Student.Age
 
// Group by age and count.
query {
    for n in db.Student do
    groupBy n.Age into g
    select (g.Key, g.Count())
}
// OR
query {
    for n in db.Student do
    groupValBy n.Age n.Age into g
    select (g.Key, g.Count())
}

群組的條件。

SELECT Student.Age, COUNT(*) 
FROM Student
GROUP BY Student.Age
HAVING student.Age > 10
 
// Group students by age where age > 10.
query {
    for student in db.Student do
    groupBy student.Age into g
    where (g.Key.HasValue && g.Key.Value > 10)
    select (g.Key, g.Count())
}

群組以計數情況。

SELECT Student.Age, COUNT(*)
FROM Student
GROUP BY Student.Age
HAVING COUNT(*) > 1
 
// Group students by age and count number of students
// at each age with more than 1 student.
query {
    for student in db.Student do
    groupBy student.Age into group
    where (group.Count() > 1)
    select (group.Key, group.Count())
}

群組，計數、加總。

SELECT Student.Age, COUNT(*), SUM(Student.Age) as total
FROM Student
GROUP BY Student.Age
 
// Group students by age and sum ages.
query {
    for student in db.Student do
    groupBy student.Age into g        
    let total = query { for student in g do
                        sumByNullable student.Age }
    select (g.Key, g.Count(), total)
}

群組，計數和預先定義的計數。

SELECT Student.Age, COUNT(*) as myCount
FROM Student
GROUP BY Student.Age
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
 
// Group students by age, count number of students
// at each age, and display all with count > 1
// in descending order of count.
query {
    for student in db.Student do
    groupBy student.Age into g
    where (g.Count() > 1)        
    sortByDescending (g.Count())
    select (g.Key, g.Count())
}

IN 一組指定值

SELECT *
FROM Student
WHERE Student.StudentID IN (1, 2, 5, 10)
 
// Select students where studentID is one of a given list.
let idQuery = query { for id in [1; 2; 5; 10] do
                      select id }
query { 
    for student in db.Student do
    where (idQuery.Contains(student.StudentID))
    select student
}

LIKE 和 TOP。

-- '_e%' matches strings where the second character is 'e'
SELECT TOP 2 * FROM Student
WHERE Student.Name LIKE '_e%'
 
// Look for students with Name match _e% pattern and take first two.
query {
    for student in db.Student do
    where (SqlMethods.Like( student.Name, "_e%") )
    select student
    take 2   
    }

使用模式比對集合中的LIKE 。

-- '[abc]%' matches strings where the first character is
-- 'a', 'b', 'c', 'A', 'B', or 'C'
SELECT * FROM Student
WHERE Student.Name LIKE '[abc]%'
 
open System.Data.Linq.SqlClient;

printfn "\nLook for students with Name matching [abc]%% pattern."
query {
    for student in db.Student do
    where (SqlMethods.Like( student.Name, "[abc]%") )
    select student  
    }

與 集合排除樣式的LIKE 。

-- '[^abc]%' matches strings where the first character is
-- not 'a', 'b', 'c', 'A', 'B', or 'C'
SELECT * FROM Student
WHERE Student.Name LIKE '[^abc]%'
 
// Look for students with name matching [^abc]%% pattern.
query {
    for student in db.Student do
    where (SqlMethods.Like( student.Name, "[^abc]%") )
    select student  
    }

在一個欄位，則為LIKE ，但選取不同的欄位。

SELECT StudentID AS ID FROM Student
WHERE Student.Name LIKE '[^abc]%'
 
open System.Data.Linq.SqlClient;

printfn "\nLook for students with name matching [^abc]%% pattern and select ID."
query {
    for n in db.Student do
    where (SqlMethods.Like( n.Name, "[^abc]%") )
    select n.StudentID    
    }
|> Seq.iter (fun id -> printfn "%d" id)

LIKE，與子字串搜尋。

SELECT * FROM Student
WHERE Student.Name like '%A%'
 
// Using Contains as a query filter.
query {
    for student in db.Student do
    where (student.Name.Contains("a"))
    select student
}

具有兩個資料表的 JOIN 。

SELECT * FROM Student
JOIN CourseSelection 
ON Student.StudentID = CourseSelection.StudentID
 
// Join Student and CourseSelection tables.
query {
    for student in db.Student do 
    join selection in db.CourseSelection on
          (student.StudentID = selection.StudentID)
    select (student, selection)
}

具有兩個資料表的LEFT JOIN 。

SELECT * FROM 
Student LEFT JOIN CourseSelection 
ON Student.StudentID = CourseSelection.StudentID
 
//Left Join Student and CourseSelection tables.
query {
    for student in db.Student do
    leftOuterJoin selection in db.CourseSelection on
                   (student.StudentID = selection.StudentID) into result
    for selection in result.DefaultIfEmpty() do
    select (student, selection)
    }

JOIN 和 COUNT

SELECT COUNT(*) FROM 
Student JOIN CourseSelection 
ON Student.StudentID = CourseSelection.StudentID
 
// Join with count.
query {
    for n in db.Student do 
    join e in db.CourseSelection on
          (n.StudentID = e.StudentID)
    count        
}

DISTINCT

SELECT DISTINCT StudentID FROM CourseSelection
 
// Join with distinct.
query {
    for student in db.Student do
    join selection in db.CourseSelection on
          (student.StudentID = selection.StudentID)
    distinct        
}

不同的計數。

SELECT DISTINCT COUNT(StudentID) FROM CourseSelection
 
// Join with distinct and count.
query {
    for n in db.Student do 
    join e in db.CourseSelection on
          n.StudentID = e.StudentID)
    distinct
    count       
}

BETWEEN

SELECT * FROM Student
WHERE Student.Age BETWEEN 10 AND 15
 
// Selecting students with ages between 10 and 15.
query {
    for student in db.Student do
    where (student.Age ?>= 10 && student.Age ?< 15)
    select student
}

OR

SELECT * FROM Student
WHERE Student.Age =11 OR Student.Age = 12
 
// Selecting students with age that's either 11 or 12.
query {
    for student in db.Student do
    where (student.Age.Value = 11 || student.Age.Value = 12)
    select student
}

與排序的OR

SELECT * FROM Student
WHERE Student.Age =12 OR Student.Age = 13
ORDER BY Student.Age DESC
 
// Selecting students in a certain age range and sorting.
query {
    for n in db.Student do
    where (n.Age.Value = 12 || n.Age.Value = 13)
    sortByNullableDescending n.Age
    select n
}

TOP、 OR和順序。

SELECT TOP 2 student.Name FROM Student
WHERE Student.Age = 11 OR Student.Age = 12
ORDER BY Student.Name DESC
 
// Selecting students with certain ages,
// taking account of the possibility of nulls.
query {
    for student in db.Student do
    where ((student.Age.HasValue && student.Age.Value = 11) ||
           (student.Age.HasValue && student.Age.Value = 12))
    sortByDescending student.Name
    select student.Name
    take 2
    }

兩個查詢UNION 。

SELECT * FROM Student
UNION
SELECT * FROM lastStudent
 
// Union of two queries.
module Queries =
    let query1 = query {
            for n in db.Student do
            select (n.Name, n.Age)
        }

    let query2 = query {
            for n in db.LastStudent do
            select (n.Name, n.Age)
            }

    query2.Union (query1)

兩個查詢的交集。

SELECT * FROM Student
INTERSECT
SELECT * FROM LastStudent
 
// Intersect of two queries.
module Queries2 =
    let query1 = query {
           for n in db.Student do
           select (n.Name, n.Age)
        }

    let query2 = query {
            for n in db.LastStudent do
            select (n.Name, n.Age)
            }

    query1.Intersect(query2)

CASE 條件。

SELECT student.StudentID, 
    CASE Student.Age
        WHEN -1 THEN 100
        ELSE Student.Age
        END,
        Student.Age
from Student
 
// Using if statement to alter results for special value.
query {
    for student in db.Student do
    select (if student.Age.HasValue && student.Age.Value = -1 then
               (student.StudentID, System.Nullable<int>(100), student.Age)
            else (student.StudentID, student.Age, student.Age))
    }

多個案例。

SELECT Student.StudentID, 
    CASE Student.Age
        WHEN -1 THEN 100
        WHEN 0 THEN 1000
        ELSE Student.Age
    END,
    Student.Age
FROM Student
 
// Using if statement to alter results for special values.
query {
    for student in db.Student do
    select (if student.Age.HasValue && student.Age.Value = -1 then
               (student.StudentID, System.Nullable<int>(100), student.Age)
            elif student.Age.HasValue && student.Age.Value = 0 then
               (student.StudentID, System.Nullable<int>(1000), student.Age)
            else (student.StudentID, student.Age, student.Age))
    }

多個資料表。

SELECT * FROM Student, Course
 
// Multiple table select.
query {
        for student in db.Student do
        for course in db.Course do
        select (student, course)
}

多重聯結。

SELECT Student.Name, Course.CourseName
FROM Student
JOIN CourseSelection
ON CourseSelection.StudentID = Student.StudentID
JOIN Course
ON Course.CourseID = CourseSelection.CourseID
 
 // Multiple joins.
query {
    for student in db.Student do
    join courseSelection in db.CourseSelection on
        (student.StudentID = courseSelection.StudentID)
    join course in db.Course on
          (courseSelection.CourseID = course.CourseID)
    select (student.Name, course.CourseName)
    }

多左外部聯結。

SELECT Student.Name, Course.CourseName
FROM Student
LEFT OUTER JOIN CourseSelection
ON CourseSelection.StudentID = Student.StudentID
LEFT OUTER JOIN Course
ON Course.CourseID = CourseSelection.CourseID
 
// Using leftOuterJoin with multiple joins.
query {
    for student in db.Student do
    leftOuterJoin courseSelection in db.CourseSelection on
        (student.StudentID = courseSelection.StudentID) into g1
    for courseSelection in g1.DefaultIfEmpty() do
    leftOuterJoin course in db.Course on
                   (courseSelection.CourseID = course.CourseID) into g2
    for course in g2.DefaultIfEmpty() do
    select (student.Name, course.CourseName)
    }

下列程式碼可用來建立這些範例的範例資料庫。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [master];
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'MyDatabase')
                DROP DATABASE MyDatabase;
GO

-- Create the MyDatabase database.
CREATE DATABASE MyDatabase COLLATE SQL_Latin1_General_CP1_CI_AS;
GO

-- Specify a simple recovery model 
-- to keep the log growth to a minimum.
ALTER DATABASE MyDatabase
                SET RECOVERY SIMPLE;
GO

USE MyDatabase;
GO

CREATE TABLE [dbo].[Course] (
    [CourseID]   INT           NOT NULL,
    [CourseName] NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([CourseID] ASC)
);

CREATE TABLE [dbo].[Student] (
    [StudentID] INT           NOT NULL,
    [Name]      NVARCHAR (50) NOT NULL,
    [Age]       INT           NULL,
    PRIMARY KEY CLUSTERED ([StudentID] ASC)
);

CREATE TABLE [dbo].[CourseSelection] (
    [ID]        INT NOT NULL,
    [StudentID] INT NOT NULL,
    [CourseID]  INT NOT NULL,
    PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_CourseSelection_ToTable] FOREIGN KEY ([StudentID]) REFERENCES [dbo].[Student] ([StudentID]) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT [FK_CourseSelection_Course_1] FOREIGN KEY ([CourseID]) REFERENCES [dbo].[Course] ([CourseID]) ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE [dbo].[LastStudent] (
    [StudentID] INT           NOT NULL,
    [Name]      NVARCHAR (50) NOT NULL,
    [Age]       INT           NULL,
    PRIMARY KEY CLUSTERED ([StudentID] ASC)
);

-- Insert data into the tables.
USE MyDatabase
INSERT INTO Course (CourseID, CourseName)
VALUES(1, 'Algebra I');
INSERT INTO Course (CourseID, CourseName)
VALUES(2, 'Trigonometry');
INSERT INTO Course (CourseID, CourseName)
VALUES(3, 'Algebra II');
INSERT INTO Course (CourseID, CourseName)
VALUES(4, 'History');
INSERT INTO Course (CourseID, CourseName)
VALUES(5, 'English');
INSERT INTO Course (CourseID, CourseName)
VALUES(6, 'French');
INSERT INTO Course (CourseID, CourseName)
VALUES(7, 'Chinese');

INSERT INTO Student (StudentID, Name, Age)
VALUES(1, 'Abercrombie, Kim', 10);
INSERT INTO Student (StudentID, Name, Age)
VALUES(2, 'Abolrous, Hazen', 14);
INSERT INTO Student (StudentID, Name, Age)
VALUES(3, 'Hance, Jim', 12);
INSERT INTO Student (StudentID, Name, Age)
VALUES(4, 'Adams, Terry', 12);
INSERT INTO Student (StudentID, Name, Age)
VALUES(5, 'Hansen, Claus', 11);
INSERT INTO Student (StudentID, Name, Age)
VALUES(6, 'Penor, Lori', 13);
INSERT INTO Student (StudentID, Name, Age)
VALUES(7, 'Perham, Tom', 12);
INSERT INTO Student (StudentID, Name, Age)
VALUES(8, 'Peng, Yun-Feng', NULL);

INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(1, 1, 2);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(2, 1, 3);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(3, 1, 5);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(4, 2, 2);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(5, 2, 5);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(6, 2, 6);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(7, 2, 3);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(8, 3, 2);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(9, 3, 1);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(10, 4, 2);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(11, 4, 5);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(12, 4, 2);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(13, 5, 3);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(14, 5, 2);
INSERT INTO CourseSelection (ID, StudentID, CourseID)
VALUES(15, 7, 3);

下列程式碼包含出現在本主題中的範例程式碼。

#if INTERACTIVE
#r "FSharp.Data.TypeProviders.dll"
#r "System.Data.dll"
#r "System.Data.Linq.dll"
#endif
open System
open Microsoft.FSharp.Data.TypeProviders
open System.Data.Linq.SqlClient
open System.Linq

[<Generate>]
type schema = SqlDataConnection<"Data Source=SERVER\INSTANCE;Initial Catalog=MyDatabase;Integrated Security=SSPI;">

let db = schema.GetDataContext()

let student = db.Student

let data = [1; 5; 7; 11; 18; 21]

open System
type Nullable<'T when 'T : ( new : unit -> 'T) and 'T : struct and 'T :> ValueType > with
    member this.Print() =
        if (this.HasValue) then this.Value.ToString()
        else "NULL"

printfn "\ncontains query operator"
query {
    for student in db.Student do
    select student.Age.Value
    contains 11
    }
|> printfn "Is at least one student age 11? %b" 

printfn "\ncount query operator"
query {
    for student in db.Student do
    select student
    count
    }
|> printfn "Number of students: %d" 


printfn "\nlast query operator." 
let num =
    query {
        for number in data do
        sortBy number
        last
        }
printfn "Last number: %d" num


open Microsoft.FSharp.Linq

printfn "\nlastOrDefault query operator." 
query {
        for number in data do
        sortBy number
        lastOrDefault
        }
|> printfn "lastOrDefault: %d"

printfn "\nexactlyOne query operator."
let student2 =
    query {
        for student in db.Student do
        where (student.StudentID = 1)
        select student
        exactlyOne
        }
printfn "Student with StudentID = 1 is %s" student2.Name

printfn "\nexactlyOneOrDefault query operator."
let student3 =
    query {
        for student in db.Student do
        where (student.StudentID = 1)
        select student
        exactlyOneOrDefault
        }
printfn "Student with StudentID = 1 is %s" student3.Name

printfn "\nheadOrDefault query operator."
let student4 =
    query {
        for student in db.Student do
        select student
        headOrDefault
        }
printfn "head student is %s" student4.Name

printfn "\nselect query operator."
query {
    for student in db.Student do
    select student
    }
|> Seq.iter (fun student -> printfn "StudentID, Name: %d %s" student.StudentID student.Name)

printfn "\nwhere query operator."
query {
    for student in db.Student do
    where (student.StudentID > 4)
    select student
    }
|> Seq.iter (fun student -> printfn "StudentID, Name: %d %s" student.StudentID student.Name)

printfn "\nminBy query operator."
let student5 =
    query {
        for student in db.Student do
        minBy student.StudentID
    }

printfn "\nmaxBy query operator."
let student6 =
    query {
        for student in db.Student do
        maxBy student.StudentID
    }

printfn "\ngroupBy query operator."
query {
    for student in db.Student do
    groupBy student.Age into g
    select (g.Key, g.Count())
    }
|> Seq.iter (fun (age, count) -> printfn "Age: %s Count at that age: %d" (age.Print()) count)

printfn "\nsortBy query operator."
query {
    for student in db.Student do
    sortBy student.Name
    select student
}
|> Seq.iter (fun student -> printfn "StudentID, Name: %d %s" student.StudentID student.Name)

printfn "\nsortByDescending query operator."
query {
    for student in db.Student do
    sortByDescending student.Name
    select student
}
|> Seq.iter (fun student -> printfn "StudentID, Name: %d %s" student.StudentID student.Name)

printfn "\nthenBy query operator."
query {
    for student in db.Student do
    where student.Age.HasValue
    sortBy student.Age.Value
    thenBy student.Name
    select student
}
|> Seq.iter (fun student -> printfn "StudentID, Name: %d %s" student.Age.Value student.Name)

printfn "\nthenByDescending query operator."
query {
    for student in db.Student do
    where student.Age.HasValue
    sortBy student.Age.Value
    thenByDescending student.Name
    select student
}
|> Seq.iter (fun student -> printfn "StudentID, Name: %d %s" student.Age.Value student.Name)

printfn "\ngroupValBy query operator."
query {
    for student in db.Student do
    groupValBy student.Name student.Age into g
    select (g, g.Key, g.Count())
    }
|> Seq.iter (fun (group, age, count) ->
    printfn "Age: %s Count at that age: %d" (age.Print()) count
    group |> Seq.iter (fun name -> printfn "Name: %s" name))

printfn "\n sumByNullable query operator"
query {
    for student in db.Student do
    sumByNullable student.Age
    }
|> (fun sum -> printfn "Sum of ages: %s" (sum.Print()))

printfn "\n minByNullable"
query {
    for student in db.Student do
    minByNullable student.Age
    }
|> (fun age -> printfn "Minimum age: %s" (age.Print()))

printfn "\n maxByNullable"
query {
    for student in db.Student do
    maxByNullable student.Age
    }
|> (fun age -> printfn "Maximum age: %s" (age.Print()))

printfn "\n averageBy"
query {
    for student in db.Student do
    averageBy (float student.StudentID)
    }
|> printfn "Average student ID: %f"

printfn "\n averageByNullable"
query {
    for student in db.Student do
    averageByNullable (Nullable.float student.Age)
    }
|> (fun avg -> printfn "Average age: %s" (avg.Print()))

printfn "\n find query operator"
query {
    for student in db.Student do
    find (student.Name = "Abercrombie, Kim")
}
|> (fun student -> printfn "Found a match with StudentID = %d" student.StudentID)

printfn "\n all query operator"
query {
    for student in db.Student do
    all (SqlMethods.Like(student.Name, "%,%"))
}
|> printfn "Do all students have a comma in the name? %b"

printfn "\n head query operator"
query {
    for student in db.Student do
    head
    }
|> (fun student -> printfn "Found the head student with StudentID = %d" student.StudentID)

printfn "\n nth query operator"
query {
    for numbers in data do
    nth 3
    }
|> printfn "Third number is %d"

printfn "\n skip query operator"
query {
    for student in db.Student do
    skip 1
    }
|> Seq.iter (fun student -> printfn "StudentID = %d" student.StudentID)

printfn "\n skipWhile query operator"
query {
    for number in data do
    skipWhile (number < 3)
    select number
    }
|> Seq.iter (fun number -> printfn "Number = %d" number)


printfn "\n sumBy query operator"
query {
   for student in db.Student do
   sumBy student.StudentID
   }
|> printfn "Sum of student IDs: %d" 

printfn "\n take query operator"
query {
   for student in db.Student do
   select student
   take 2
   }
|> Seq.iter (fun student -> printfn "StudentID = %d" student.StudentID)

printfn "\n takeWhile query operator"
query {
    for number in data do
    takeWhile (number < 10)
    }
|> Seq.iter (fun number -> printfn "Number = %d" number)

printfn "\n sortByNullable query operator"
query {
    for student in db.Student do
    sortByNullable student.Age
    select student
}
|> Seq.iter (fun student ->
    printfn "StudentID, Name, Age: %d %s %s" student.StudentID student.Name (student.Age.Print()))

printfn "\n sortByNullableDescending query operator"
query {
    for student in db.Student do
    sortByNullableDescending student.Age
    select student
}
|> Seq.iter (fun student ->
    printfn "StudentID, Name, Age: %d %s %s" student.StudentID student.Name (student.Age.Print()))

printfn "\n thenByNullable query operator"
query {
    for student in db.Student do
    sortBy student.Name
    thenByNullable student.Age
    select student
}
|> Seq.iter (fun student ->
    printfn "StudentID, Name, Age: %d %s %s" student.StudentID student.Name (student.Age.Print()))

printfn "\n thenByNullableDescending query operator"
query {
    for student in db.Student do
    sortBy student.Name
    thenByNullableDescending student.Age
    select student
}
|> Seq.iter (fun student ->
    printfn "StudentID, Name, Age: %d %s %s" student.StudentID student.Name (student.Age.Print()))

printfn "All students: "
query {
        for student in db.Student do
        select student
    }
    |> Seq.iter (fun student -> printfn "%s %d %s" student.Name student.StudentID (student.Age.Print()))

printfn "\nCount of students: "
query {
        for student in db.Student do        
        count
    }
|>  (fun count -> printfn "Student count: %d" count)

printfn "\nExists."
query {
        for student in db.Student do
        where (query { for courseSelection in db.CourseSelection do
                       exists (courseSelection.StudentID = student.StudentID) })
        select student }
|> Seq.iter (fun student -> printfn "%A" student.Name)

printfn "\n Group by age and count"
query {
        for n in db.Student do
        groupBy n.Age into g
        select (g.Key, g.Count())
}
|> Seq.iter (fun (age, count) -> printfn "%s %d" (age.Print()) count)

printfn "\n Group value by age."
query {
        for n in db.Student do
        groupValBy n.Age n.Age into g
        select (g.Key, g.Count())
    }
|> Seq.iter (fun (age, count) -> printfn "%s %d" (age.Print()) count)


printfn "\nGroup students by age where age > 10."
query {
        for student in db.Student do
        groupBy student.Age into g
       where (g.Key.HasValue && g.Key.Value > 10)
        select (g, g.Key)
}
|> Seq.iter (fun (students, age) ->
    printfn "Age: %s" (age.Value.ToString())
    students
    |> Seq.iter (fun student -> printfn "%s" student.Name))

printfn "\nGroup students by age and print counts of number of students at each age with more than 1 student."
query {
        for student in db.Student do
        groupBy student.Age into group
        where (group.Count() > 1)
        select (group.Key, group.Count())
}
|> Seq.iter (fun (age, ageCount) ->
     printfn "Age: %s Count: %d" (age.Print()) ageCount)

printfn "\nGroup students by age and sum ages."
query {
        for student in db.Student do
        groupBy student.Age into g        
        let total = query { for student in g do sumByNullable student.Age }
        select (g.Key, g.Count(), total)
}
|> Seq.iter (fun (age, count, total) ->
    printfn "Age: %d" (age.GetValueOrDefault())
    printfn "Count: %d" count
    printfn "Total years: %s" (total.ToString()))

printfn "\nGroup students by age and count number of students at each age, and display all with count > 1 in descending order of count."
query {
        for student in db.Student do
        groupBy student.Age into g
        where (g.Count() > 1)        
        sortByDescending (g.Count())
        select (g.Key, g.Count())
}
|> Seq.iter (fun (age, myCount) ->
    printfn "Age: %s" (age.Print())
    printfn "Count: %d" myCount)

printfn "\n Select students from a set of IDs"
let idList = [1; 2; 5; 10]
let idQuery = query { for id in idList do
                      select id }
query {
        for student in db.Student do
        where (idQuery.Contains(student.StudentID))
        select student
        }
|> Seq.iter (fun student ->
    printfn "Name: %s" student.Name)

printfn "\nLook for students with Name match _e%% pattern and take first two."
query {
    for student in db.Student do
    where (SqlMethods.Like( student.Name, "_e%") )
    select student
    take 2   
    }
|> Seq.iter (fun student -> printfn "%s" student.Name)

printfn "\nLook for students with Name matching [abc]%% pattern."
query {
    for student in db.Student do
    where (SqlMethods.Like( student.Name, "[abc]%") )
    select student  
    }
|> Seq.iter (fun student -> printfn "%s" student.Name)

printfn "\nLook for students with name matching [^abc]%% pattern."
query {
    for student in db.Student do
    where (SqlMethods.Like( student.Name, "[^abc]%") )
    select student  
    }
|> Seq.iter (fun student -> printfn "%s" student.Name)

printfn "\nLook for students with name matching [^abc]%% pattern and select ID."
query {
    for n in db.Student do
    where (SqlMethods.Like( n.Name, "[^abc]%") )
    select n.StudentID    
    }
|> Seq.iter (fun id -> printfn "%d" id)

printfn "\n Using Contains as a query filter."
query {
        for student in db.Student do
        where (student.Name.Contains("a"))
        select student
    }
|> Seq.iter (fun student -> printfn "%s" student.Name)

printfn "\nSearching for names from a list."
let names = [|"a";"b";"c"|]
query {
    for student in db.Student do
    if names.Contains (student.Name) then select student }
|> Seq.iter (fun student -> printfn "%s" student.Name)

printfn "\nJoin Student and CourseSelection tables."
query {
        for student in db.Student do 
        join (for selection in db.CourseSelection ->
              student.StudentID = selection.StudentID)
        select (student, selection)
    }
|> Seq.iter (fun (student, selection) -> printfn "%d %s %d" student.StudentID student.Name selection.CourseID)


printfn "\nLeft Join Student and CourseSelection tables."
query {
    for student in db.Student do
    leftOuterJoin (for selection in db.CourseSelection ->
                   student.StudentID = selection.StudentID) into result
    for selection in result.DefaultIfEmpty() do
    select (student, selection)
    }
|> Seq.iter (fun (student, selection) ->
    let selectionID, studentID, courseID =
        match selection with
        | null -> "NULL", "NULL", "NULL"
        | sel -> (sel.ID.ToString(), sel.StudentID.ToString(), sel.CourseID.ToString())
    printfn "%d %s %d %s %s %s" student.StudentID student.Name (student.Age.GetValueOrDefault()) selectionID studentID courseID)


printfn "\nJoin with count"
query {
        for n in db.Student do 
        join (for e in db.CourseSelection -> n.StudentID = e.StudentID)
        count        
    }
|>  printfn "%d"

printfn "\n Join with distinct."
query {
        for student in db.Student do 
        join (for selection in db.CourseSelection ->
              student.StudentID = selection.StudentID)
        distinct        
    }
|> Seq.iter (fun (student, selection) -> printfn "%s %d" student.Name selection.CourseID)

printfn "\n Join with distinct and count."
query {
        for n in db.Student do 
        join (for e in db.CourseSelection -> n.StudentID = e.StudentID)
        distinct
        count       
    }
|> printfn "%d"


printfn "\n Selecting students with age between 10 and 15."
query {
        for student in db.Student do
        where (student.Age.Value >= 10 && student.Age.Value < 15)
        select student
    }
|> Seq.iter (fun student -> printfn "%s" student.Name)

printfn "\n Selecting students with age either 11 or 12."
query {
        for student in db.Student do
        where (student.Age.Value = 11 || student.Age.Value = 12)
        select student
    }
|> Seq.iter (fun student -> printfn "%s" student.Name)

printfn "\n Selecting students in a certain age range and sorting."
query {
        for n in db.Student do
        where (n.Age.Value = 12 || n.Age.Value = 13)
        sortByNullableDescending n.Age
        select n
    }
|> Seq.iter (fun student -> printfn "%s %s" student.Name (student.Age.Print()))

printfn "\n Selecting students with certain ages, taking account of possibility of nulls."
query {
        for student in db.Student do
        where ((student.Age.HasValue && student.Age.Value = 11) ||
               (student.Age.HasValue && student.Age.Value = 12))
        sortByDescending student.Name 
        select student.Name
        take 2
    }
|> Seq.iter (fun name -> printfn "%s" name)

printfn "\n Union of two queries."
module Queries =
    let query1 = query {
            for n in db.Student do
            select (n.Name, n.Age)
        }

    let query2 = query {
            for n in db.LastStudent do
            select (n.Name, n.Age)
            }

    query2.Union (query1)
    |> Seq.iter (fun (name, age) -> printfn "%s %s" name (age.Print()))

printfn "\n Intersect of two queries."
module Queries2 =
    let query1 = query {
           for n in db.Student do
           select (n.Name, n.Age)
        }

    let query2 = query {
            for n in db.LastStudent do
            select (n.Name, n.Age)
            }

    query1.Intersect(query2)
    |> Seq.iter (fun (name, age) -> printfn "%s %s" name (age.Print()))

printfn "\n Using if statement to alter results for special value."
query {
        for student in db.Student do
        select (if student.Age.HasValue && student.Age.Value = -1 then
                   (student.StudentID, System.Nullable<int>(100), student.Age)
                else (student.StudentID, student.Age, student.Age))
    }
|> Seq.iter (fun (id, value, age) -> printfn "%d %s %s" id (value.Print()) (age.Print()))

printfn "\n Using if statement to alter results special values."
query {
        for student in db.Student do
        select (if student.Age.HasValue && student.Age.Value = -1 then
                   (student.StudentID, System.Nullable<int>(100), student.Age)
                elif student.Age.HasValue && student.Age.Value = 0 then
                    (student.StudentID, System.Nullable<int>(100), student.Age)
                else (student.StudentID, student.Age, student.Age))
    }
|> Seq.iter (fun (id, value, age) -> printfn "%d %s %s" id (value.Print()) (age.Print()))

printfn "\n Multiple table select."
query {
        for student in db.Student do
        for course in db.Course do
        select (student, course)
}
|> Seq.iteri (fun index (student, course) ->
    if (index = 0) then printfn "StudentID Name Age CourseID CourseName"
    printfn "%d %s %s %d %s" student.StudentID student.Name (student.Age.Print()) course.CourseID course.CourseName)

printfn "\nMultiple Joins"
query {
    for student in db.Student do
    join courseSelection in db.CourseSelection on
        (student.StudentID = courseSelection.StudentID)
    join course in db.Course on
          (courseSelection.CourseID = course.CourseID)
    select (student.Name, course.CourseName)
    }










|> Seq.iter (fun (studentName, courseName) -> printfn "%s %s" studentName courseName)

printfn "\nMultiple Left Outer Joins"
query {
   for student in db.Student do
    leftOuterJoin (for courseSelection in db.CourseSelection ->
                   student.StudentID = courseSelection.StudentID) into g1
    for courseSelection in g1.DefaultIfEmpty() do
    leftOuterJoin (for course in db.Course ->
                   courseSelection.CourseID = course.CourseID) into g2
    for course in g2.DefaultIfEmpty() do
    select (student.Name, course.CourseName)
    }
|> Seq.iter (fun (studentName, courseName) -> printfn "%s %s" studentName courseName)

然後這個完整輸出，當程式碼在 F# Interactive 中執行。

請參閱

參考

Linq.QueryBuilder 類別 (F#)

其他資源

F# 語言參考

計算運算式 (F#)