当前位置： 首页 > news >正文

[转]查询表达式 (F#)

news 来源：原创 2024/5/16 20:44:33

本文转自：http://msdn.microsoft.com/zh-cn/library/hh225374.aspx

查询表达式可以查询数据源并将数据是一种预期形式。             查询表达式提供 F# 中支持 LINQ。 

 
复制 
query { expression }
备注            
            
          

查询表达式是计算表达式的类型类似于序列表达式。               就象通过提供代码指定顺序在序列表达式中，可以在查询表达式指定设置数据通过提供代码。  在序列表达式，yield 关键字标识要返回的数据为产生的序列的一部分。  在查询表达式中，select 关键字执行相同的功能。  除了 select 关键字以外，F# 还支持十分类似于 SQL SELECT 语句的大量查询运算符。  这是一个简单的查询表达式的示例，与连接到 Northwind OData 源的代码。 

F#
复制 
// 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 类型提供程序在查询运算符绑定到 OData 支持由于限制。  有关更多信息，请参见 ODataService 类型提供程序 (f#)。 

此表假定一个数据库为以下形式：                       

示例数据库关系图                       
学生课程数据库关系图          

代码还在下表中假定下列数据库连接代码。               项目应添加对 System.Data、要求和 FSharp.Data.TypeProviders 程序集。  创建此数据库的代码是组成的本主题末尾的。 

F#
复制 
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

确定所选元素是否包含一个指定的元素。

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

count

返回所选元素的数量。

F#
复制 
let countOfStudents =
    query {
        for student in db.Student do
        select student
        count
    }

last

到目前为止选择选定的表的最后一个元素。

F#
复制 
let number = 
    query {
        for number in data do
        last
    }

lastOrDefault

;如果未找到至此，选择选定的表中的最后一个元素或一个默认值元素。

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

exactlyOne

到目前为止选定的一个，特定元素。  如果多个元素存在，则将引发。 

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

exactlyOneOrDefault

;如果未找到至此，选择选定的表的唯一，特定元素或一个默认值该元素。

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

headOrDefault

如果该序列不包含元素至此，选择选定的表的第一个元素或一个默认值。

F#
复制 
let student =
    query {
        for student in db.Student do
        select student
        headOrDefault
    }

select

到目前为止项目选定的每个元素。

F#
复制 
query {
    for student in db.Student do
    select student
    }

where

根据指定谓词的元素。

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

minBy

为选定的每个元素到目前为止选择一个值并返回最小的结果值。

F#
复制 
let student =
    query {
        for student in db.Student do
        minBy student.StudentID
    }

maxBy

为选定的每个元素到目前为止选择一个值并返回最大结果值。

F#
复制 
let student =
    query {
        for student in db.Student do
        maxBy student.StudentID
    }

groupBy

到目前为止组按照指定的键选择器中选择的元素。

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

sortBy

到目前为止对升序选择组件的特定排序的键。

F#
复制 
query {
    for student in db.Student do
    sortBy student.Name
    select student
}

sortByDescending

到目前为止按降序进行选择的元素由特定排序的键。

F#
复制 
query {
    for student in db.Student do
    sortByDescending student.Name
    select student
}

thenBy

在升序到目前为止执行特定排序的键选择哪个后续排序元素。  此运算符只能在 sortBy、sortByDescending、thenBy或 thenByDescending之后只使用。 

F#
复制 
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之后只使用。 

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

groupValBy

为给定键选择和组至此选择一个值元素的每个元素。

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

join

相互关系两个基于匹配的键的设置选定的值。  请注意键的顺序在周围的 = 签名连接表达式是有意义的。  在连接，因此，如果行。-> 符号后拆分，缩进的缩进距离必须至少在关键字 for。 

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

groupJoin

相互关系两个基于匹配的键和组的设置选定的值结果。  请注意键的顺序在周围的 = 签名连接表达式是有意义的。 

F#
复制 
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

相互关系两个基于匹配的键和组的设置选定的值结果。  如果任何组为空，使用一组具有一个默认值。  请注意键的顺序在周围的 = 签名连接表达式是有意义的。 

F#
复制 
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，可不具有值，它将被忽略。 

F#
复制 
query {
    for student in db.Student do
    sumByNullable student.Age
}

minByNullable

为选定的每个元素到目前为止选择一个可以为 null 的值并返回这些值最小位数。  如果有可以为 null，可不具有值，它将被忽略。 

F#
复制 
query {
    for student in db.Student do
    minByNullable student.Age
}

maxByNullable

为选定的每个元素到目前为止选择一个可以为 null 的值并返回这些值最大次数。  如果有可以为 null，可不具有值，它将被忽略。 

F#
复制 
query {
    for student in db.Student do
    maxByNullable student.Age
    }

averageByNullable

为选定的每个元素到目前为止选择一个可以为 null 的值并返回平均值这些值。  如果有可以为 null，可不具有值，它将被忽略。 

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

averageBy

为选定的每个元素到目前为止选择一个值并返回平均值这些值。

F#
复制 
query {
    for student in db.Student do
    averageBy (float student.StudentID)
}

distinct

到目前为止选择不同的组件从所选元素。

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

exists

确定选定的任何元素到目前为止是否满足条件。

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

find

选择到目前为止满足指定条件的第一个选定的元素。

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

all

确定选定的所有元素到目前为止是否满足条件。

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

head

到目前为止选择第一个元素从选定的表。

F#
复制 
query {
    for student in db.Student do
    head
}

nth

到目前为止选择元素在选定的表中的指定索引。

F#
复制 
query {
    for numbers in data do
    nth 3
}

skip

到目前为止跳过所选元素指定数目的然后选择剩余的元素。

F#
复制 
query {
    for student in db.Student do
    skip 1
}

skipWhile

在序列中的元素，只要一个指定的条件为 true 的然后选择剩余的元素。

F#
复制 
query {
    for number in data do
    skipWhile (number < 3)
    select student
    }

sumBy

为选定的每个元素到目前为止选择一个值并返回这些值的总和。

F#
复制 
query {
   for student in db.Student do
   sumBy student.StudentID
   }

take

到目前为止选择连续的指定数量的元素从选定的表。

F#
复制 
query {
   for student in db.Student do
   select student
   take 2
   }

takeWhile

选择元素序列，只要一个指定条件为 true，则跳过剩余的元素。

F#
复制 
query {
    for number in data do
    takeWhile (number < 10)
    }

sortByNullable

到目前为止对升序选择的元素由特定的排序的键。

F#
复制 
query {
    for student in db.Student do
    sortByNullable student.Age
    select student
}

sortByNullableDescending

到目前为止按降序进行选择的元素由特定的排序的键。

F#
复制 
query {
    for student in db.Student do
    sortByNullableDescending student.Age
    select student
}

thenByNullable

在升序到目前为止执行特定的排序的键选择哪个后续排序元素。  此运算符只能在 sortBy之后只使用、sortByDescending、thenBy或 thenByDescending，也可以为 null 的变量。 

F#
复制 
query {
    for student in db.Student do
    sortBy student.Name
    thenByNullable student.Age
    select student
}

thenByNullableDescending

按降序到目前为止执行特定的排序的键选择哪个后续排序元素。  此运算符只能在 sortBy之后只使用、sortByDescending、thenBy或 thenByDescending，也可以为 null 的变量。 

F#
复制 
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# 查询表达式 (区分大小写)                                       

选择所有字段从表。                                       

Transact-SQL
复制 
SELECT * FROM Student
F#
复制 
// All students.
query {
    for student in db.Student do
    select student
}

计数记录在表中。                                       

Transact-SQL
复制 
SELECT COUNT(*) FROM Student
F#
复制 
// Count of students.
query {
    for student in db.Student do        
    count
}

EXISTS                                       

Transact-SQL
复制 
SELECT * FROM Student
WHERE EXISTS 
(SELECT * FROM CourseSelection
WHERE CourseSelection.StudentID = Student.StudentID)
F#
复制 
// 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 }

分组                                       

Transact-SQL
复制 
SELECT Student.Age, COUNT(*) FROM Student
GROUP BY Student.Age
F#
复制 
// 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()) }

分组加上条件。                                       

Transact-SQL
复制 
SELECT Student.Age, COUNT(*) 
FROM Student
GROUP BY Student.Age
HAVING student.Age > 10
F#
复制 
// 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())
}

