クエリ式 (F#)

クエリ式は、データ ソースを照会し、目的のフォームにデータを設定できるようになります。クエリ式は、F# の LINQ をサポートします。

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)

前のコード例では、クエリ式は、中かっこにあります。式のコードの意味であるため、クエリ結果のデータベースの貴社のテーブルのすべての貴社を返します。クエリ式は、例に示すように IQueryable<T>、を返します IEnumerable<T>、および実装するため [Seq module] を使用して型を繰り返すことができます。

すべてのコンピュテーション式の型はビルダー クラスからビルドします。クエリのコンピュテーション式のビルダー クラスは QueryBuilderです。詳細については、「コンピュテーション式 (F#)」および「Linq.QueryBuilder クラス (F#)」を参照してください。

クエリ演算子

クエリ演算子は、返されるレコードに条件を設定するクエリの詳細 (など) を指定できるようにするか、結果の並べ替え順序を指定します。クエリのソースはクエリ演算子をサポートする必要があります。サポートされていないクエリ演算子を使用すると NotSupportedException がスローされます。

SQL に変換できる式だけがクエリ式で使用されます。たとえば where クエリ演算子を使用すると、式での関数呼び出しは使用できません。

表 1 は、使用可能なクエリ演算子。また、このトピックの SQL クエリと F# の同等のクエリ式を後で比較する Table2 "を参照してください。一部のクエリ演算子は、型のプロバイダーによってサポートされていません。特に、OData の型のプロバイダーはクエリ演算子で 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

相互に関連する 2 組の一致するキーに基づいて選択した値。の周囲のキーの順序が = バインディング式が重要である署名されていることに注意してください。すべての結合では、行が -> 記号の後に分割すると、インデントはキーワード forまで少なくともインデントする必要があります。

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

groupJoin

相互の関係は 2 セットの一致に基づいて選択された結果値を調整し、グループ化。の周囲のキーの順序が = バインディング式が重要である署名されていることに注意してください。

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

相互の関係は 2 セットの一致に基づいて選択された結果値を調整し、グループ化。任意のグループが空の場合は、1 つの既定値を持つグループによって代用されます。の周囲のキーの順序が = バインディング式が重要である署名されていることに注意してください。

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 許容値を選択し、これらの値の合計を返します。null 許容値が値を持っていないときは、無視されます。

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

minByNullable

これまでにより、選択された各要素の null 許容値を選択し、これらの値の最小値を返します。null 許容値が値を持っていないときは、無視されます。

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

maxByNullable

これまでにより、選択された各要素の null 許容値を選択し、これらの値の最大値を返します。null 許容値が値を持っていないときは、無視されます。

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

averageByNullable

これまでにより、選択された各要素の null 許容値を選択し、これらの値の平均を返します。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

指定された条件が満たされた選択し、残りの要素をバイパス限りシーケンスの要素が。

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

sortByNullable

特定の Null 並べ替えキーによって昇順にこの時点までの選択された要素を並べ替えます。

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

sortByNullableDescending

特定の Null 並べ替えキーによって降順でこの時点までの選択された要素を並べ替えます。

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  
    }

1 フィールドの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
}

2 テーブルとの単純な 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)
}

2 テーブルとの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)
    }

COUNTのJOIN

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
    }

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)

2 種類のクエリの積集合。

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 で実行するときに完全な出力を次に示します。

  --> Referenced 'C:\Program Files (x86)\Reference Assemblies\Microsoft\FSharp\3.0\Runtime\v4.0\Type Providers\FSharp.Data.TypeProviders.dll'


--> Referenced 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Data.dll'


--> Referenced 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Data.Linq.dll'


contains query operator
Binding session to 'C:\Users\ghogen\AppData\Local\Temp\tmp5E3C.dll'...
  Binding session to 'C:\Users\ghogen\AppData\Local\Temp\tmp611A.dll'...
  Is at least one student age 11?
  true

count query operator
Number of students: 8

last query operator.
  Last number: 21

lastOrDefault query operator.
  lastOrDefault: 21

exactlyOne query operator.
  Student with StudentID = 1 is Abercrombie, Kim

exactlyOneOrDefault query operator.
  Student with StudentID = 1 is Abercrombie, Kim

