type T =
RegExProviderType<"(? < AreaCode >
let reg = T()
let result =
T.IsMatch("425-1232345")
let r =
reg.Match("425-1232345").Group_AreaCode.Value //r = 425
|
namespace
open System
open System.Linq.Expressions
open System.Reflection
open Microsoft.FSharp.Core.CompilerServices
open Microsoft.FSharp.Collections
open System.Collections.Generic
open Microsoft.FSharp.TypeProvider.Emit
open System.Text.RegularExpressions
type HelperClass() =
type DebugType() 1=
inherit System.Object()
[
type public RegExTypeProvider() as this =
inherit
TypeProviderForNamespaces()
let thisAssembly =
Assembly.GetExecutingAssembly()
let rootNamespace =
"
let baseType =
typeof
let regexType =
let
t = ProvidedTypeDefinition2(thisAssembly,
rootNamespace, "
t.DefineStaticParameters([ProvidedStaticParameter("pattern",typeof
fun
theTypeNameOfThisInstantiation args ->
match
args with
| [| :? string as template |]4 ->
t
|
_ -> failwith "need generic definition"
)
//add a method or property
t
do
this.AddNamespace(rootNamespace, [regexType] )
[
do()
|
//add isMatch function
let parameters = [
ProvidedParameter("data", typeof
let mi =
ProvidedMethod("IsMatch", parameters, typeof
mi.AddXmlDoc("Test if
the input string matches the pattern")
mi.IsStaticMethod <- true
mi.InvokeCode <- fun args
-> <@@ (Regex.IsMatch((%%args.[0]:string), template)) @@>
t.AddMember mi
|
//add RawRegex property
let pi =
ProvidedProperty("RawRegex", typeof
mi.AddXmlDoc("Return raw
regex")
pi.IsStatic <- true
pi.GetterCode <- (fun args
-> <@@ Regex(template) @@>)
t.AddMember pi
|
//generate new type from pattern
let
matchType =
let
t = ProvidedTypeDefinition("MatchEx", Some(typeof
t
|
//add properties for matchEx
let reg = Regex(template)
for name in reg.GetGroupNames()
do
if name
<> "0" then
let propertyName = sprintf "Group_%s" name
let pi = ProvidedProperty(propertyName, typeof
pi.GetterCode <- fun args -> <@@ ( (%%args.[0]:Match).Groups.[name]
) @@>
matchType.AddMember(pi)
|
//add match function
let parameters = [ ProvidedParameter("data", typeof
let mi = ProvidedMethod("Match", parameters, matchType)
mi.AddXmlDoc("Match function")
mi.InvokeCode <- fun args -> <@@
(Regex(template).Match((%%args.[1]:string))) @@>
t.AddMember mi
|
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 };
|
