Compartir a través de

Expresiones de consulta (F#)

  • Artículo

Las expresiones de consulta permiten consultar un origen de datos y para colocar los datos en un formulario deseado. Las expresiones de consulta proporcionan compatibilidad con LINQ en F#.

query { expression }

Comentarios

Las expresiones de consulta son un tipo de expresión de cálculo similar a las expresiones de secuencia. Igual que especifique una secuencia mediante código en una expresión de secuencia, se especifica un conjunto de datos proporcionando código en una expresión de consulta. En una expresión de secuencia, la palabra clave de yield identifica los datos que se obtienen como parte de la secuencia resultante. En expresiones de consulta, la palabra clave de select realiza la misma función. Además de la palabra clave de select, F# también admite varios operadores de consulta que son como las partes de una instrucción SQL SELECT. A continuación se muestra un ejemplo de una expresión de consulta simple, junto con el código que se conecta a Northwind OData el origen.

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

En el ejemplo de código anterior, la expresión de consulta encuentra entre llaves. El significado del código de la expresión es, devuelve a cada cliente en la tabla customers de la base de datos en los resultados de la consulta. Las expresiones de consulta devuelven un tipo que implementa IQueryable y IEnumerable, por lo que pueden ser iterados mediante Module Seq mientras el ejemplo.

Compilan a cada tipo de la expresión de cálculo de una clase de generador. La clase de generador para la expresión de cálculo de consulta es QueryBuilder. Para obtener más información, vea Expresiones de cálculo (F#) y Linq.QueryBuilder (Clase de F#).

Operadores de consulta

Los operadores de consulta permiten especificar los detalles de la consulta, por ejemplo para colocar criterios en los registros que se va a devolver, o especificar el criterio de ordenación de resultados. El origen de la consulta debe admitir el operador de consulta. Si intenta utilizar un operador no compatibles con la consulta, NotSupportedException se producirá.

Sólo las expresiones que se pueden traducir a SQL se permiten en las expresiones de consulta. Por ejemplo, no se permite que las llamadas de función de las expresiones cuando se utiliza el operador de consulta de where.

La tabla 1 se muestran los operadores disponibles de la consulta. Además, vea Table2, que compara consultas SQL y las expresiones de consulta equivalentes de F# más adelante en este tema. Algunos proveedores de tipo no admiten operadores de consulta. En concreto, el proveedor de tipo de OData se restringe a los operadores de consulta que admite debido a las limitaciones en OData. Para obtener más información, vea Proveedor de tipo de ODataService (F#).

Esta tabla se supone una base de datos en el formato siguiente:

Diagrama de base de datos de ejemplo

Diagrama de base de datos de curso de estudiante

El código en tablas que siguen también supone el siguiente código de conexión a bases de datos. Los proyectos deben agregar referencias a los ensamblados System.Data, de System.Data.Linq, y de FSharp.Data.TypeProviders. El código que crea esta base de datos se incluye al final de este tema.

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]

Figura 1.Operadores de consulta

Operador

Descripción

contains

Determina si los elementos seleccionados incluyen un elemento especificado.

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

count

Devuelve el número de elementos seleccionados.

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

last

Selecciona el último elemento de los seleccionados hasta ahora.

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

lastOrDefault

Selecciona el último elemento de los seleccionados hasta ahora, o un valor predeterminado si no se encuentra ningún elemento.

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

exactlyOne

Selecciona el elemento único, concreto seleccionados hasta ahora. Si varios elementos están presentes, se produce una excepción.

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

exactlyOneOrDefault

Selecciona el elemento único, específico de los seleccionados hasta ahora, o un valor predeterminado si ese elemento no se encuentra.

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

headOrDefault

Selecciona el primer elemento de los seleccionados hasta ahora, o un valor predeterminado si la secuencia no contiene ningún elemento.

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

select

Proyecta cada uno de los elementos seleccionados hasta ahora.

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

where

Selecciona los elementos según un predicado especificado.

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

minBy

Seleccione un valor para cada elemento seleccionado hasta ahora y devuelve el valor resultante mínimo.

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

maxBy

Seleccione un valor para cada elemento seleccionado hasta ahora y devuelve el valor resultante máximo.

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

groupBy

Agrupa los elementos seleccionados hasta ahora como un selector de clave especificado.

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

sortBy

Ordena los elementos seleccionados hasta ahora en orden ascendente por la clave especificada de la ordenación.

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

sortByDescending

Ordena los elementos seleccionados hasta ahora en orden descendente por la clave especificada de la ordenación.

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

thenBy

Realiza una clasificación subsiguiente de los elementos seleccionados hasta ahora en orden ascendente por la clave especificada de la ordenación. Este operador sólo se puede utilizar después de sortBy, de sortByDescending, de thenBy, o de thenByDescending.

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

thenByDescending

Realiza una clasificación subsiguiente de los elementos seleccionados hasta ahora en orden descendente por la clave especificada de la ordenación. Este operador sólo se puede utilizar después de sortBy, de sortByDescending, de thenBy, o de thenByDescending.

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

groupValBy

Seleccione un valor para cada elemento seleccionado hasta ahora y agrupa los elementos por la clave especificada.

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

join

Correlativos dos conjuntos de valores seleccionados según las claves coincidentes. Observe que el orden de las claves alrededor de = firma en una expresión de combinación es significativo. En todo el unión, si la línea se divide después del símbolo de ->, la sangría debe aplicar sangría al menos hasta la palabra clave for.

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

groupJoin

Los correlativos dos conjuntos de valores seleccionados según las claves coincidentes y agrupa los resultados. Observe que el orden de las claves alrededor de = firma en una expresión de combinación es 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

Los correlativos dos conjuntos de valores seleccionados según las claves coincidentes y agrupa los resultados. Si cualquier grupo está vacío, se utiliza en su lugar un grupo con un valor predeterminado único. Observe que el orden de las claves alrededor de = firma en una expresión de combinación es 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

Seleccione un valor que acepta valores NULL para cada elemento seleccionado hasta ahora y devuelve la suma de estos valores. Si cualquiera de los tipos que acepta valores NULL no tiene un valor, este se omite.

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

minByNullable

Seleccione un valor que acepta valores NULL para cada elemento seleccionado hasta ahora y devuelve el mínimo de estos valores. Si cualquiera de los tipos que acepta valores NULL no tiene un valor, este se omite.

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

maxByNullable

Seleccione un valor que acepta valores NULL para cada elemento seleccionado hasta ahora y devuelve el máximo de estos valores. Si cualquiera de los tipos que acepta valores NULL no tiene un valor, este se omite.

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

averageByNullable

Seleccione un valor que acepta valores NULL para cada elemento seleccionado hasta ahora y devuelve el promedio de estos valores. Si cualquiera de los tipos que acepta valores NULL no tiene un valor, este se omite.

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

averageBy

Seleccione un valor para cada elemento seleccionado hasta ahora y devuelve el promedio de estos valores.

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

distinct

Selecciona elementos distintos de los elementos seleccionados hasta ahora.

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

exists

Determina si todos los elementos seleccionados hasta ahora satisface una condición.

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

find

Selecciona el primer elemento seleccionado hasta ahora que cumple una condición especificada.

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

all

Determina si todos los elementos seleccionados hasta ahora satisface una condición.

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

head

Selecciona el primer elemento de los seleccionados hasta ahora.

query {
    for student in db.Student do
    head
}

nth

Selecciona el elemento en un índice especificado entre los seleccionados hasta ahora.

query {
    for numbers in data do
    nth 3
}

skip

Omite un número especificado de elementos seleccionados hasta ahora y después se selecciona los elementos restantes.

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

skipWhile

Omite los elementos de una secuencia mientras la condición especificada sea true y después se selecciona los elementos restantes.

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

sumBy

Seleccione un valor para cada elemento seleccionado hasta ahora y devuelve la suma de estos valores.

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

take

Selecciona un número especificado de elementos contiguos de los seleccionados hasta ahora.

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

takeWhile

Selecciona los elementos de una secuencia como una condición especificada sea true y, a continuación omite los elementos restantes.

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

sortByNullable

Ordena los elementos seleccionados hasta ahora en orden ascendente por clave aceptan dada de la ordenación.

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

sortByNullableDescending

Ordena los elementos seleccionados hasta ahora en orden descendente por clave aceptan dada de la ordenación.

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

thenByNullable

Realiza una clasificación subsiguiente de los elementos seleccionados hasta ahora en orden ascendente por clave aceptan dada de la ordenación. Este operador sólo se puede utilizar inmediatamente después de sortBy, sortByDescending, thenBy, o thenByDescending, o sus variantes que acepta valores NULL.

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

thenByNullableDescending

Realiza una clasificación subsiguiente de los elementos seleccionados hasta ahora en orden descendente por clave aceptan dada de la ordenación. Este operador sólo se puede utilizar inmediatamente después de sortBy, sortByDescending, thenBy, o thenByDescending, o sus variantes que acepta valores NULL.

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

Comparación de Transact-SQL y las expresiones de consulta de F#

La tabla siguiente muestra algunas consultas Transact-SQL comunes y sus equivalentes en F#. El código de esta tabla también supone la misma base de datos que la tabla anterior y el mismo código inicial para configurar el proveedor de tipo.

Figura 2.Transact-SQL y expresiones de consulta de F#

Transact-SQL (no distingue entre mayúsculas y minúsculas)

Expresión de consulta de F# (distingue entre mayúsculas y minúsculas)

Seleccione todos los campos de la tabla.

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

Registros de recuento en una tabla.

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
}

Grupo

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

Agrupación con la condición.

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

Agrupación con la condición de recuento.

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

Agrupar, contando, y el sumar.

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

El agrupar, el contar, y el orden por número.

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 conjunto de valores especificados

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 y 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 el conjunto de la coincidencia de modelos.

-- '[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 el modelo determinado de exclusión.

-- '[^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 en un campo, pero seleccione otro campo.

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 búsqueda de una subcadena.

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 simple con dos tablas.

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 dos tablas.

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        
}

Recuento distinto.

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 a la ordenación

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

TOP, OR, y ordenación.

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 de dos consultas.

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)

Intersección de dos consultas.

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)

condición de 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))
    }

Casos múltiples.

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

Varias tablas.

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

Varios combinaciones.

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

Combinaciones externas izquierdas.

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

El código siguiente se puede usar para crear la base de datos de ejemplo para estos ejemplos.

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

El código siguiente contiene el código de ejemplo que aparece en este tema.

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

Y a continuación se muestra el resultado completo cuando este código se ejecuta en F# interactive.

                                                                                                                                    

Vea también

Referencia

Linq.QueryBuilder (Clase de F#)

Otros recursos

Referencia del lenguaje F#

Expresiones de cálculo (F#)