headOrDefault query operator.
  head student is Abercrombie, Kim

select query operator.
  StudentID, Name: 1 Abercrombie, Kim
StudentID, Name: 2 Abolrous, Hazen
StudentID, Name: 3 Hance, Jim
StudentID, Name: 4 Adams, Terry
StudentID, Name: 5 Hansen, Claus
StudentID, Name: 6 Penor, Lori
StudentID, Name: 7 Perham, Tom
StudentID, Name: 8 Peng, Yun-Feng

where query operator.
  StudentID, Name: 5 Hansen, Claus
StudentID, Name: 6 Penor, Lori
StudentID, Name: 7 Perham, Tom
StudentID, Name: 8 Peng, Yun-Feng

minBy query operator.
  maxBy query operator.
  groupBy query operator.
  Age: NULL Count at that age: 1
Age: 10 Count at that age: 1
Age: 11 Count at that age: 1
Age: 12 Count at that age: 3
Age: 13 Count at that age: 1
Age: 14 Count at that age: 1

sortBy query operator.
  StudentID, Name: 1 Abercrombie, Kim
StudentID, Name: 2 Abolrous, Hazen
StudentID, Name: 4 Adams, Terry
StudentID, Name: 3 Hance, Jim
StudentID, Name: 5 Hansen, Claus
StudentID, Name: 8 Peng, Yun-Feng
StudentID, Name: 6 Penor, Lori
StudentID, Name: 7 Perham, Tom

sortByDescending query operator.
  StudentID, Name: 7 Perham, Tom
StudentID, Name: 6 Penor, Lori
StudentID, Name: 8 Peng, Yun-Feng
StudentID, Name: 5 Hansen, Claus
StudentID, Name: 3 Hance, Jim
StudentID, Name: 4 Adams, Terry
StudentID, Name: 2 Abolrous, Hazen
StudentID, Name: 1 Abercrombie, Kim

thenBy query operator.
  StudentID, Name: 10 Abercrombie, Kim
StudentID, Name: 11 Hansen, Claus
StudentID, Name: 12 Adams, Terry
StudentID, Name: 12 Hance, Jim
StudentID, Name: 12 Perham, Tom
StudentID, Name: 13 Penor, Lori
StudentID, Name: 14 Abolrous, Hazen

thenByDescending query operator.
  StudentID, Name: 10 Abercrombie, Kim
StudentID, Name: 11 Hansen, Claus
StudentID, Name: 12 Perham, Tom
StudentID, Name: 12 Hance, Jim
StudentID, Name: 12 Adams, Terry
StudentID, Name: 13 Penor, Lori
StudentID, Name: 14 Abolrous, Hazen

groupValBy query operator.
  Age: NULL Count at that age: 1
Name: Peng, Yun-Feng
Age: 10 Count at that age: 1
Name: Abercrombie, Kim
Age: 11 Count at that age: 1
Name: Hansen, Claus
Age: 12 Count at that age: 3
Name: Hance, Jim
Name: Adams, Terry
Name: Perham, Tom
Age: 13 Count at that age: 1
Name: Penor, Lori
Age: 14 Count at that age: 1
Name: Abolrous, Hazen

sumByNullable query operator
Sum of ages: 84

minByNullable
Minimum age: 10

maxByNullable
Maximum age: 14

averageBy
Average student ID: 4.500000

averageByNullable
Average age: 12

find query operator
Found a match with StudentID = 1

all query operator
Do all students have a comma in the name?
  true

head query operator
Found the head student with StudentID = 1

nth query operator
Third number is 11

skip query operator
StudentID = 2
StudentID = 3
StudentID = 4
StudentID = 5
StudentID = 6
StudentID = 7
StudentID = 8

skipWhile query operator
Number = 5
Number = 7
Number = 11
Number = 18
Number = 21

sumBy query operator
Sum of student IDs: 36

take query operator
StudentID = 1
StudentID = 2

takeWhile query operator
Number = 1
Number = 5
Number = 7

