Differences between SQL, LINQ, and LAMBDA query statements

The writing format for LINQ is as follows:
From Temp variable in collection object or database object
Where Condition expression
[ORDER BY condition]
The value queried in the Select Temp variable
[GROUP BY conditions]

The lambda expression is written in the following format:

(parameter list) = = expression or block of statements

Where: Number of parameters: can have more than one parameter, one parameter, or no parameter.

Parameter type: can be defined implicitly or explicitly.

expression or block of statements: This part is the implementation part of the function we normally write (function body).

1. Search All

Instance Code queries all records of the student table. SELECT * from Studentlinq: from    s in Students    select Slambda:    students.select (s + = s)

2 Search All by condition:

Instance Code  queries the sname, Ssex, and class columns of all records in the student table. Select Sname,ssex,class from Studentlinq: from    s in Students    select new {        s.sname,        s.ssex,        s.class    } LAMBDA:    students.select (s = = new {        SNAME = S.sname,ssex = S.ssex,class = S.class    })

3.distinct Remove the duplicate

Instance Code query teacher All units are not duplicated depart column. Select distinct depart from teacherlinq: from    T in teachers.distinct ()    Select T.departlambda:    Teachers.distinct (). Select (t = t.depart)

4. Connection query between and

Instance Code queries all records in the score table that have scores between 60 and 80. SELECT * from score where degree between and 80Linq: from    s in Scores    where S.degree >= && s.de GREE <    Select Slambda:    scores.where (        s = =                S.degree >= && S.degree < 80
   )    )

5. Filter in within range

Instance Code select * from score where degree in (85,86,88) Linq: from    s in Scores    where (            new decimal[]{85,86,88}
   ). Contains (s.degree)    Select Slambda:    scores.where (s = = new decimal[] {85,86,88}. Contains (S.degree))

6.or Conditional filtering

Example Code Query student table in the "95031" class or sex for "female" students record. SELECT * FROM student where class = ' 95031 ' or ssex= N ' female ' Linq: from    s in Students    where s.class = = ' 95031 '       | | s . CLASS = = "female"    Select Slambda:    students.where (s = = (S.class = = "95031" | | s.class = "female"))

7. Sorting

Instance Code queries all records of the student table in descending order of class. SELECT * FROM Student ORDER by Class Desclinq: from    s in Students    s.class descending    Select Slambda :    students.orderbydescending (s = s.class)

8.count () Row count query

Instance Code select COUNT (*) from student where class = ' 95031 ' Linq:    (from    s in Students        where s.class = = "95031" C3/>select s    ). Count () Lambda:    students.where (s = = S.class = = "95031")                . Select (s = = s)                    . Count ()

9.avg () Average

Example Code query ' 3-105 ' for the average score of the course. Select AVG (degree) from score where cno = ' 3-105 ' Linq:    (from        s in Scores        where s.cno = = "3-105"        Select S.degree    ). Average () Lambda:    scores.where (s = = S.cno = = "3-105")            . Select (s = = S.degree)

10. Sub-query

Instance  Code queries the highest score in the score table for student number and course number. Select distinct S.SNO,C.CNO from student as s,course as C, score as Scwhere s.sno= (select Sno from score where degree = (s Elect Max (degree) from score) and C.cno = (select CNO from score where degree = (select Max (degree) from score)) Linq:    (from        s in Students to C in Courses from SC in Scores to        maxdegree = (from SSS in Scores                        select SS S.degree                        ). Max () let        sno = (from the SS in Scores                where SS. degree = = Maxdegree                Select SS. SNO). Single (). ToString () let        cno = (from ssss in Scores                where ssss. degree = = Maxdegree                Select SSSs. CNO). Single (). ToString ()        where S.sno = = SNO && C.cno = = CNO        Select new {            S.sno,            c.cno        }    ). Distinct ()

11. Packet filtering

Example Code queries the average score of at least 5 students in the score table and 3 courses. Select AVG (degree) from score where CNO like ' 3% ' GROUP by CNO have Count (*) >=5linq: From        s in Scores        where s . CNO. StartsWith ("3")        group S by s.cno into        cc        where CC. Count () >= 5        Select CC. Average (c = c.degree) Lambda:    scores.where (s = = S.cno. StartsWith ("3"))            . GroupBy (s = = S.cno)              . Where (cc = (cc. Count () >= 5))                . Select (cc = CC. Average (c = c.degree)) Linq:sqlmethodlike can also be written like this:    s.cno. StartsWith ("3") or Sqlmethods.like (S.cno, "%3")

12. Grouping

Example Code queries the average score of at least 5 students in the score table and 3 courses. Select AVG (degree) from score where CNO like ' 3% ' GROUP by CNO have Count (*) >=5linq: From        s in Scores        where s . CNO. StartsWith ("3")        group S by s.cno into        cc        where CC. Count () >= 5        Select CC. Average (c = c.degree) Lambda:    scores.where (s = = S.cno. StartsWith ("3"))            . GroupBy (s = = S.cno)              . Where (cc = (cc. Count () >= 5))                . Select (cc = CC. Average (c = c.degree)) Linq:sqlmethodlike can also be written like this:    s.cno. StartsWith ("3") or Sqlmethods.like (S.cno, "%3")

13. Multi-Table Query

Instance Code Select Sc.sno,c.cname,sc.degree from course as c,score as SC where c.cno = sc.cnolinq: From    C in Courses    J Oin SC in Scores on    c.cno equals SC. CNO    Select New    {        sc. Sno,c.cname,sc. Degree    }lambda:    courses.join (Scores, C = c.cno,                             sc = sc. CNO,                             (c, sc) =                                        = new {                                            SNO = sc. SNO,                                            CNAME = c.cname,                                            degree = sc. Degree                                        })                . Average ()

Differences between SQL, LINQ, and LAMBDA query statements