分组的计数情况。                                       

Transact-SQL
复制 
SELECT Student.Age, COUNT(*)
FROM Student
GROUP BY Student.Age
HAVING COUNT(*) > 1
F#
复制 
// 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())
}

分组，计数、求和与求平均值计算。                                       

Transact-SQL
复制 
SELECT Student.Age, COUNT(*), SUM(Student.Age) as total
FROM Student
GROUP BY Student.Age
F#
复制 
// 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) }

分组，计数和排序由计数。                                       

Transact-SQL
复制 
SELECT Student.Age, COUNT(*) as myCount
FROM Student
GROUP BY Student.Age
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
F#
复制 
// 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     设置指定值                                       

Transact-SQL
复制 
SELECT *
FROM Student
WHERE Student.StudentID IN (1, 2, 5, 10)
F#
复制 
// 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。                                       

Transact-SQL
复制 
-- '_e%' matches strings where the second character is 'e'
SELECT TOP 2 * FROM Student
WHERE Student.Name LIKE '_e%'
F#
复制 
// 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。                                       

Transact-SQL
复制 
-- '[abc]%' matches strings where the first character is
-- 'a', 'b', 'c', 'A', 'B', or 'C'
SELECT * FROM Student
WHERE Student.Name LIKE '[abc]%'
F#
复制 
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。                                       

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

