1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120

 

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