Sunday, September 25, 2011

Sample in Detail - SQL Type Provider and SQL query

SQL TypeProvider samples are recently removed from the sample package. Those samples are trying to map SQL query to F# query. SQL query has been there for decades and large number of persons are using it every day.



SQL Statement (not case-sensitive)
F# query (case-sensitive)
C# query (case-sensitive)
1
select * from Student
where exists
(select * from CourseSelection
where CourseSelection.StudentID = 1)
let q =
    query {
        for i in db.Students do
        where (query { for c in db.CourseSelection do
                       exists (c.StudentID = 1) })
        select i }
var q = from n in db.Students
                    where (from m in db.CourseSelections
                           where m.StudentID == 1
                           select m).Any()
                    select n;
1a
select Student.Age, COUNT(*)
from Student
group by Student.Age
having student.Age > 1
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())
}

var q = from n in db.Students
                    where n.Age > 1
                    group n by n.Age into g
                    select new { g.Key, Count = g.Count() };
1b
select Student.Age, COUNT(*)
from Student
group by Student.Age
having COUNT(*) > 1
let q = query {
        for n in db.Student do
        groupBy n.Age into g
        where (g.Count()>1)
        select (g.Key, g.Count())
}

var q = from n in db.Students
                    group n by n.Age into g
                    where g.Count() > 1
                    select new { g.Key, Count = g.Count() };
1c
select Student.Age, COUNT(*), SUM(Student.Age) as total
from Student
group by Student.Age
let q = query {
        for n in db.Student do
        groupBy n.Age into g       
        let t = query { for n in g do sumByNullable n.Age }
        select (g.Key, g.Count(), t)
}
var q = from n in db.Students
                    group n by n.Age into g
                    where g.Count() > 1
                    select new { Age=g.Key, Sum=g.Sum(s=>s.Age), Count = g.Count() };
1d
select Student.Age, COUNT(*) as MyCount
from Student
group by Student.Age
having COUNT(*) > 1
order by COUNT(*) desc
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())       
}
var q = from n in db.Students
                    group n by n.Age into g
                    where g.Count()>1
                    orderby g.Count() descending
                    select new { Age = g.Key, Count=g.Count() };
2
select top 2 * from Student
where student.Name like '_a'
open System.Data.Linq.SqlClient;

let q = query {
    for n in db.Student do
    where (SqlMethods.Like( n.Name, "_a") )
    select n
    take 2  
    }
var q = (from n in db.Students                   
                    where SqlMethods.Like(n.Name, "_a")
                    select n).Take(2);
3
select * from Student
where student.Name like '[abc]%'
open System.Data.Linq.SqlClient;

let q = query {
    for n in db.Student do
    where (SqlMethods.Like( n.Name, "[abc]%") )
    select n   
    }
var q = from n in db.Students                   
                    where SqlMethods.Like(n.Name, "[abc]%")
                    select n;
4
select * from Student
where student.Name like '[^abc]%'
open System.Data.Linq.SqlClient;

let q = query {
    for n in db.Student do
    where (SqlMethods.Like( n.Name, "[^abc]%") )
    select n   
    }
var q = from n in db.Students                   
                    where SqlMethods.Like(n.Name, "[^abc]%")
                    select n;
5
select StudentID as ID from Student
where student.Name like '[^abc]%'
open System.Data.Linq.SqlClient;

let q = query {
    for n in db.Student do
    where (SqlMethods.Like( n.Name, "[^abc]%") )
    select n.StudentID   
    }
var q = from n in db.Students                   
                    where SqlMethods.Like(n.Name, "[^abc]%")
                    select n.StudentID;
6
select * from
Student left join CourseSelection
on Student.StudentID = CourseSelection.StudentID
let q2 = query {
        for n in db.Student do
        join (for e in db.StudentID -> n.StudentID = e.CourseID.Value)
        select n
    }

let q = query {
    for n in q2.DefaultIfEmpty() do
    select n   
    }
var q = from n in db.CourseSelections
                    join m in db.Students
                    on n.StudentID equals m.StudentID into g
                    from x in g
                    select x;
7
select * from
Student right join CourseSelection
on Student.StudentID = CourseSelection.StudentID
No match, you can reverse two tables.
No match, you can reverse two tables.
8
Select * from student
let q = query {
        for n in db.Student do
        select n
    }
var q = from n in db.Students
                    select n;
9
select count(*) from student
let q = query {
        for n in db.Student do       
        count
    }
var q = (from n in db.Students
                     select n).Count();