在一个字段的LIKE，但是，选择一个不同的字段。                                       

Transact-SQL
复制 
SELECT StudentID AS ID FROM Student
WHERE Student.Name LIKE '[^abc]%'
F#
复制 
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   ，将子字符串搜索。                                       

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

只有两个表的简单 JOIN。                                       

Transact-SQL
复制 
SELECT * FROM Student
JOIN CourseSelection 
ON Student.StudentID = CourseSelection.StudentID
F#
复制 
// 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。                                       

Transact-SQL
复制 
SELECT * FROM 
Student LEFT JOIN CourseSelection 
ON Student.StudentID = CourseSelection.StudentID
F#
复制 
//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                                       

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

DISTINCT                                       

Transact-SQL
复制 
SELECT DISTINCT StudentID FROM CourseSelection
F#
复制 
// Join with distinct.
query {
    for student in db.Student do
    join selection in db.CourseSelection on
          (student.StudentID = selection.StudentID)
    distinct        
}

清单的计数。                                       

Transact-SQL
复制 
SELECT DISTINCT COUNT(StudentID) FROM CourseSelection
F#
复制 
// 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                                       

Transact-SQL
复制 
SELECT * FROM Student
WHERE Student.Age BETWEEN 10 AND 15
F#
复制 
// 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                                       

Transact-SQL
复制 
SELECT * FROM Student
WHERE Student.Age =11 OR Student.Age = 12
F#
复制 
// 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                                       

Transact-SQL
复制 
SELECT * FROM Student
WHERE Student.Age =12 OR Student.Age = 13
ORDER BY Student.Age DESC
F#
复制 
// 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和排序。                                       

Transact-SQL
复制 
SELECT TOP 2 student.Name FROM Student
WHERE Student.Age = 11 OR Student.Age = 12
ORDER BY Student.Name DESC
F#
复制 
// 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。                                       

Transact-SQL
复制 
SELECT * FROM Student
UNION
SELECT * FROM lastStudent
F#
复制 
// 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)

两个查询的交集。                                       

Transact-SQL
复制 
SELECT * FROM Student
INTERSECT
SELECT * FROM LastStudent
F#
复制 
// 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   情况。                                       

Transact-SQL
复制 
SELECT student.StudentID, 
    CASE Student.Age
        WHEN -1 THEN 100
        ELSE Student.Age
        END,
        Student.Age
from Student
F#
复制 
// 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))     }

多个用例。                                       

Transact-SQL
复制 
SELECT Student.StudentID, 
    CASE Student.Age
        WHEN -1 THEN 100
        WHEN 0 THEN 1000
        ELSE Student.Age
    END,
    Student.Age
