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 })
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)
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 ) )
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))
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"))
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)
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 ()
10.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)
11. 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 ()
12. 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")
13. 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")
14. 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 ()
Differences between SQL, LINQ, and lambda query statements