Hi Rob,
We'll be adding a few more samples over the next few days.
For now, here are some to get you going. This assumes
- You have SQLEXPRESS installed
- Nothwnd.mdf is in the same directory as your script
- You've run SQLMETAL.EXE on that database to generate your database O/R mapping in northwnd.dll
Note the LINQ query support is in the F# power pack. It is labelled experimental because some advanced SQL concepts are not yet handled (e.g. left-outer-joins)
#nowarn "57"
#r @"System.Core.dll"
#r @"System.Data.Linq.dll"
#r @"FSharp.PowerPack.Linq.dll"
#r @"northwnd.dll"
open Microsoft.FSharp.Linq
let connString = @"AttachDBFileName='" + __SOURCE_DIRECTORY__ + @"\NORTHWND.MDF';Server='.\SQLEXPRESS';user instance=true;Integrated Security=SSPI;Connection Timeout=30"
let db = new NORTHWND(connString)
// Add logging so we can see the SQL
db.Log <- System.Console.Out
// A simple select
query <@ seq { for c in db.Customers do
yield (c.ContactName,c.Address) } @>
// A simple select/take
query <@ seq { for c in db.Customers do
yield (c.ContactName,c.Address) }
|> Seq.take 4 @>
// A simple select/distinct
query <@ seq { for c in db.Customers do
yield (c.ContactName,c.Address) }
|> Seq.distinct @>
// A simple select/first
query <@ seq { for c in db.Customers do
yield (c.ContactName,c.Address) }
|> Seq.hd @>
// A simple select/filter over two tables
query <@ seq { for c in db.Customers do
for e in db.Employees do
if c.Address.Contains("Jardim") &&
c.Address.Contains("rosas") then
yield (e.LastName,c.ContactName) } @>
// A simple select/filter over two tables, i.e. a join
query <@ seq { for c in db.Customers do
for e in db.Employees do
if c.ContactName = e.LastName then
yield c.ContactName } @>
// A query over three tables
query <@ seq { for p in db.Products do
for c in db.Categories do
for s in db.Suppliers do
yield c.CategoryName, p.ProductName, s.CompanyName }
|> Seq.length @>
// Using Nullable via reflected definitions
[<ReflectedDefinition>]
let (=?!) (x : System.Nullable<'a>) (y: 'a) =
x.HasValue && x.Value = y
query <@ seq { for p in db.Products do
for c in db.Categories do
for s in db.Suppliers do
if p.CategoryID =?! c.CategoryID &&
p.SupplierID =?! s.SupplierID then
yield c.CategoryName, p.ProductName, s.CompanyName }
|> Seq.length @>
query <@ seq { for p in db.Products do
if p.CategoryID =?! 1 then
yield p.ProductName }
|> Seq.length @>
// A simple group_by.
query <@ Query.group_by
(fun (c:Customers) -> c.Address.Length)
(seq { for c in db.Customers do yield c })
|> Seq.length @>
// A simple sort_by.
query <@ Seq.sort_by
(fun (c:Customers) -> c.Address.Length)
(seq { for c in db.Customers do yield c })
|> Seq.length @>
// A simple 'exists'.
query <@ Seq.exists
(fun (c:Customers) -> c.Address.Length > 10)
(seq { for c in db.Customers do yield c }) @>
// A simple 'join'.
query <@ Query.join
(seq { for e in db.Employees do yield e })
(seq { for c in db.Customers do yield c })
(fun e -> e.Country)
(fun c -> c.Country)
(fun e c -> (e,c))
|> Seq.length @>
// Another way to write the join
query <@ seq { for e in db.Employees do
for c in db.Customers do
if e.Country = c.Country then
yield (e,c) }
|> Seq.length @>
// A simple group join
query <@ Query.group_join
(seq { for c in db.Employees do yield c })
(seq { for c in db.Customers do yield c })
(fun e -> e.Country)
(fun c -> c.Country)
(fun e cs -> (e,Seq.length cs))
|> Seq.length @>