FROM Student
F#
复制 
// 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))     }

多个表。                                       

Transact-SQL
复制 
SELECT * FROM Student, Course
F#
复制 
// Multiple table select.
query {
        for student in db.Student do
        for course in db.Course do         select (student, course) }

多联接。                                       

Transact-SQL
复制 
SELECT Student.Name, Course.CourseName
FROM Student
JOIN CourseSelection
ON CourseSelection.StudentID = Student.StudentID
JOIN Course
ON Course.CourseID = CourseSelection.CourseID
F#
复制 
 // 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)     }

多个左外部联接。                                       

Transact-SQL
复制 
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
F#
复制 
// 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)     }

下面的代码可用于创建这些示例的示例数据库。                       

Transact-SQL
复制 
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);

下面的代码包含显示在本主题的代码示例。

F#
复制 
#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

相关文章：

  • UINavigationControlle 之 UINavigationBar及navigationItem关系探讨
  • sublime text2快捷键的使用
  • windows创建进程的3种方式下各API调用层次
  • 看看别人家的孩子
  • nginx和dubbo微服务架构图
  • 北京西客站火车行李托运指南
  • DNS原理和正、反向解析配置
  • android Vibrator 使用
  • Reddit CEO亲自诠释内容审核的无奈
  • 本地磁盘上有SVN的感叹号的解决办法
  • 第一篇博客
  • 无法加载 DLL“oramts.dll”: 找不到指定的模块。 (异常来自 HRESULT:0x8007007E)。 —— 的解决方法...
  • JDK9: 集成 Jshell 和 Maven 项目.
  • PE文件结构深入详解
  • 【HTTP】Speed and Mobility: An Approach for HTTP 2.0 to Make Mobile Apps and the Web Faster
  • SegmentFault for Android 3.0 发布
  • 实现windows 窗体的自己画，网上摘抄的，学习了
  • 《Javascript高级程序设计 (第三版)》第五章 引用类型
  • 2019.2.20 c++ 知识梳理
  • echarts的各种常用效果展示
  • Git初体验
  • js 实现textarea输入字数提示
  • sublime配置文件
  • V4L2视频输入框架概述
  • v-if和v-for连用出现的问题
  • vue中实现单选
  • 开发了一款写作软件(OSX,Windows)，附带Electron开发指南
  • 盘点那些不知名却常用的 Git 操作
  • 前嗅ForeSpider教程：创建模板
  • 嵌入式文件系统
  • 入口文件开始，分析Vue源码实现
  • 说说动画卡顿的解决方案
  • 在Mac OS X上安装 Ruby运行环境
  • 正则表达式小结
  • 终端用户监控：真实用户监控还是模拟监控？
  • 2017年360最后一道编程题
  • ​LeetCode解法汇总2583. 二叉树中的第 K 大层和
  • ​软考-高级-信息系统项目管理师教程 第四版【第14章-项目沟通管理-思维导图】​
  • $.type 怎么精确判断对象类型的 --（源码学习2）
  • (13)Hive调优——动态分区导致的小文件问题
  • (done) 两个矩阵 “相似” 是什么意思？
  • （Redis使用系列） Springboot 在redis中使用BloomFilter布隆过滤器机制 六
  • （二）构建dubbo分布式平台-平台功能导图
  • （含react-draggable库以及相关BUG如何解决）固定在左上方某盒子内（如按钮）添加可拖动功能，使用react hook语法实现
  • （七）c52学习之旅-中断
  • （亲测）设​置​m​y​e​c​l​i​p​s​e​打​开​默​认​工​作​空​间...
  • (转)Linux整合apache和tomcat构建Web服务器
  • (转)使用VMware vSphere标准交换机设置网络连接
  • (转)自己动手搭建Nginx+memcache+xdebug+php运行环境绿色版 For windows版
  • ******IT公司面试题汇总+优秀技术博客汇总
  • .net 获取url的方法
  • .NET 中 GetHashCode 的哈希值有多大概率会相同（哈希碰撞）
  • .NET国产化改造探索（三）、银河麒麟安装.NET 8环境
  • .NET企业级应用架构设计系列之应用服务器
  • .net与java建立WebService再互相调用