Condividi tramite

Espressioni di query (F#)

  • Articolo

Le espressioni di query consentono di eseguire query a un'origine dati e inserire i dati in una forma desiderata. Le espressioni di query forniscono il supporto per LINQ in F#.

query { expression }

Note

Le espressioni di query sono un tipo di espressione di calcolo simile alle espressioni di sequenza. Come si specifica una sequenza fornendo il codice in un'espressione sequenza, specificare un set di dati fornendo il codice in un'espressione di query. In un'espressione di sequenza, la parola chiave yield identifica i dati da restituire come parte della sequenza risultante. Nelle espressioni di query, la parola chiave select esegue la stessa funzione. Oltre alla parola chiave select, F# supporta anche alcuni operatori di query essi sono molto simile alle parti di un'istruzione SQL SELECT. Di seguito è riportato un esempio di un'espressione di query semplice, con codice che connette a Northwind OData il database di origine.

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

Nell'esempio di codice precedente, l'espressione di query è tra parentesi graffe. Il significato del codice nell'espressione è, restituire ogni cliente nella tabella Customers del database nei risultati della query. Le espressioni di query restituiscono un tipo che implementa IQueryable e IEnumerablee pertanto possono essere ripetuti utilizzando modulo seguente come illustrato nell'esempio.

Ogni tipo di espressione di calcolo è compilato da una classe del generatore. La classe del generatore per l'espressione di calcolo di query è QueryBuilder. Per ulteriori informazioni, vedere Espressioni di calcolo (F#) e Classe Linq.QueryBuilder (F#).

Operatori di query

Gli operatori di query consentono di specificare i dettagli della query, come inserire i criteri sui record da restituire, o specificano l'ordine di ordinamento dei risultati. L'origine di query deve supportare l'operatore di query. Se si tenta di utilizzare un operatore di query non supportato, NotSupportedException verrà generata un'eccezione.

Solo le espressioni che possono essere convertite in SQL sono consentite nelle espressioni di query. Ad esempio, non sono consentite le chiamate di funzione nelle espressioni quando si utilizza l'operatore di query where.

Operatori di query disponibili nella tabella 1. Inoltre, vedere Table2, che confronta successivamente query SQL e le espressioni di query equivalenti di F# in questo argomento. Alcuni operatori di query non sono supportati da alcun tipo di provider. In particolare, il provider di tipo OData è limitato da operatori di query che supportano a causa delle limitazioni in OData. Per ulteriori informazioni, vedere Provider del tipo di ODataService (F#).

Questa tabella presuppone un database nel formato seguente:

Diagramma del database di esempio

Diagramma di database corso per studenti

Il codice nelle tabelle che seguono si assume anche il seguente codice di connessione al database. Aggiungere riferimenti a System.Data, System.Data.Linq e FSharp.Data.TypeProviders.dll. Il codice che crea il database è incluso alla fine di questo argomento.

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]

TABLE_NAMEOperatori di query

Operatore

Descrizione

contains

Determina se gli elementi selezionati includono un elemento specificato.

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

count

Restituisce il numero di elementi nell'oggetto .

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

last

Selezionare l'ultimo elemento di quelli selezionati fino a quel momento.

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

lastOrDefault

Seleziona l'ultimo elemento di quelli selezionati finora o un valore predefinito se non viene trovato alcun elemento.

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

exactlyOne

Selezionare il singolo, elemento specifico selezionato finora. Se più elementi sono presenti, viene generata un'eccezione.

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

exactlyOneOrDefault

Seleziona il singolo elemento specifico di quelli selezionati finora o un valore predefinito se tale elemento non viene trovato.

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

headOrDefault

Seleziona il primo elemento di quelli selezionati finora o un valore predefinito se la sequenza non contiene alcun elemento.

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

select

Progetti per ciascuno degli elementi selezionati fino a quel momento.

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

where

Selezionare gli elementi in base a un predicato specificato.

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

minBy

Seleziona un valore per ogni elemento selezionato finora e restituisce il valore risultante minimo.

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

maxBy

Seleziona un valore per ogni elemento selezionato finora e restituisce il valore risultante massimo.

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

groupBy

Raggruppa gli elementi selezionati fino a un selettore di chiave specificato.

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

sortBy

Ordina gli elementi selezionati finora in ordine crescente in base alla chiave di ordinamento specificata.

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

sortByDescending

Ordina gli elementi selezionati finora in ordine decrescente in base alla chiave di ordinamento specificata.

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

thenBy

Esegue un ordinamento successivo degli elementi selezionati finora in ordine crescente in base alla chiave di ordinamento specificata. Questo operatore può essere utilizzato solo dopo sortBy, sortByDescending, thenBy, o thenByDescending.

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

thenByDescending

Esegue un ordinamento successivo degli elementi selezionati finora in ordine decrescente in base alla chiave di ordinamento specificata. Questo operatore può essere utilizzato solo dopo sortBy, sortByDescending, thenBy, o thenByDescending.

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

groupValBy

Seleziona un valore per ogni elemento selezionato finora e raggruppa gli elementi in base alla chiave specificata.

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

join

Mette in relazione due insiemi di valori selezionati in base alle chiavi corrispondenti. Si noti che l'ordine delle chiavi intorno a = in un'espressione di join è significativo. In tutti i join, se la riga è suddivisa al simbolo ->, il rientro deve essere impostato un rientro fino almeno alla parola chiave for.

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

