Duration (Pacific Standard Time):
To (Pacific Standard Time):
Impact:
  • None
User Action:
  • None
Visual Studio 2012 - Visual F#

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 }

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.

No code example is currently available or this language may not be supported.

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

Student Course 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.

No code example is currently available or this language may not be supported.
Table 1. Query Operators

Operator

Description

contains

Determines whether the selected elements include a specified element.

No code example is currently available or this language may not be supported.

count

Returns the number of selected elements.

No code example is currently available or this language may not be supported.

last

Selects the last element of those selected so far.

No code example is currently available or this language may not be supported.

lastOrDefault

Selects the last element of those selected so far, or a default value if no element is found.

No code example is currently available or this language may not be supported.

exactlyOne

Selects the single, specific element selected so far. If multiple elements are present, an exception is thrown.

No code example is currently available or this language may not be supported.

exactlyOneOrDefault

Selects the single, specific element of those selected so far, or a default value if that element is not found.

No code example is currently available or this language may not be supported.

headOrDefault

Selects the first element of those selected so far, or a default value if the sequence contains no elements.

No code example is currently available or this language may not be supported.

select

Projects each of the elements selected so far.

No code example is currently available or this language may not be supported.

where

Selects elements based on a specified predicate.

No code example is currently available or this language may not be supported.

minBy

Selects a value for each element selected so far and returns the minimum resulting value.

No code example is currently available or this language may not be supported.

maxBy

Selects a value for each element selected so far and returns the maximum resulting value.

No code example is currently available or this language may not be supported.

groupBy

Groups the elements selected so far according to a specified key selector.

No code example is currently available or this language may not be supported.

sortBy

Sorts the elements selected so far in ascending order by the given sorting key.

No code example is currently available or this language may not be supported.

sortByDescending

Sorts the elements selected so far in descending order by the given sorting key.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

groupValBy

Selects a value for each element selected so far and groups the elements by the given key.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

averageBy

Selects a value for each element selected so far and returns the average of these values.

No code example is currently available or this language may not be supported.

distinct

Selects distinct elements from the elements selected so far.

No code example is currently available or this language may not be supported.

exists

Determines whether any element selected so far satisfies a condition.

No code example is currently available or this language may not be supported.

find

Selects the first element selected so far that satisfies a specified condition.

No code example is currently available or this language may not be supported.

all

Determines whether all elements selected so far satisfy a condition.

No code example is currently available or this language may not be supported.

head

Selects the first element from those selected so far.

No code example is currently available or this language may not be supported.

nth

Selects the element at a specified index amongst those selected so far.

No code example is currently available or this language may not be supported.

skip

Bypasses a specified number of the elements selected so far and then selects the remaining elements.

No code example is currently available or this language may not be supported.

skipWhile

Bypasses elements in a sequence as long as a specified condition is true and then selects the remaining elements.

No code example is currently available or this language may not be supported.

sumBy

Selects a value for each element selected so far and returns the sum of these values.

No code example is currently available or this language may not be supported.

take

Selects a specified number of contiguous elements from those selected so far.

No code example is currently available or this language may not be supported.

takeWhile

Selects elements from a sequence as long as a specified condition is true, and then skips the remaining elements.

No code example is currently available or this language may not be supported.

sortByNullable

Sorts the elements selected so far in ascending order by the given nullable sorting key.

No code example is currently available or this language may not be supported.

sortByNullableDescending

Sorts the elements selected so far in descending order by the given nullable sorting key.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

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

Transact-SQL (not case sensitive)

F# Query Expression (case sensitive)

Select all fields from table.

SELECT * FROM Student
No code example is currently available or this language may not be supported.

Count records in a table.

SELECT COUNT(*) FROM Student
No code example is currently available or this language may not be supported.

EXISTS

SELECT * FROM Student
WHERE EXISTS 
(SELECT * FROM CourseSelection
WHERE CourseSelection.StudentID = Student.StudentID)
No code example is currently available or this language may not be supported.

Grouping

SELECT Student.Age, COUNT(*) FROM Student
GROUP BY Student.Age
No code example is currently available or this language may not be supported.

Grouping with condition.

SELECT Student.Age, COUNT(*) 
FROM Student
GROUP BY Student.Age
HAVING student.Age > 10
No code example is currently available or this language may not be supported.

Grouping with count condition.

SELECT Student.Age, COUNT(*)
FROM Student
GROUP BY Student.Age
HAVING COUNT(*) > 1
No code example is currently available or this language may not be supported.