sortByNullable query operator
StudentID, Name, Age: 8 Peng, Yun-Feng NULL
StudentID, Name, Age: 1 Abercrombie, Kim 10
StudentID, Name, Age: 5 Hansen, Claus 11
StudentID, Name, Age: 7 Perham, Tom 12
StudentID, Name, Age: 3 Hance, Jim 12
StudentID, Name, Age: 4 Adams, Terry 12
StudentID, Name, Age: 6 Penor, Lori 13
StudentID, Name, Age: 2 Abolrous, Hazen 14

sortByNullableDescending query operator
StudentID, Name, Age: 2 Abolrous, Hazen 14
StudentID, Name, Age: 6 Penor, Lori 13
StudentID, Name, Age: 7 Perham, Tom 12
StudentID, Name, Age: 3 Hance, Jim 12
StudentID, Name, Age: 4 Adams, Terry 12
StudentID, Name, Age: 5 Hansen, Claus 11
StudentID, Name, Age: 1 Abercrombie, Kim 10
StudentID, Name, Age: 8 Peng, Yun-Feng NULL

thenByNullable query operator
StudentID, Name, Age: 1 Abercrombie, Kim 10
StudentID, Name, Age: 2 Abolrous, Hazen 14
StudentID, Name, Age: 4 Adams, Terry 12
StudentID, Name, Age: 3 Hance, Jim 12
StudentID, Name, Age: 5 Hansen, Claus 11
StudentID, Name, Age: 8 Peng, Yun-Feng NULL
StudentID, Name, Age: 6 Penor, Lori 13
StudentID, Name, Age: 7 Perham, Tom 12

thenByNullableDescending query operator
StudentID, Name, Age: 1 Abercrombie, Kim 10
StudentID, Name, Age: 2 Abolrous, Hazen 14
StudentID, Name, Age: 4 Adams, Terry 12
StudentID, Name, Age: 3 Hance, Jim 12
StudentID, Name, Age: 5 Hansen, Claus 11
StudentID, Name, Age: 8 Peng, Yun-Feng NULL
StudentID, Name, Age: 6 Penor, Lori 13
StudentID, Name, Age: 7 Perham, Tom 12
All students: 
Abercrombie, Kim 1 10
Abolrous, Hazen 2 14
Hance, Jim 3 12
Adams, Terry 4 12
Hansen, Claus 5 11
Penor, Lori 6 13
Perham, Tom 7 12
Peng, Yun-Feng 8 NULL

Count of students: 
Student count: 8

Exists.
"
  Abercrombie, Kim"
"Abolrous, Hazen"
"Hance, Jim"
"Adams, Terry"
"Hansen, Claus"
"Perham, Tom"

Group by age and count
NULL 1
10 1
11 1
12 3
13 1
14 1

Group value by age.
  NULL 1
10 1
11 1
12 3
13 1
14 1

Group students by age where age > 10.
  Age: 11
Hansen, Claus
Age: 12
Hance, Jim
Adams, Terry
Perham, Tom
Age: 13
Penor, Lori
Age: 14
Abolrous, Hazen

Group students by age and print counts of number of students at each age with more than 1 student.
  Age: 12 Count: 3

Group students by age and sum ages.
  Age: 0
Count: 1
Total years: 
Age: 10
Count: 1
Total years: 10
Age: 11
Count: 1
Total years: 11
Age: 12
Count: 3
Total years: 36
Age: 13
Count: 1
Total years: 13
Age: 14
Count: 1
Total years: 14

Group students by age and count number of students at each age, and display all with count > 1 in descending order of count.
  Age: 12
Count: 3

Select students from a set of IDs
Name: Abercrombie, Kim
Name: Abolrous, Hazen
Name: Hansen, Claus

Look for students with Name match _e% pattern and take first two.
  Penor, Lori
Perham, Tom

Look for students with Name matching [abc]% pattern.
  Abercrombie, Kim
Abolrous, Hazen
Adams, Terry

Look for students with name matching [^abc]% pattern.
  Hance, Jim
Hansen, Claus
Penor, Lori
Perham, Tom
Peng, Yun-Feng

Look for students with name matching [^abc]% pattern and select ID.
  3
5
6
7
8

Using Contains as a query filter.
  Abercrombie, Kim
Abolrous, Hazen
Hance, Jim
Adams, Terry
Hansen, Claus
Perham, Tom

Searching for names from a list.
  Join Student and CourseSelection tables.
  2 Abolrous, Hazen 2