groupJoin

Correla due set di valori selezionati in base a chiavi corrispondenti e raggruppa i risultati. Si noti che l'ordine delle chiavi intorno a = in un'espressione di join è significativo.

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

Correla due set di valori selezionati in base a chiavi corrispondenti e raggruppa i risultati. Se qualsiasi gruppo è vuoto, viene utilizzato un gruppo con un singolo valore predefinito. Si noti che l'ordine delle chiavi intorno a = in un'espressione di join è significativo.

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

Seleziona un valore nullable per ogni elemento selezionato finora e restituisce la somma di questi valori. Se qualsiasi tipo nullable non dispone di un valore, viene ignorato.

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

minByNullable

Seleziona un valore nullable per ogni elemento selezionato finora e restituisce il valore minimo di tali valori. Se qualsiasi tipo nullable non dispone di un valore, viene ignorato.

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

maxByNullable

Seleziona un valore nullable per ogni elemento selezionato finora e restituisce il valore massimo di tali valori. Se qualsiasi tipo nullable non dispone di un valore, viene ignorato.

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

averageByNullable

Seleziona un valore nullable per ogni elemento selezionato finora e restituisce la media di questi valori. Se qualsiasi tipo nullable non dispone di un valore, viene ignorato.

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

averageBy

Seleziona un valore per ogni elemento selezionato finora e restituisce la media di questi valori.

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

distinct

Selezionare gli elementi distinti dagli elementi selezionati fino a quel momento.

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

exists

Determina un qualsiasi elemento selezionato finora che soddisfa una condizione.

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

find

Seleziona il primo elemento selezionato finora che soddisfa una condizione specificata.

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

all

Determina se tutti gli elementi selezionati finora soddisfano una condizione.

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

head

Selezionare il primo elemento da quelli selezionati fino a quel momento.

query {
    for student in db.Student do
    head
}

nth

Operatore di query che seleziona l'elemento in corrispondenza di un indice specificato tra quelli selezionati finora.

query {
    for numbers in data do
    nth 3
}

skip

Ignora un numero specificato di elementi selezionati finora e seleziona gli elementi rimanenti.

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

skipWhile

Ignora gli elementi in sequenza finché la condizione specificata è soddisfatta e quindi seleziona gli elementi rimanenti.

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

sumBy

Seleziona un valore per ogni elemento selezionato finora e restituisce la somma di questi valori.

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

take

Seleziona un numero specificato di elementi contigui tra quelli selezionati finora.

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

takeWhile

Seleziona gli elementi da una sequenza finché la condizione specificata è soddisfatta, poi ignora gli elementi rimanenti.

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

sortByNullable

Ordina gli elementi selezionati finora in ordine crescente in base alla chiave di ordinamento nullable specificata.

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

sortByNullableDescending

Ordina gli elementi selezionati finora in ordine decrescente in base alla chiave di ordinamento nullable specificata.

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

thenByNullable

Esegue un ordinamento successivo degli elementi selezionati finora in ordine crescente in base alla chiave di ordinamento nullable specificata. Questo operatore può essere utilizzato solo subito dopo sortBy, sortByDescending, thenBy, o thenByDescending, o le variabili nullable.

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

thenByNullableDescending

Esegue un ordinamento successivo degli elementi selezionati finora in ordine decrescente in base alla chiave di ordinamento nullable specificata. Questo operatore può essere utilizzato solo subito dopo sortBy, sortByDescending, thenBy, o thenByDescending, o le variabili nullable.

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

Confronto delle espressioni di query F# e Transact-SQL

Nella tabella seguente vengono illustrate alcune query comune Transact-SQL e i relativi equivalenti in F#. Il codice in questa tabella si presuppone inoltre per lo stesso database la tabella precedente e lo stesso codice iniziale per installare il provider del tipo.

TABLE_NAMEEspressioni di query F# e Transact-SQL

Transact-SQL (non faccia distinzione tra maiuscole e minuscole)

Espressione di query F# (la distinzione tra maiuscole e minuscole)

Selezionare tutti i campi dalla tabella.

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

I conti i record di una tabella.

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
}

Raggruppamento

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())
}

Raggruppamento con la condizione.

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())
}

Raggruppamento con lo stato di conteggio.

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())
}

Raggruppamento, contando e sommare.

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

Raggruppamento, l'ordinamento e contare il numero.

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 Un insieme di valori specificati.

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 e 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 con il set di corrispondenza del modello.

-- '[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 con il modello di esclusione impostato.

-- '[^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 su un campo, ma seleziona un campo diverso.

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, con la ricerca delle sottostringhe.

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 semplice con due tabelle.

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 con due tabelle.

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 con 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        
}

DistinctCount

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 con l'ordine

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, ORe ordine.

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 di due query.

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)

Intersezione di due query.

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 Condizione...

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

Casi multipli.

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

Più tabelle.

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

Join multiplo.

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

Outer join più a sinistra.

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

Il seguente codice può essere utilizzato per creare il database di esempio per questi esempi.

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

Il codice seguente contiene il codice di esempio visualizzato in questo argomento.

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

E di seguito è riportato l'output completo quando questo codice viene eseguito in F# interactive.

                                                                                                                                    

Vedere anche

Riferimenti

Classe Linq.QueryBuilder (F#)

Altre risorse

Riferimenti per il linguaggio F#

Espressioni di calcolo (F#)