Using the type provider sample

Category:
TypeProviders.SqlDataConnection
Description: type provider sample. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]
type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider1() =
let db = T0.GetDataContext();
let q2 = query {
for n in db.Course do
select n.CourseName
}
q2 |> Seq.iter (fun n -> printfn "%A" n)
Assert.AreEqual(q2 |> Seq.length, 4)

Execution Result:
"Math"
"Physics"
"Biology"
"English"
both elements are equal

TypeProvider query IN case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample using IN. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider2() =
let db = T0.GetDataContext();
let q2 = query {
for n in db.Course do
join (for e in db.CourseSelection -> n.CourseID = e.CourseID.Value)
select (n.CourseName, e.StudentID)
}
let names = [|"Lisa";"Brent";"Jimmy"|]
let q = query {
for n in db.Student do
if names.Contains (n.Name) then select n
}
q |> Seq.iter (fun n -> printfn "%A" n.Name)
Assert.AreEqual(q |> Seq.length, 2)

Execution Result:
"Lisa"
"Brent"
both elements are equal

TypeProvider query EXISTS case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample using exists . Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider3() =
let db = T0.GetDataContext();
let q =
query {
for i in db.Student do
where (query { for c in db.CourseSelection do
exists (c.StudentID = 1) })
select i }
q |> Seq.iter (fun n -> printfn "%A" n.Name)
Assert.AreEqual(q |> Seq.length, 9)

Execution Result:
"Lisa"
"Brent"
"Anita"
"Ken"
"Cathy"
"Tom"
"Zeoy"
"Mark"
"John"
both elements are equal

TypeProvider query GroupBy case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample using GroupBy. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider4() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
groupby n.Age into g
where (g.Key.HasValue && g.Key.Value > 1)
select (g.Key, g.Count())
}
q |> Seq.iter (fun n -> printfn "%A" n)
Assert.AreEqual(q |> Seq.length, 4)

Execution Result:
(20, 2)
(21, 2)
(22, 3)
(23, 1)
both elements are equal

TypeProvider query GroupBy and OrderBy DESC case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample using GroupBy and OrderBy DESC. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider5() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
groupby n.Age into g
where (g.Count() > 1)
sortByDescending ( g.Count() )
select (g.Key, g.Count())
}
q |> Seq.iter (fun n -> printfn "%A" n)
Assert.AreEqual(q |> Seq.length, 3)

Execution Result:
(22, 3)
(20, 2)
(21, 2)
both elements are equal

TypeProvider query TOP case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample using TOP. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider6() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
where (SqlMethods.Like( n.Name, "_a") )
select n
take 2
}
q |> Seq.iter (fun n -> printfn "%s" n.Name)
Assert.AreEqual(q |> Seq.length, 0)

Execution Result:
both elements are equal

TypeProvider query LEFT join case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample: LEFT join. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider7() =
let db = T0.GetDataContext();
let q2 = query {
for n in db.Student do
join (for e in db.Course -> n.StudentID = e.CourseID)
select n
}

let q = query {
for n in q2.DefaultIfEmpty() do
select n
}
q |> Seq.iter (fun n -> printfn "%s" n.Name)
Assert.AreEqual(q |> Seq.length, 4)

Execution Result:
Lisa
Brent
Anita
Ken
both elements are equal

TypeProvider query COUNT case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample: COUNT. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider8() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
count
}
printfn "count is %d" q
Assert.AreEqual(q, 9)

Execution Result:
count is 9
both elements are equal

TypeProvider query DISTINCT case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample: DISTINCT. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider9() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
join (for e in db.CourseSelection -> n.StudentID = e.StudentID)
distinct
}
q |> Seq.iter (fun (s, c) -> printfn "(Name=%s, CourseID=%d)" s.Name c.CourseID.Value)
Assert.AreEqual(q |> Seq.length, 6)

Execution Result:
(Name=Lisa, CourseID=1)
(Name=Brent, CourseID=1)
(Name=Brent, CourseID=2)
(Name=Anita, CourseID=1)
(Name=Anita, CourseID=2)
(Name=Anita, CourseID=3)
both elements are equal

TypeProvider query DISTINCT COUNT case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample: DISTINCT COUNT. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider10() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
join (for e in db.CourseSelection -> n.StudentID = e.StudentID)
distinct
count
}
printfn "distinct count = %d" q
Assert.AreEqual(q, 6)

Execution Result:
distinct count = 6
both elements are equal

