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
}
|
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 };
|
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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment