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 in  Students       Select  slambda:    = = 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 in Students     Select New {        s.sname,        s.ssex,        s.class    }lambda:    new  {        = S. Sname,ssex = S.ssex,class = s.class    })

View Code

3.distinct Remove the duplicate

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

View Code

4. Connection query between and

instance Code queries all records in the score table that have scores between 60 and 80. Select* fromScorewheredegree between -and theLinq: fromSinchScoreswhereS.degree >= -&& S.degree < the    SelectSLambda:Scores.Where (S=(S.degree>= -&& S.degree < the             )    )

View Code

5. Filter in within range

instance CodeSelect* fromScorewhereDegreeinch( -, the, the) Linq: fromSinchScoreswhere (            New decimal[]{ -, the, the}          ). Contains (S.degree)SelectSLambda:Scores.Where (S=NewDecimal[] { -, the, the}. Contains (S.degree))

View Code

6.or Conditional filtering

instance Code query in student table"95031"class or gender for"female"students ' records. Select* fromStudentwhere class='95031'or ssex= N'female'Linq: fromSinchStudentswhereS.class = ="95031"|| S.class = ="female"    SelectSLambda:Students.Where (S= = (S.class = ="95031"|| S.class = ="female"))

View Code

7. Sorting

instance Code queries all records of the student table in descending order of class.  Select from student ORDER by Class Desclinq:      Students      s.class Descending    Select  slambda:     = S.class)

View Code

8.count () Row count query

instance CodeSelectCOUNT (*) fromStudentwhere class='95031'Linq: ( fromSinchStudentswhereS.class = ="95031"        Selects). Count () Lambda:Students.Where (s= = S.class = ="95031" )                . Select (S=s). Count ()

View Code

9.avg () Average

Instance Code query'3-105'The average number of courses. SelectAVG (degree) fromScorewhereCNO ='3-105'Linq: ( fromSinchScoreswhereS.cno = ="3-105"        Selects.degree). Average () Lambda:Scores.Where (s= = S.cno = ="3-105")            . Select (S= S.degree)

View Code

10. Sub-query

instance Code queries the highest score in the score table for student number and course number. SelectDistinct S.SNO,C.CNO fromStudent asS,course asC, score asSCwhereS.sno= (SelectSno fromScorewheredegree = (SelectMax (degree) fromscore)) and C.cno= (SelectCno fromScorewheredegree = (SelectMax (degree) fromscore)) Linq: ( fromSinchStudents fromCinchCourses fromScinchScores let Maxdegree= ( fromSssinchScoresSelectSSS. degree). Max () Let Sno= ( fromSsinchScoreswhereSs. degree = =MaxdegreeSelectSS. SNO). Single (). ToString () Let CNO= ( fromSSSsinchScoreswhereSSSs. degree = =MaxdegreeSelectssss. CNO). Single (). ToString ()whereS.sno = = SNO && C.cno = =CNOSelect New{s.sno, c.cno}). Distinct ()

View Code

11. Packet filtering

example Code queries the average score of at least 5 students in the score table and 3 courses. SelectAVG (degree) fromScorewhereCNO like'3%'GROUP BY Cno have Count (*) >=5Linq: fromSinchScoreswhereS.cno. StartsWith ("3") group S by s.cno into CCwhereCc. Count () >=5        SelectCc. 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")

View Code

12. Grouping

example Code queries the average score of at least 5 students in the score table and 3 courses. SelectAVG (degree) fromScorewhereCNO like'3%'GROUP BY Cno have Count (*) >=5Linq: fromSinchScoreswhereS.cno. StartsWith ("3") group S by s.cno into CCwhereCc. Count () >=5        SelectCc. 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")

View Code

13. Multi-Table Query

instance CodeSelectSc.sno,c.cname,sc.degree fromCourse asC,score asScwhereC.cno =sc.cnolinq: fromCinchCourses Join SCinchScores on C.cno equals SC. CNOSelect 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 ()

View Code

Differences between SQL, LINQ, and lambda query statements