TypeProvider query TOP and Nullable case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample: TOP and nullable. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider11() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
where ((n.Age.HasValue && n.Age.Value =21) || (n.Age.HasValue && n.Age.Value=22))
select n.Name
take 2
}
q |> Seq.iter (fun n -> printfn "%s" n)
Assert.AreEqual(q |> Seq.length, 2)

Execution Result:
Lisa
Brent
both elements are equal

TypeProvider query UNION case

Category:
TypeProviders.SqlDataConnection
Description: type provider sample: UNION. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider12() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
select (n.Name, n.Age)
}
let q2 = query {
for n in db.Student do
select (n.Name, n.Age)
}
let q3 = q.Union (q2)
q |> Seq.iter (fun n -> printfn "%A" n)
Assert.AreEqual(q |> Seq.length, 9)

Execution Result:
("Lisa", 21)
("Brent", 22)
("Anita", 20)
("Ken", 22)
("Cathy", 22)
("Tom", 20)
("Zeoy", 21)
("Mark", 23)
("John", null)
both elements are equal

TypeProvider query CASE

Category:
TypeProviders.SqlDataConnection
Description: type provider sample: CASE. Use the CreateFSharpSampleDatabase.sql to setup your database tables.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider13() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
select (if n.Age.HasValue && n.Age.Value=1 then (n.StudentID, System.Nullable(n.Age.Value*100), n.Age) else (n.StudentID, n.Age, n.Age))
}
q |> Seq.iter (fun n -> printfn "%A" n)
Assert.AreEqual(q |> Seq.length, 9)

Execution Result:
(1, 21, 21)
(2, 22, 22)
(3, 20, 20)
(4, 22, 22)
(5, 22, 22)
(6, 20, 20)
(7, 21, 21)
(8, 23, 23)
(9, null, null)
both elements are equal

Having and groupby

Category:
TypeProviders.SqlDataConnection
Description: Use having and groupby clause
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider15() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
groupby n.Age into g
where (g.Count()>1)
select (g.Key, g.Count())
}
q |> Seq.iter (fun (key, count) -> printfn "key=%A count=%d" key count)

Execution Result:
key=20 count=2
key=21 count=2
key=22 count=3

Cartesian product

Category:
TypeProviders.SqlDataConnection
Description: Cartesian product on two tables
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider14() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
for m in db.Course do
select (n, m)
}
q |> Seq.iter (fun (student, course) -> printfn "%A %A" student.Name course.CourseName)

Execution Result:
"Lisa" "Math"
"Brent" "Math"
"Anita" "Math"
"Ken" "Math"
"Cathy" "Math"
"Tom" "Math"
"Zeoy" "Math"
"Mark" "Math"
"John" "Math"
"Lisa" "Physics"
"Brent" "Physics"
"Anita" "Physics"
"Ken" "Physics"
"Cathy" "Physics"
"Tom" "Physics"
"Zeoy" "Physics"
"Mark" "Physics"
"John" "Physics"
"Lisa" "Biology"
"Brent" "Biology"
"Anita" "Biology"
"Ken" "Biology"
"Cathy" "Biology"
"Tom" "Biology"
"Zeoy" "Biology"
"Mark" "Biology"
"John" "Biology"
"Lisa" "English"
"Brent" "English"
"Anita" "English"
"Ken" "English"
"Cathy" "English"
"Tom" "English"
"Zeoy" "English"
"Mark" "English"
"John" "English"

TypeProvider average

Category:
TypeProviders.SqlDataConnection
Description: TypeProvider average
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider16() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
let f = float(n.Age.GetValueOrDefault())
averageBy f
}
q |> printfn "average age = %A"

Execution Result:
average age = 19.0

TypeProvider sum

Category:
TypeProviders.SqlDataConnection
Description: Use sum to get the total age for all the students
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider17() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
sumByNullable n.Age
}
q |> printfn "sum of age = %A"

Execution Result:
sum of age = 171

TypeProvider min/max

Category:
TypeProviders.SqlDataConnection
Description: use min and max to get the youngest and oldest students.
Code:
[<Generate>]

type T0 = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection< "Data Source=localhost;Initial Catalog=FSharpSample;User ID=sa;Password=FSharpSample1234" >

let TypeProvider18() =
let db = T0.GetDataContext();
let q = query {
for n in db.Student do
minByNullable n.Age
}
q |> printfn "min age = %A"

let q = query {
for n in db.Student do
maxByNullable n.Age
}
q |> printfn "max age = %A"


Execution Result:
min age = 20
max age = 23

Last edited Sep 13, 2011 at 7:37 PM by ttliu2000, version 5

Comments

No comments yet.