SQL LinqToSql Lambda

1、查詢Student表中的所有記錄的Sname、Ssex和Class列。selectsname,ssex,classfromstudentLinq:fromsinStudentsselectnew{s.SNAME,s.SSEX,s.CLASS}Lambda:Students.Select(s=>new{SNAME=s.SNAME,SSEX=s.SSEX,CLASS=s.CLASS})2、查詢教師所有的單位即不重復的Depart列。selectdistinctdepartfromteacherLinq:fromtinTeachers.Distinct()selectt.DEPARTLambda:Teachers.Distinct().Select(t=>t.DEPART)3、查詢Student表的所有記錄。select*fromstudentLinq:fromsinStudentsselectsLambda:Students.Select(s=>s)4、查詢Score表中成績在60到80之間的所有記錄。select*fromscorewheredegreebetween60and80Linq:fromsinScoreswheres.DEGREE>=60&&s.DEGREE<80selectsLambda:Scores.Where(s=>(s.DEGREE>=60&&s.DEGREE<80))5、查詢Score表中成績為85，86或88的記錄。select*fromscorewheredegreein(85,86,88)Linq:InfromsinScoreswhere(newdecimal[]{85,86,88}).Contains(s.DEGREE)selectsLambda:Scores.Where(s=>newDecimal[]{85,86,88}.Contains(s.DEGREE))NotinfromsinScoreswhere!(newdecimal[]{85,86,88}).Contains(s.DEGREE)selectsLambda:Scores.Where(s=>!(newDecimal[]{85,86,88}.Contains(s.DEGREE)))Any()應用：雙表進行Any時，必須是主鍵為(String)CustomerDemographicsCustomerTypeID（String）CustomerCustomerDemos(CustomerIDCustomerTypeID)(String)一個主鍵與二個主建進行Any(或者是一對一關鍵進行Any)不可，以二個主鍵于與一個主鍵進行AnyfromeinCustomerDemographicswhere!e.CustomerCustomerDemos.Any()selectefromcinCategorieswhere!c.PRoducts.Any()selectc6、查詢Student表中"95031"班或性別為"女"的同學記錄。select*fromstudentwhereclass='95031'orssex=N'女'Linq:fromsinStudentswheres.CLASS=="95031"||s.CLASS=="女"selectsLambda:Students.Where(s=>(s.CLASS=="95031"||s.CLASS=="女"))7、以Class降序查詢Student表的所有記錄。select*fromstudentorderbyClassDESCLinq:fromsinStudentsorderbys.CLASSdescendingselectsLambda:Students.OrderByDescending(s=>s.CLASS)8、以Cno升序、Degree降序查詢Score表的所有記錄。select*fromscoreorderbyCnoASC,DegreeDESCLinq:(這里CnoASC在linq中要寫在最外面)fromsinScoresorderbys.DEGREEdescendingorderbys.CNOascendingselectsLambda:Scores.OrderByDescending(s=>s.DEGREE).OrderBy(s=>s.CNO)9、查詢"95031"班的學生人數。selectcount(*)fromstudentwhereclass='95031'Linq:(fromsinStudentswheres.CLASS=="95031"selects).Count()Lambda:Students.Where(s=>s.CLASS=="95031").Select(s=>s).Count()10、查詢Score表中的最高分的學生學號和課程號。selectdistincts.Sno,c.Cnofromstudentass,courseasc,scoreasscwheres.sno=(selectsnofromscorewheredegree=(selectmax(degree)fromscore))andc.cno=(selectcnofromscorewheredegree=(selectmax(degree)fromscore))Linq:(fromsinStudentsfromcinCoursesfromscinScoresletmaxDegree=(fromsssinScoresselectsss.DEGREE).Max()letsno=(fromssinScoreswheress.DEGREE==maxDegreeselectss.SNO).Single().ToString()letcno=(fromssssinScoreswheressss.DEGREE==maxDegreeselectssss.CNO).Single().ToString()wheres.SNO==sno&&c.CNO==cnoselectnew{s.SNO,c.CNO}).Distinct()操作時問題？執行時報錯：wheres.SNO==sno（這行報出來的）運算符"=="無法應用于"string"和"System.Linq.IQueryable<string>"類型的操作數解決：原：letsno=(fromssinScoreswheress.DEGREE==maxDegreeselectss.SNO).ToString()Queryable().Single()返回序列的唯一元素；如果該序列并非恰好包含一個元素，則會引發異常。解：letsno=(fromssinScoreswheress.DEGREE==maxDegreeselectss.SNO).Single().ToString()11、查詢'3-105'號課程的平均分。selectavg(degree)fromscorewherecno='3-105'Linq:(fromsinScoreswheres.CNO=="3-105"selects.DEGREE).Average()Lambda:Scores.Where(s=>s.CNO=="3-105").Select(s=>s.DEGREE).Average()12、查詢Score表中至少有5名學生選修的并以3開頭的課程的平均分數。selectavg(degree)fromscorewherecnolike'3%'groupbyCnohavingcount(*)>=5Linq:fromsinScoreswheres.CNO.StartsWith("3")groupsbys.CNOintoccwherecc.Count()>=5selectcc.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也可以這樣寫:s.CNO.StartsWith("3")orSqlMethods.Like(s.CNO,"%3")13、查詢最低分大于70，最高分小于90的Sno列。selectsnofromscoregroupbysnohavingmin(degree)>70andmax(degree)<90Linq:fromsinScoresgroupsbys.SNOintosswheress.Min(cc=>cc.DEGREE)>70&&ss.Max(cc=>cc.DEGREE)<90selectnew{sno=ss.Key}Lambda:Scores.GroupBy(s=>s.SNO).Where(ss=>((ss.Min(cc=>cc.DEGREE)>70)&&(ss.Max(cc=>cc.DEGREE)<90))).Select(ss=>new{sno=ss.Key})14、查詢所有學生的Sname、Cno和Degree列。selects.sname,sc.cno,sc.degreefromstudentass,scoreasscwheres.sno=sc.snoLinq:fromsinStudentsjoinscinScoresons.SNOequalssc.SNOselectnew{s.SNAME,sc.CNO,sc.DEGREE}Lambda:Students.Join(Scores,s=>s.SNO,sc=>sc.SNO,(s,sc)=>new{SNAME=s.SNAME,CNO=sc.CNO,DEGREE=sc.DEGREE})15、查詢所有學生的Sno、Cname和Degree列。selectsc.sno,c.cname,sc.degreefromcourseasc,scoreasscwherec.cno=sc.cnoLinq:fromcinCoursesjoinscinScoresonc.CNOequalssc.CNOselectnew{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})16、查詢所有學生的Sname、Cname和Degree列。selects.sname,c.cname,sc.degreefromstudentass,courseasc,scoreasscwheres.sno=sc.snoandc.cno=sc.cnoLinq:fromsinStudentsfromcinCoursesfromscinScoreswheres.SNO==sc.SNO&&c.CNO==sc.CNOselectnew{s.SNAME,c.CNAME,sc.DEGREE}