Grouping, counting, and summing.

SELECT Student.Age, COUNT(*), SUM(Student.Age) as total
FROM Student
GROUP BY Student.Age
No code example is currently available or this language may not be supported.

Grouping, counting, and ordering by count.

SELECT Student.Age, COUNT(*) as myCount
FROM Student
GROUP BY Student.Age
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
No code example is currently available or this language may not be supported.

IN a set of specified values

SELECT *
FROM Student
WHERE Student.StudentID IN (1, 2, 5, 10)
No code example is currently available or this language may not be supported.

LIKE and TOP.

-- '_e%' matches strings where the second character is 'e'
SELECT TOP 2 * FROM Student
WHERE Student.Name LIKE '_e%'
No code example is currently available or this language may not be supported.

LIKE with pattern match set.

-- '[abc]%' matches strings where the first character is
-- 'a', 'b', 'c', 'A', 'B', or 'C'
SELECT * FROM Student
WHERE Student.Name LIKE '[abc]%'
No code example is currently available or this language may not be supported.

LIKE with set exclusion pattern.

-- '[^abc]%' matches strings where the first character is
-- not 'a', 'b', 'c', 'A', 'B', or 'C'
SELECT * FROM Student
WHERE Student.Name LIKE '[^abc]%'
No code example is currently available or this language may not be supported.

LIKE on one field, but select a different field.

SELECT StudentID AS ID FROM Student
WHERE Student.Name LIKE '[^abc]%'
No code example is currently available or this language may not be supported.

LIKE , with substring search.

SELECT * FROM Student
WHERE Student.Name like '%A%'
No code example is currently available or this language may not be supported.

Simple JOIN with two tables.

SELECT * FROM Student
JOIN CourseSelection 
ON Student.StudentID = CourseSelection.StudentID
No code example is currently available or this language may not be supported.

LEFT JOIN with two tables.

SELECT * FROM 
Student LEFT JOIN CourseSelection 
ON Student.StudentID = CourseSelection.StudentID
No code example is currently available or this language may not be supported.

JOIN with COUNT

SELECT COUNT(*) FROM 
Student JOIN CourseSelection 
ON Student.StudentID = CourseSelection.StudentID
No code example is currently available or this language may not be supported.

DISTINCT

SELECT DISTINCT StudentID FROM CourseSelection
No code example is currently available or this language may not be supported.

Distinct count.

SELECT DISTINCT COUNT(StudentID) FROM CourseSelection
No code example is currently available or this language may not be supported.

BETWEEN

SELECT * FROM Student
WHERE Student.Age BETWEEN 10 AND 15
No code example is currently available or this language may not be supported.

OR

SELECT * FROM Student
WHERE Student.Age =11 OR Student.Age = 12
No code example is currently available or this language may not be supported.

OR with ordering

SELECT * FROM Student
WHERE Student.Age =12 OR Student.Age = 13
ORDER BY Student.Age DESC
No code example is currently available or this language may not be supported.

TOP , OR, and ordering.

SELECT TOP 2 student.Name FROM Student
WHERE Student.Age = 11 OR Student.Age = 12
ORDER BY Student.Name DESC
No code example is currently available or this language may not be supported.

UNION of two queries.

SELECT * FROM Student
UNION
SELECT * FROM lastStudent
No code example is currently available or this language may not be supported.

Intersection of two queries.

SELECT * FROM Student
INTERSECT
SELECT * FROM LastStudent
No code example is currently available or this language may not be supported.

CASE condition.

SELECT student.StudentID, 
    CASE Student.Age
        WHEN -1 THEN 100
        ELSE Student.Age
        END,
        Student.Age
from Student
No code example is currently available or this language may not be supported.

Multiple cases.

SELECT Student.StudentID, 
    CASE Student.Age
        WHEN -1 THEN 100
        WHEN 0 THEN 1000
        ELSE Student.Age
    END,
    Student.Age
FROM Student
No code example is currently available or this language may not be supported.

Multiple tables.

SELECT * FROM Student, Course
No code example is currently available or this language may not be supported.

Multiple joins.

SELECT Student.Name, Course.CourseName
FROM Student
JOIN CourseSelection
ON CourseSelection.StudentID = Student.StudentID
JOIN Course
ON Course.CourseID = CourseSelection.CourseID
No code example is currently available or this language may not be supported.

Multiple left outer joins.

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
No code example is currently available or this language may not be supported.

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.

No code example is currently available or this language may not be supported.

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