10
select COUNT(*) from
Student join CourseSelection
on Student.StudentID = CourseSelection.StudentID
let q = query {
        for n in db.Student do
        join (for e in db.CourseSelection -> n.StudentID = e.StudentID)
        count       
    }
var q = (from n in db.CourseSelections
                    join m in db.Students
                    on n.StudentID equals m.StudentID into g
                    from x in g
                    select x).Count();
11
select distinct StudentID from CourseSelection
let q = query {
        for n in db.Student do
        join (for e in db.CourseSelection -> n.StudentID = e.StudentID)
        distinct       
    }
var q = (from n in db.CourseSelections
                     select n).Distinct();
12
select distinct count(StudentID) from CourseSelection
let q = query {
        for n in db.Student do
        join (for e in db.CourseSelection -> n.StudentID = e.StudentID)
        distinct
        count      
    }
var q = (from n in db.CourseSelections
                     select n).Distinct().Count();
13
select * from Student
where Student.Name like '%A%'
(not case-sensitive)
let q = query {
        for n in db.Student do
        where (n.Name.Contains("a"))
        select n
    }
(not case-sensitive)
var q = from n in db.Students                   
                    where SqlMethods.Like(n.Name, "%A%")
                    select n;
14

select
* from Student
where Student.Name in ('A', 'B', 'C')

(not case-sensitive)
Open System.Linq  //please do not forget to add this line

let names = [|"a";"b";"c"|]
let q = query {
    for n in db.Student do
    if names.Contains (n.Name) then select n }
 (not case-sensitive)
var list = new List<string>(new string[] { "a", "b", "c" });
            var q = from n in db.Students
                     where list.Contains(n.Name)
                     select n;
15
select * from Student
where Student.Age between 2 and 5
let q = query {
        for n in db.Student do
        where (n.Age.Value >=1 && n.Age.Value <5)
        select n
    }
var q = from n in db.Students
                     where n.Age>=1 && n.Age<5
                     select n;
16
select * from Student
where Student.Age =1 OR Student.Age = 2
let q = query {
        for n in db.Student do
        where (n.Age.Value =1 || n.Age.Value =2)
        select n
    }
var q = from n in db.Students
                     where n.Age==1 || n.Age==2
                     select n;
17
select * from Student
where Student.Age =1 OR Student.Age = 2
order by Student.Name desc
let db = T.GetDataContext();
let q = query {
        for n in db.Student do
        where (n.Age.Value =1 || n.Age.Value =2)
        sortByNullableDescending n.Age
        select n
    }
var q = from n in db.Students
                    where n.Age == 1 || n.Age == 2
                    orderby n.Age descending
                    select n;
18
select top 2 student.Name from Student
where Student.Age =1 OR Student.Age = 2
order by Student.Name desc

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
    }
var q = (from n in db.Students
                    where n.Age == 1 || n.Age == 2
                    orderby n.Name descending
                    select n).Take(2);
19
select * from Student
union
select * from lastStudent
let q = query {
        for n in db.Student do
        select (n.Name, n.Age)
    }

let q2 = query {
        for n in db.LastStudent do
        select (n.Name, n.Age)
        }

let q3 = q.Union (q2)
var q = (from n in db.Students
                     select n).Union(
                    (from n in db.LastStudents
                     select n));
20
select Student.Age, COUNT(*) from Student
group by Student.Age
Open System.Linq  //please do not forget to add this line

let q = query {
        for n in db.Student do
        groupBy n.Age into g
        select (g.Key, g.Count())
}

OR

let q = query {
        for n in db.Student do
        groupValBy n.Age n.Age into g
        select (g.Key, g.Count())
    }
var q = from n in db.Students
                    group n by n.Age into g
                    select new { Age=g.Key, Count=g.Count() }
21
select student.StudentID,
      case Student.Age
            when 1 then Student.Age * 100
            else student.Age
      end,
      student.Age
from Student
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))
    }
var q = from n in db.Students                   
                    select new { n.StudentID,  Age= n.Age==1 ? n.Age*100 : n.Age};
22
select * from Student
INTERSECT
select * from LastStudent
let q = query {
        for n in db.Student do
        select (n.Name, n.Age)
    }

let q2 = query {
        for n in db.LastStudent do
        select (n.Name, n.Age)
        }

let q3 = q. Intersect (q2)
var q = (from n in db.Students
                     select n).Intersect(
                    (from n in db.LastStudents
                     select n));
23
select * from Student, Course
let q = query {
        for n in db.Student do
        for m in db.Course do
        select (n, m)
}
(need more work to flatten the structure and grab out all fields)
var q = from n in db.Students
                    from m in db.Courses
                    select new { n, m };


No comments: