다음을 통해 공유

쿼리 식(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>, 및 반복 수를 사용 하 여 Seq 모듈 예제와 같이.

모든 계산 식 형식은 작성기 클래스를 기반으로 합니다.쿼리 계산 식 작성기 클래스는 QueryBuilder.자세한 내용은 계산 식(F#) 및 Linq.QueryBuilder 클래스(F#)을 참조하십시오.

쿼리 연산자

쿼리 연산자 쿼리의 세부 정보 같은 조건을 반환 될 레코드에 넣으려면 지정할 수 있도록 또는 결과의 정렬 순서를 지정 합니다.쿼리 원본 쿼리 연산자를 지원 해야 합니다.지원 되지 않는 쿼리 연산자를 사용 하는 경우 NotSupportedException throw 됩니다.

SQL에 변환 될 수 있는 식에만 쿼리 식에서 허용 됩니다.사용 하는 경우 예를 들어, 함수 호출 식의 수는 where 쿼리 연산자입니다.

표 1 사용 가능한 쿼리 연산자를 보여 줍니다.또한 Table2 SQL 쿼리 및 해당 F# 쿼리 식이이 항목의 뒷부분에 비교를 참조 하십시오.일부 쿼리 연산자는 일부 형식 공급자에 의해 지원 되지 않습니다.특히 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입니다.쿼리 연산자

Operator

설명

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

지금까지 선택의 특정 단일 요소를 선택 합니다.여러 요소가 있는 경우 예외가 throw 됩니다.

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 허용 값을 선택 하 고 이러한 값의 합계를 반환 합니다.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

지금 선택한 각 요소에 대 한 nullable 값을 선택 하 고 이러한 값의 최대값을 반환 합니다.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

지금까지 지정 된 nullable 정렬 키가 오름차순으로 선택한 요소를 정렬 합니다.

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

sortByNullableDescending

지금까지 지정 된 nullable 정렬 키로 내림차순을 선택한 요소를 정렬 합니다.

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

thenByNullable

지금까지 지정 된 nullable 정렬 키가 오름차순으로 선택한 요소도 다시 정렬 합니다.직후이 연산자만 사용할 수 있습니다는 sortBy, sortByDescending, thenBy, 또는 thenByDescending, 또는 해당 nullable 변형 합니다.

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

thenByNullableDescending

지금까지 지정 된 nullable 정렬 키로 내림차순을 선택한 요소도 다시 정렬 합니다.직후이 연산자만 사용할 수 있습니다는 sortBy, sortByDescending, thenBy, 또는 thenByDescending, 또는 해당 nullable 변형 합니다.

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

트랜잭션-SQL 및 F# 쿼리 식의 비교

다음 표에서 몇 가지 일반적인 Transact SQL 쿼리 및 해당 하는 F# 표시.또한 코드에이 테이블 위의 표에 같은 초기 코드 형식 공급자를 설정 하려면 동일한 데이터베이스를 가정 합니다.

표 2입니다.트랜잭션-SQL 및 F# 쿼리 식

트랜잭션 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
}

TOPOR, 및 순서.

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#)