3 Hance, Jim 3
5 Hansen, Claus 5
2 Abolrous, Hazen 2
5 Hansen, Claus 5
6 Penor, Lori 6
3 Hance, Jim 3
2 Abolrous, Hazen 2
1 Abercrombie, Kim 1
2 Abolrous, Hazen 2
5 Hansen, Claus 5
2 Abolrous, Hazen 2
3 Hance, Jim 3
2 Abolrous, Hazen 2
3 Hance, Jim 3

Left Join Student and CourseSelection tables.
  1 Abercrombie, Kim 10 9 3 1
2 Abolrous, Hazen 14 1 1 2
2 Abolrous, Hazen 14 4 2 2
2 Abolrous, Hazen 14 8 3 2
2 Abolrous, Hazen 14 10 4 2
2 Abolrous, Hazen 14 12 4 2
2 Abolrous, Hazen 14 14 5 2
3 Hance, Jim 12 2 1 3
3 Hance, Jim 12 7 2 3
3 Hance, Jim 12 13 5 3
3 Hance, Jim 12 15 7 3
4 Adams, Terry 12 NULL NULL NULL
5 Hansen, Claus 11 3 1 5
5 Hansen, Claus 11 5 2 5
5 Hansen, Claus 11 11 4 5
6 Penor, Lori 13 6 2 6
7 Perham, Tom 12 NULL NULL NULL
8 Peng, Yun-Feng 0 NULL NULL NULL

Join with count
15

Join with distinct.
  Abercrombie, Kim 2
Abercrombie, Kim 3
Abercrombie, Kim 5
Abolrous, Hazen 2
Abolrous, Hazen 5
Abolrous, Hazen 6
Abolrous, Hazen 3
Hance, Jim 2
Hance, Jim 1
Adams, Terry 2
Adams, Terry 5
Adams, Terry 2
Hansen, Claus 3
Hansen, Claus 2
Perham, Tom 3

Join with distinct and count.
  15

Selecting students with age between 10 and 15.
  Abercrombie, Kim
Abolrous, Hazen
Hance, Jim
Adams, Terry
Hansen, Claus
Penor, Lori
Perham, Tom

Selecting students with age either 11 or 12.
  Hance, Jim
Adams, Terry
Hansen, Claus
Perham, Tom

Selecting students in a certain age range and sorting.
  Penor, Lori 13
Perham, Tom 12
Hance, Jim 12
Adams, Terry 12

Selecting students with certain ages, taking account of possibility of nulls.
  Hance, Jim
Adams, Terry

Union of two queries.
  Abercrombie, Kim 10
Abolrous, Hazen 14
Hance, Jim 12
Adams, Terry 12
Hansen, Claus 11
Penor, Lori 13
Perham, Tom 12
Peng, Yun-Feng NULL

Intersect of two queries.
  Using if statement to alter results for special value.
  1 10 10
2 14 14
3 12 12
4 12 12
5 11 11
6 13 13
7 12 12
8 NULL NULL

Using if statement to alter results special values.
  1 10 10
2 14 14
3 12 12
4 12 12
5 11 11
6 13 13
7 12 12
8 NULL NULL

Multiple table select.
  StudentID Name Age CourseID CourseName
