Query Expressions (F#)
Query expressions enable you to query a data source and put the data in a desired form. Query expressions provide support for LINQ in F#.
query { expression }
Remarks
Query expressions are a type of computation expression similar to sequence expressions. Just as you specify a sequence by providing code in a sequence expression, you specify a set of data by providing code in a query expression. In a sequence expression, the yield keyword identifies data to be returned as part of the resulting sequence. In query expressions, the select keyword performs the same function. In addition to the select keyword, F# also supports a number of query operators that are much like the parts of a SQL SELECT statement. Here is an example of a simple query expression, along with code that connects to the Northwind OData source.
// 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)
In the previous code example, the query expression is in curly braces. The meaning of the code in the expression is, return every customer in the Customers table in the database in the query results. Query expressions return a type that implements IQueryable<T> and IEnumerable<T>, and so they can be iterated using the Seq module as the example shows.
Every computation expression type is built from a builder class. The builder class for the query computation expression is QueryBuilder. For more information, see Computation Expressions (F#) and Linq.QueryBuilder Class (F#).
Query Operators
Query operators enable you to specify the details of the query, such as to put criteria on records to be returned, or specify the sorting order of results. The query source must support the query operator. If you attempt to use an unsupported query operator, NotSupportedException will be thrown.
Only expressions that can be translated to SQL are allowed in query expressions. For example, no function calls are allowed in the expressions when you use the where query operator.
Table 1 shows available query operators. In addition, see Table2, which compares SQL queries and the equivalent F# query expressions later in this topic. Some query operators aren't supported by some type providers. In particular, the OData type provider is limited in the query operators that it supports due to limitations in OData. For more information, see ODataService Type Provider (F#).
This table assumes a database in the following form:
Sample Database Diagram
The code in the tables that follow also assumes the following database connection code. Projects should add references to System.Data, System.Data.Linq, and FSharp.Data.TypeProviders assemblies. The code that creates this database is included at the end of this topic.
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 1. Query Operators
Operator |
Description |
contains |
Determines whether the selected elements include a specified element.
|
count |
Returns the number of selected elements.
|
last |
Selects the last element of those selected so far.
|
lastOrDefault |
Selects the last element of those selected so far, or a default value if no element is found.
|
exactlyOne |
Selects the single, specific element selected so far. If multiple elements are present, an exception is thrown.
|
exactlyOneOrDefault |
Selects the single, specific element of those selected so far, or a default value if that element is not found.
|
headOrDefault |
Selects the first element of those selected so far, or a default value if the sequence contains no elements.
|
select |
Projects each of the elements selected so far.
|
where |
Selects elements based on a specified predicate.
|
minBy |
Selects a value for each element selected so far and returns the minimum resulting value.
|
maxBy |
Selects a value for each element selected so far and returns the maximum resulting value.
|
groupBy |
Groups the elements selected so far according to a specified key selector.
|
sortBy |
Sorts the elements selected so far in ascending order by the given sorting key.
|
sortByDescending |
Sorts the elements selected so far in descending order by the given sorting key.
|
thenBy |
Performs a subsequent ordering of the elements selected so far in ascending order by the given sorting key. This operator may only be used after a sortBy, sortByDescending, thenBy, or thenByDescending.
|
thenByDescending |
Performs a subsequent ordering of the elements selected so far in descending order by the given sorting key. This operator may only be used after a sortBy, sortByDescending, thenBy, or thenByDescending.
|
groupValBy |
Selects a value for each element selected so far and groups the elements by the given key.
|
join |
Correlates two sets of selected values based on matching keys. Note that the order of the keys around the = sign in a join expression is significant. In all joins, if the line is split after the -> symbol, the indentation must be indented at least as far as the keyword for.
|
groupJoin |
Correlates two sets of selected values based on matching keys and groups the results. Note that the order of the keys around the = sign in a join expression is significant.
|
leftOuterJoin |
Correlates two sets of selected values based on matching keys and groups the results. If any group is empty, a group with a single default value is used instead. Note that the order of the keys around the = sign in a join expression is significant.
|
sumByNullable |
Selects a nullable value for each element selected so far and returns the sum of these values. If any nullable does not have a value, it is ignored.
|
minByNullable |
Selects a nullable value for each element selected so far and returns the minimum of these values. If any nullable does not have a value, it is ignored.
|
maxByNullable |
Selects a nullable value for each element selected so far and returns the maximum of these values. If any nullable does not have a value, it is ignored.
|
averageByNullable |
Selects a nullable value for each element selected so far and returns the average of these values. If any nullable does not have a value, it is ignored.
|
averageBy |
Selects a value for each element selected so far and returns the average of these values.
|
distinct |
Selects distinct elements from the elements selected so far.
|
exists |
Determines whether any element selected so far satisfies a condition.
|
find |
Selects the first element selected so far that satisfies a specified condition.
|
all |
Determines whether all elements selected so far satisfy a condition.
|
head |
Selects the first element from those selected so far.
|
nth |
Selects the element at a specified index amongst those selected so far.
|
skip |
Bypasses a specified number of the elements selected so far and then selects the remaining elements.
|
skipWhile |
Bypasses elements in a sequence as long as a specified condition is true and then selects the remaining elements.
|
sumBy |
Selects a value for each element selected so far and returns the sum of these values.
|
take |
Selects a specified number of contiguous elements from those selected so far.
|
takeWhile |
Selects elements from a sequence as long as a specified condition is true, and then skips the remaining elements.
|
sortByNullable |
Sorts the elements selected so far in ascending order by the given nullable sorting key.
|
sortByNullableDescending |
Sorts the elements selected so far in descending order by the given nullable sorting key.
|
thenByNullable |
Performs a subsequent ordering of the elements selected so far in ascending order by the given nullable sorting key. This operator may only be used immediately after a sortBy, sortByDescending, thenBy, or thenByDescending, or their nullable variants.
|
thenByNullableDescending |
Performs a subsequent ordering of the elements selected so far in descending order by the given nullable sorting key. This operator may only be used immediately after a sortBy, sortByDescending, thenBy, or thenByDescending, or their nullable variants.
|
Comparison of Transact-SQL and F# Query Expressions
The following table shows some common Transact-SQL queries and their equivalents in F#. The code in this table also assumes the same database as the previous table and the same initial code to set up the type provider.
Table 2. Transact-SQL and F# Query Expressions
The following code can be used to create the sample database for these examples.
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);
The following code contains the sample code that appears in this topic.
#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)
And here is the full output when this code is run in F# Interactive.
--> Referenced 'C:\Program Files (x86)\Reference Assemblies\Microsoft\FSharp\3.0\Runtime\v4.0\Type Providers\FSharp.Data.TypeProviders.dll' --> Referenced 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Data.dll' --> Referenced 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Data.Linq.dll' contains query operator Binding session to 'C:\Users\ghogen\AppData\Local\Temp\tmp5E3C.dll'... Binding session to 'C:\Users\ghogen\AppData\Local\Temp\tmp611A.dll'... Is at least one student age 11? true count query operator Number of students: 8 last query operator. Last number: 21 lastOrDefault query operator. lastOrDefault: 21 exactlyOne query operator. Student with StudentID = 1 is Abercrombie, Kim exactlyOneOrDefault query operator. Student with StudentID = 1 is Abercrombie, Kim headOrDefault query operator. head student is Abercrombie, Kim select query operator. StudentID, Name: 1 Abercrombie, Kim StudentID, Name: 2 Abolrous, Hazen StudentID, Name: 3 Hance, Jim StudentID, Name: 4 Adams, Terry StudentID, Name: 5 Hansen, Claus StudentID, Name: 6 Penor, Lori StudentID, Name: 7 Perham, Tom StudentID, Name: 8 Peng, Yun-Feng where query operator. StudentID, Name: 5 Hansen, Claus StudentID, Name: 6 Penor, Lori StudentID, Name: 7 Perham, Tom StudentID, Name: 8 Peng, Yun-Feng minBy query operator. maxBy query operator. groupBy query operator. Age: NULL Count at that age: 1 Age: 10 Count at that age: 1 Age: 11 Count at that age: 1 Age: 12 Count at that age: 3 Age: 13 Count at that age: 1 Age: 14 Count at that age: 1 sortBy query operator. StudentID, Name: 1 Abercrombie, Kim StudentID, Name: 2 Abolrous, Hazen StudentID, Name: 4 Adams, Terry StudentID, Name: 3 Hance, Jim StudentID, Name: 5 Hansen, Claus StudentID, Name: 8 Peng, Yun-Feng StudentID, Name: 6 Penor, Lori StudentID, Name: 7 Perham, Tom sortByDescending query operator. StudentID, Name: 7 Perham, Tom StudentID, Name: 6 Penor, Lori StudentID, Name: 8 Peng, Yun-Feng StudentID, Name: 5 Hansen, Claus StudentID, Name: 3 Hance, Jim StudentID, Name: 4 Adams, Terry StudentID, Name: 2 Abolrous, Hazen StudentID, Name: 1 Abercrombie, Kim thenBy query operator. StudentID, Name: 10 Abercrombie, Kim StudentID, Name: 11 Hansen, Claus StudentID, Name: 12 Adams, Terry StudentID, Name: 12 Hance, Jim StudentID, Name: 12 Perham, Tom StudentID, Name: 13 Penor, Lori StudentID, Name: 14 Abolrous, Hazen thenByDescending query operator. StudentID, Name: 10 Abercrombie, Kim StudentID, Name: 11 Hansen, Claus StudentID, Name: 12 Perham, Tom StudentID, Name: 12 Hance, Jim StudentID, Name: 12 Adams, Terry StudentID, Name: 13 Penor, Lori StudentID, Name: 14 Abolrous, Hazen groupValBy query operator. Age: NULL Count at that age: 1 Name: Peng, Yun-Feng Age: 10 Count at that age: 1 Name: Abercrombie, Kim Age: 11 Count at that age: 1 Name: Hansen, Claus Age: 12 Count at that age: 3 Name: Hance, Jim Name: Adams, Terry Name: Perham, Tom Age: 13 Count at that age: 1 Name: Penor, Lori Age: 14 Count at that age: 1 Name: Abolrous, Hazen sumByNullable query operator Sum of ages: 84 minByNullable Minimum age: 10 maxByNullable Maximum age: 14 averageBy Average student ID: 4.500000 averageByNullable Average age: 12 find query operator Found a match with StudentID = 1 all query operator Do all students have a comma in the name? true head query operator Found the head student with StudentID = 1 nth query operator Third number is 11 skip query operator StudentID = 2 StudentID = 3 StudentID = 4 StudentID = 5 StudentID = 6 StudentID = 7 StudentID = 8 skipWhile query operator Number = 5 Number = 7 Number = 11 Number = 18 Number = 21 sumBy query operator Sum of student IDs: 36 take query operator StudentID = 1 StudentID = 2 takeWhile query operator Number = 1 Number = 5 Number = 7 sortByNullable query operator StudentID, Name, Age: 8 Peng, Yun-Feng NULL StudentID, Name, Age: 1 Abercrombie, Kim 10 StudentID, Name, Age: 5 Hansen, Claus 11 StudentID, Name, Age: 7 Perham, Tom 12 StudentID, Name, Age: 3 Hance, Jim 12 StudentID, Name, Age: 4 Adams, Terry 12 StudentID, Name, Age: 6 Penor, Lori 13 StudentID, Name, Age: 2 Abolrous, Hazen 14 sortByNullableDescending query operator StudentID, Name, Age: 2 Abolrous, Hazen 14 StudentID, Name, Age: 6 Penor, Lori 13 StudentID, Name, Age: 7 Perham, Tom 12 StudentID, Name, Age: 3 Hance, Jim 12 StudentID, Name, Age: 4 Adams, Terry 12 StudentID, Name, Age: 5 Hansen, Claus 11 StudentID, Name, Age: 1 Abercrombie, Kim 10 StudentID, Name, Age: 8 Peng, Yun-Feng NULL thenByNullable query operator StudentID, Name, Age: 1 Abercrombie, Kim 10 StudentID, Name, Age: 2 Abolrous, Hazen 14 StudentID, Name, Age: 4 Adams, Terry 12 StudentID, Name, Age: 3 Hance, Jim 12 StudentID, Name, Age: 5 Hansen, Claus 11 StudentID, Name, Age: 8 Peng, Yun-Feng NULL StudentID, Name, Age: 6 Penor, Lori 13 StudentID, Name, Age: 7 Perham, Tom 12 thenByNullableDescending query operator StudentID, Name, Age: 1 Abercrombie, Kim 10 StudentID, Name, Age: 2 Abolrous, Hazen 14 StudentID, Name, Age: 4 Adams, Terry 12 StudentID, Name, Age: 3 Hance, Jim 12 StudentID, Name, Age: 5 Hansen, Claus 11 StudentID, Name, Age: 8 Peng, Yun-Feng NULL StudentID, Name, Age: 6 Penor, Lori 13 StudentID, Name, Age: 7 Perham, Tom 12 All students: Abercrombie, Kim 1 10 Abolrous, Hazen 2 14 Hance, Jim 3 12 Adams, Terry 4 12 Hansen, Claus 5 11 Penor, Lori 6 13 Perham, Tom 7 12 Peng, Yun-Feng 8 NULL Count of students: Student count: 8 Exists. "Abercrombie, Kim" "Abolrous, Hazen" "Hance, Jim" "Adams, Terry" "Hansen, Claus" "Perham, Tom" Group by age and count NULL 1 10 1 11 1 12 3 13 1 14 1 Group value by age. NULL 1 10 1 11 1 12 3 13 1 14 1 Group students by age where age > 10. Age: 11 Hansen, Claus Age: 12 Hance, Jim Adams, Terry Perham, Tom Age: 13 Penor, Lori Age: 14 Abolrous, Hazen Group students by age and print counts of number of students at each age with more than 1 student. Age: 12 Count: 3 Group students by age and sum ages. Age: 0 Count: 1 Total years: Age: 10 Count: 1 Total years: 10 Age: 11 Count: 1 Total years: 11 Age: 12 Count: 3 Total years: 36 Age: 13 Count: 1 Total years: 13 Age: 14 Count: 1 Total years: 14 Group students by age and count number of students at each age, and display all with count > 1 in descending order of count. Age: 12 Count: 3 Select students from a set of IDs Name: Abercrombie, Kim Name: Abolrous, Hazen Name: Hansen, Claus Look for students with Name match _e% pattern and take first two. Penor, Lori Perham, Tom Look for students with Name matching [abc]% pattern. Abercrombie, Kim Abolrous, Hazen Adams, Terry Look for students with name matching [^abc]% pattern. Hance, Jim Hansen, Claus Penor, Lori Perham, Tom Peng, Yun-Feng Look for students with name matching [^abc]% pattern and select ID. 3 5 6 7 8 Using Contains as a query filter. Abercrombie, Kim Abolrous, Hazen Hance, Jim Adams, Terry Hansen, Claus Perham, Tom Searching for names from a list. Join Student and CourseSelection tables. 2 Abolrous, Hazen 2 3 Hance, Jim 3 5 Hansen, Claus 5 2 Abolrous, Hazen 2 5 Hansen, Claus 5 6 Penor, Lori 6 3 Hance, Jim 3 2 Abolrous, Hazen 2 1 Abercrombie, Kim 1 2 Abolrous, Hazen 2 5 Hansen, Claus 5 2 Abolrous, Hazen 2 3 Hance, Jim 3 2 Abolrous, Hazen 2 3 Hance, Jim 3 Left Join Student and CourseSelection tables. 1 Abercrombie, Kim 10 9 3 1 2 Abolrous, Hazen 14 1 1 2 2 Abolrous, Hazen 14 4 2 2 2 Abolrous, Hazen 14 8 3 2 2 Abolrous, Hazen 14 10 4 2 2 Abolrous, Hazen 14 12 4 2 2 Abolrous, Hazen 14 14 5 2 3 Hance, Jim 12 2 1 3 3 Hance, Jim 12 7 2 3 3 Hance, Jim 12 13 5 3 3 Hance, Jim 12 15 7 3 4 Adams, Terry 12 NULL NULL NULL 5 Hansen, Claus 11 3 1 5 5 Hansen, Claus 11 5 2 5 5 Hansen, Claus 11 11 4 5 6 Penor, Lori 13 6 2 6 7 Perham, Tom 12 NULL NULL NULL 8 Peng, Yun-Feng 0 NULL NULL NULL Join with count 15 Join with distinct. Abercrombie, Kim 2 Abercrombie, Kim 3 Abercrombie, Kim 5 Abolrous, Hazen 2 Abolrous, Hazen 5 Abolrous, Hazen 6 Abolrous, Hazen 3 Hance, Jim 2 Hance, Jim 1 Adams, Terry 2 Adams, Terry 5 Adams, Terry 2 Hansen, Claus 3 Hansen, Claus 2 Perham, Tom 3 Join with distinct and count. 15 Selecting students with age between 10 and 15. Abercrombie, Kim Abolrous, Hazen Hance, Jim Adams, Terry Hansen, Claus Penor, Lori Perham, Tom Selecting students with age either 11 or 12. Hance, Jim Adams, Terry Hansen, Claus Perham, Tom Selecting students in a certain age range and sorting. Penor, Lori 13 Perham, Tom 12 Hance, Jim 12 Adams, Terry 12 Selecting students with certain ages, taking account of possibility of nulls. Hance, Jim Adams, Terry Union of two queries. Abercrombie, Kim 10 Abolrous, Hazen 14 Hance, Jim 12 Adams, Terry 12 Hansen, Claus 11 Penor, Lori 13 Perham, Tom 12 Peng, Yun-Feng NULL Intersect of two queries. Using if statement to alter results for special value. 1 10 10 2 14 14 3 12 12 4 12 12 5 11 11 6 13 13 7 12 12 8 NULL NULL Using if statement to alter results special values. 1 10 10 2 14 14 3 12 12 4 12 12 5 11 11 6 13 13 7 12 12 8 NULL NULL Multiple table select. StudentID Name Age CourseID CourseName 1 Abercrombie, Kim 10 1 Algebra I 2 Abolrous, Hazen 14 1 Algebra I 3 Hance, Jim 12 1 Algebra I 4 Adams, Terry 12 1 Algebra I 5 Hansen, Claus 11 1 Algebra I 6 Penor, Lori 13 1 Algebra I 7 Perham, Tom 12 1 Algebra I 8 Peng, Yun-Feng NULL 1 Algebra I 1 Abercrombie, Kim 10 2 Trigonometry 2 Abolrous, Hazen 14 2 Trigonometry 3 Hance, Jim 12 2 Trigonometry 4 Adams, Terry 12 2 Trigonometry 5 Hansen, Claus 11 2 Trigonometry 6 Penor, Lori 13 2 Trigonometry 7 Perham, Tom 12 2 Trigonometry 8 Peng, Yun-Feng NULL 2 Trigonometry 1 Abercrombie, Kim 10 3 Algebra II 2 Abolrous, Hazen 14 3 Algebra II 3 Hance, Jim 12 3 Algebra II 4 Adams, Terry 12 3 Algebra II 5 Hansen, Claus 11 3 Algebra II 6 Penor, Lori 13 3 Algebra II 7 Perham, Tom 12 3 Algebra II 8 Peng, Yun-Feng NULL 3 Algebra II 1 Abercrombie, Kim 10 4 History 2 Abolrous, Hazen 14 4 History 3 Hance, Jim 12 4 History 4 Adams, Terry 12 4 History 5 Hansen, Claus 11 4 History 6 Penor, Lori 13 4 History 7 Perham, Tom 12 4 History 8 Peng, Yun-Feng NULL 4 History 1 Abercrombie, Kim 10 5 English 2 Abolrous, Hazen 14 5 English 3 Hance, Jim 12 5 English 4 Adams, Terry 12 5 English 5 Hansen, Claus 11 5 English 6 Penor, Lori 13 5 English 7 Perham, Tom 12 5 English 8 Peng, Yun-Feng NULL 5 English 1 Abercrombie, Kim 10 6 French 2 Abolrous, Hazen 14 6 French 3 Hance, Jim 12 6 French 4 Adams, Terry 12 6 French 5 Hansen, Claus 11 6 French 6 Penor, Lori 13 6 French 7 Perham, Tom 12 6 French 8 Peng, Yun-Feng NULL 6 French 1 Abercrombie, Kim 10 7 Chinese 2 Abolrous, Hazen 14 7 Chinese 3 Hance, Jim 12 7 Chinese 4 Adams, Terry 12 7 Chinese 5 Hansen, Claus 11 7 Chinese 6 Penor, Lori 13 7 Chinese 7 Perham, Tom 12 7 Chinese 8 Peng, Yun-Feng NULL 7 Chinese Multiple Joins Abercrombie, Kim Trigonometry Abercrombie, Kim Algebra II Abercrombie, Kim English Abolrous, Hazen Trigonometry Abolrous, Hazen English Abolrous, Hazen French Abolrous, Hazen Algebra II Hance, Jim Trigonometry Hance, Jim Algebra I Adams, Terry Trigonometry Adams, Terry English Adams, Terry Trigonometry Hansen, Claus Algebra II Hansen, Claus Trigonometry Perham, Tom Algebra II Multiple Left Outer Joins Abercrombie, Kim Trigonometry Abercrombie, Kim Algebra II Abercrombie, Kim English Abolrous, Hazen Trigonometry Abolrous, Hazen English Abolrous, Hazen French Abolrous, Hazen Algebra II Hance, Jim Trigonometry Hance, Jim Algebra I Adams, Terry Trigonometry Adams, Terry English Adams, Terry Trigonometry Hansen, Claus Algebra II Hansen, Claus Trigonometry Penor, Lori Perham, Tom Algebra II Peng, Yun-Feng type schema val db : schema.ServiceTypes.SimpleDataContextTypes.MyDatabase1 val student : System.Data.Linq.Table<schema.ServiceTypes.Student> val data : int list = [1; 5; 7; 11; 18; 21] type Nullable<'T when 'T : (new : unit -> 'T) and 'T : struct and 'T :> System.ValueType> with member Print : unit -> string val num : int = 21 val student2 : schema.ServiceTypes.Student val student3 : schema.ServiceTypes.Student val student4 : schema.ServiceTypes.Student val student5 : int = 1 val student6 : int = 8 val idList : int list = [1; 2; 5; 10] val idQuery : seq<int> val names : string [] = [|"a"; "b"; "c"|] module Queries = begin val query1 : System.Linq.IQueryable<string * System.Nullable<int>> val query2 : System.Linq.IQueryable<string * System.Nullable<int>> end module Queries2 = begin val query1 : System.Linq.IQueryable<string * System.Nullable<int>> val query2 : System.Linq.IQueryable<string * System.Nullable<int>> end