1 Abercrombie, Kim 10 1 Algebra I
2 Abolrous, Hazen 14 1 Algebra I
3 Hance, Jim 12 1 Algebra I
4 Adams, Terry 12 1 Algebra I
5 Hansen, Claus 11 1 Algebra I
6 Penor, Lori 13 1 Algebra I
7 Perham, Tom 12 1 Algebra I
8 Peng, Yun-Feng NULL 1 Algebra I
1 Abercrombie, Kim 10 2 Trigonometry
2 Abolrous, Hazen 14 2 Trigonometry
3 Hance, Jim 12 2 Trigonometry
4 Adams, Terry 12 2 Trigonometry
5 Hansen, Claus 11 2 Trigonometry
6 Penor, Lori 13 2 Trigonometry
7 Perham, Tom 12 2 Trigonometry
8 Peng, Yun-Feng NULL 2 Trigonometry
1 Abercrombie, Kim 10 3 Algebra II
2 Abolrous, Hazen 14 3 Algebra II
3 Hance, Jim 12 3 Algebra II
4 Adams, Terry 12 3 Algebra II
5 Hansen, Claus 11 3 Algebra II
6 Penor, Lori 13 3 Algebra II
7 Perham, Tom 12 3 Algebra II
8 Peng, Yun-Feng NULL 3 Algebra II
1 Abercrombie, Kim 10 4 History
2 Abolrous, Hazen 14 4 History
3 Hance, Jim 12 4 History
4 Adams, Terry 12 4 History
5 Hansen, Claus 11 4 History
6 Penor, Lori 13 4 History
7 Perham, Tom 12 4 History
8 Peng, Yun-Feng NULL 4 History
1 Abercrombie, Kim 10 5 English
2 Abolrous, Hazen 14 5 English
3 Hance, Jim 12 5 English
4 Adams, Terry 12 5 English
5 Hansen, Claus 11 5 English
6 Penor, Lori 13 5 English
7 Perham, Tom 12 5 English
8 Peng, Yun-Feng NULL 5 English
1 Abercrombie, Kim 10 6 French
2 Abolrous, Hazen 14 6 French
3 Hance, Jim 12 6 French
4 Adams, Terry 12 6 French
5 Hansen, Claus 11 6 French
6 Penor, Lori 13 6 French
7 Perham, Tom 12 6 French
8 Peng, Yun-Feng NULL 6 French
1 Abercrombie, Kim 10 7 Chinese
2 Abolrous, Hazen 14 7 Chinese
3 Hance, Jim 12 7 Chinese
4 Adams, Terry 12 7 Chinese
5 Hansen, Claus 11 7 Chinese
6 Penor, Lori 13 7 Chinese
7 Perham, Tom 12 7 Chinese
8 Peng, Yun-Feng NULL 7 Chinese

Multiple Joins
Abercrombie, Kim Trigonometry
Abercrombie, Kim Algebra II
Abercrombie, Kim English
Abolrous, Hazen Trigonometry
Abolrous, Hazen English
Abolrous, Hazen French
Abolrous, Hazen Algebra II
Hance, Jim Trigonometry
Hance, Jim Algebra I
Adams, Terry Trigonometry
Adams, Terry English
Adams, Terry Trigonometry
Hansen, Claus Algebra II
Hansen, Claus Trigonometry
Perham, Tom Algebra II

Multiple Left Outer Joins
Abercrombie, Kim Trigonometry
Abercrombie, Kim Algebra II
Abercrombie, Kim English
Abolrous, Hazen Trigonometry
Abolrous, Hazen English
Abolrous, Hazen French
Abolrous, Hazen Algebra II
Hance, Jim Trigonometry
Hance, Jim Algebra I
Adams, Terry Trigonometry
Adams, Terry English
Adams, Terry Trigonometry
Hansen, Claus Algebra II
Hansen, Claus Trigonometry
Penor, Lori 
Perham, Tom Algebra II
Peng, Yun-Feng 

type schema
val db : schema.ServiceTypes.SimpleDataContextTypes.MyDatabase1
val student : System.Data.Linq.Table<schema.ServiceTypes.Student>
val data : int list = [1; 5; 7; 11; 18; 21]
type Nullable<'T
                when 'T : (new : unit ->  'T) and 'T : struct and
                     'T :> System.ValueType> with
  member Print : unit -> string
val num : int = 21
val student2 : schema.ServiceTypes.Student
val student3 : schema.ServiceTypes.Student
val student4 : schema.ServiceTypes.Student
val student5 : int = 1
val student6 : int = 8
val idList : int list = [1; 2; 5; 10]
val idQuery : seq<int>
val names : string [] = [|"a"; "b"; "c"|]
module Queries = begin
  val query1 : System.Linq.IQueryable<string * System.Nullable<int>>
  val query2 : System.Linq.IQueryable<string * System.Nullable<int>>
end
module Queries2 = begin
  val query1 : System.Linq.IQueryable<string * System.Nullable<int>>
  val query2 : System.Linq.IQueryable<string * System.Nullable<int>>
end

参照

関連項目

Linq.QueryBuilder クラス (F#)

その他の技術情報

F# 言語リファレンス

コンピュテーション式 (F#)