SQL, LINQ, and lambda expressions

First of all, the three are completely three different things, SQL is Structured Query Language (structured query Language) abbreviation, this is familiar to everyone, the following is mainly about the basic concepts of LINQ and lambda expressions and the same query the different implementations of the three.

Simple Introduction

LINQ (Language integrate query) is a language-integrated inquiry he establishes a corresponding relationship between objects and data, and can query a collection of data in a way that accesses memory objects. A LINQ query is a language construct in C #. As a result, developers can then summarize the C # code to nest query expressions similar to SQL statements, thus enabling the functionality of the data query. LINQ is not simply a nested query expression in C #, but rather a query expression as a syntax for C #.
In the. NET class library, the LINQ-related class library is under the System.Linq namespace, which provides classes and interfaces that support querying using LINQ, the most important of which are the following two classes and two interfaces.
※ienumerable interface: It represents a collection of data that can be queried, and a query typically filters the elements in the collection one by one, returning a new IEnumerable interface to hold the query results.
※iqueryable interface: He inherits the IEnumerable interface, which represents an expression tree that can be queried.
※enumerable class: It implements the LINQ standard query operator by providing extension methods to IEnumerable. This includes operations such as crossing, navigating, sorting, querying, joining, summing, finding the maximum, and finding the minimum value.
※queryable class: It implements the LINQ standard query operator by providing extension methods to IQueryable. This includes operations such as crossing, navigating, sorting, querying, joining, summing, finding the maximum, and finding the minimum value.
A lambda expression is actually an anonymous function, which can be said to complement LINQ. There is a correspondence between the LINQ query keyword and the methods of the IEnumerable interface, but the query functionality that can be used in LINQ query expressions is few. In the actual development through the query results or data sources to make method calls, so that more query operations. Because a lambda expression is an anonymous function that can be assigned to a delegate, many of the methods in the IEnumerable interface implement custom operations, conditions, and so on through function delegates, so lambda expressions are widely used in LINQ.

Contrast implementation

* All contents are subject to enquiry

1 query all records of the student table. 2 Select * from Student3 linq:4 from     s in Students5     select S6 lambda:7     students.select (s = s)

* Query By column

Select Sname,ssex,class from Student 3 linq:4 from     s in Students 5     select new {6         s.sname, 7         s.ssex, 8
  s.class 9     } lambda:11     Students.select (s = = new {         SNAME = S.sname,ssex = S.ssex,class = S.class 1 3     })

※distinct to re-query

Query teachers all units that are not duplicated in the Depart column. 2 SELECT distinct depart from Teacher3 Linq:4 from     T in Teachers.distinct () 5     Select T.depart6 lambda:7     Tea Chers. Distinct (). Select (t = t.depart)

※ Two range of inquiries

1 query all records in the score table with scores from 60 to 80. 2 Select * from score where degree between and 3 Linq: 4 from     s in Scores  5     where S.degree >= 60 && S.degree < 6     Select S  7 Lambda:  8     scores.where (  9         s = = (                 S.degree & gt;= && S.degree <)     

※ Search within a range

SELECT * from score where degree in (85,86,88) 2 linq:3 from     s in Scores 4     where (5             new decimal[]{85,86,8 8} 6           ). Contains (s.degree) 7     Select s 8 lambda:9     scores.where (s = = new decimal[] {85,86,88}. Contains (S.degree))

※ or relationship Query

Check the student table for "95031" class or sex for "female" students record.  2 Select * FROM student where class = ' 95031 ' or ssex= N ' female ' 3 linq:4 from     s in Students 5     where s.class = "95031" 6        | | s.class = = "female" 7     Select s 8 lambda:9     students.where (s = = (S.class = = "95031" | | s.class = "female"))

※ Sort

Queries all records of the student table in descending order of class.  2 SELECT * FROM Student ORDER by Class DESC 3 linq:4 from     s in Students 5     s.class descending 6     Select S 7 lambda:8     students.orderbydescending (s = s.class)

※ Line number Query

Select COUNT (*) from student where class = ' 95031 ' 2 Linq:  3     (from    s in Students  4         where S.class = = "95031" 5         Select S  6     ). Count ()  7 Lambda:  8     students.where (s = = S.class = = "95031")  9                 . Select (s = = s) Ten                     . Count ()

※ Average Query

Check the average score for course ' 3-105 '. 2 Select AVG (degree) from score where cno = ' 3-105 ' 3 Linq:  4     (  5 from         s in Scores  6         where s.cno = = "3-105" 7         Select S.degree  8     ). Average ()  9 lambda:10     scores.where (s = = S.cno = "3-105") one by one             . Select (s = = S.degree)

※ Flight Enquiry

 Query the student number and course number of the highest score in the score table. 2 SELECT distinct S.SNO,C.CNO from student as s,course as C, score as SC 3 where s.sno= (select Sno from score where degre   E = (select Max (degree) from score)) 4 and C.cno = (select CNO from score where degree = (select Max (degree) from score)) 5 Linq:6 (7 from S in Students 8 from C in Courses 9 from SC in Scores Maxdegree = (from SSS in Scores one-select SSS. Degree 12). Max () Sno = (from the SS in Scores, where SS. degree = = maxdegree-select SS. SNO). Single (). ToString () 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, 22 C.cno 23} 24). Distinct () 

※ Group

Check the average score for a course that has at least 5 students enrolled in the score table and starts with 3. 2 Select AVG (degree) from score where CNO like ' 3% ' GROUP by CNO have Count (*) >=5 3 Linq:  4 from         s in scores< C2/>5         where S.cno. StartsWith ("3")  6         Group s by S.cno  7 to         cc  8         where CC. Count () >= 5 9         Select CC. Average (c = c.degree) lambda:11     scores.where (s = = S.cno. StartsWith ("3"))             . GroupBy (s = s.cno)               . Where (cc = (cc. Count () >= 5))                 . Select (cc = CC. Average (c = c.degree)) The Linq:sqlmethod can also be written like this:     s.cno. StartsWith ("3") or Sqlmethods.like (S.cno, "%3")

※ Group Filter

Check the average score for a course that has at least 5 students enrolled in the score table and starts with 3. 2 Select AVG (degree) from score where CNO like ' 3% ' GROUP by CNO have Count (*) >=5 3 Linq:  4 from         s in scores< C2/>5         where S.cno. StartsWith ("3")  6         Group s by S.cno  7 to         cc  8         where CC. Count () >= 5 9         Select CC. Average (c = c.degree) lambda:11     scores.where (s = = S.cno. StartsWith ("3"))             . GroupBy (s = s.cno)               . Where (cc = (cc. Count () >= 5))                 . Select (cc = CC. Average (c = c.degree)) The Linq:sqlmethod can also be written like this:     s.cno. StartsWith ("3") or Sqlmethods.like (S.cno, "%3")

※ Multi-table joint query

Select Sc.sno,c.cname,sc.degree from course as c,score as SC where c.cno = sc.cno 2 Linq:  3 from     C in Courses  4     Join SC in Scores  5 on     c.cno equals SC. CNO 6     Select New  7     {  8         SC. Sno,c.cname,sc. Degree 9     } lambda:11     Courses.join (Scores, C = c.cno,                              sc = sc. CNO,                              (c, SC) = new,                                         {                                             SNO = sc. SNO,                                             CNAME = c.cname,                                             degree = sc. Degree                                         })                 . Average ()

The above content is I check the data to do some collation and summary, there are shortcomings please criticize correct!

First of all, the three are completely three different things, SQL is Structured Query Language (structured query Language) abbreviation, this is familiar to everyone, the following is mainly about the basic concepts of LINQ and lambda expressions and the same query the different implementations of the three.

Simple Introduction

LINQ (Language integrate query) is a language-integrated inquiry he establishes a corresponding relationship between objects and data, and can query a collection of data in a way that accesses memory objects. A LINQ query is a language construct in C #. As a result, developers can then summarize the C # code to nest query expressions similar to SQL statements, thus enabling the functionality of the data query. LINQ is not simply a nested query expression in C #, but rather a query expression as a syntax for C #.
In the. NET class library, the LINQ-related class library is under the System.Linq namespace, which provides classes and interfaces that support querying using LINQ, the most important of which are the following two classes and two interfaces.
※ienumerable interface: It represents a collection of data that can be queried, and a query typically filters the elements in the collection one by one, returning a new IEnumerable interface to hold the query results.
※iqueryable interface: He inherits the IEnumerable interface, which represents an expression tree that can be queried.
※enumerable class: It implements the LINQ standard query operator by providing extension methods to IEnumerable. This includes operations such as crossing, navigating, sorting, querying, joining, summing, finding the maximum, and finding the minimum value.
※queryable class: It implements the LINQ standard query operator by providing extension methods to IQueryable. This includes operations such as crossing, navigating, sorting, querying, joining, summing, finding the maximum, and finding the minimum value.
A lambda expression is actually an anonymous function, which can be said to complement LINQ. There is a correspondence between the LINQ query keyword and the methods of the IEnumerable interface, but the query functionality that can be used in LINQ query expressions is few. In the actual development through the query results or data sources to make method calls, so that more query operations. Because a lambda expression is an anonymous function that can be assigned to a delegate, many of the methods in the IEnumerable interface implement custom operations, conditions, and so on through function delegates, so lambda expressions are widely used in LINQ.

Contrast implementation

* All contents are subject to enquiry

1 query all records of the student table. 2 Select * from Student3 linq:4 from     s in Students5     select S6 lambda:7     students.select (s = s)

* Query By column

Select Sname,ssex,class from Student 3 linq:4 from     s in Students 5     select new {6         s.sname, 7         s.ssex, 8
  s.class 9     } lambda:11     Students.select (s = = new {         SNAME = S.sname,ssex = S.ssex,class = S.class 1 3     })

※distinct to re-query

Query teachers all units that are not duplicated in the Depart column. 2 SELECT distinct depart from Teacher3 Linq:4 from     T in Teachers.distinct () 5     Select T.depart6 lambda:7     Tea Chers. Distinct (). Select (t = t.depart)

※ Two range of inquiries

1 query all records in the score table with scores from 60 to 80. 2 Select * from score where degree between and 3 Linq: 4 from     s in Scores  5     where S.degree >= 60 && S.degree < 6     Select S  7 Lambda:  8     scores.where (  9         s = = (                 S.degree & gt;= && S.degree <)     

※ Search within a range

SELECT * from score where degree in (85,86,88) 2 linq:3 from     s in Scores 4     where (5             new decimal[]{85,86,8 8} 6           ). Contains (s.degree) 7     Select s 8 lambda:9     scores.where (s = = new decimal[] {85,86,88}. Contains (S.degree))

※ or relationship Query

Check the student table for "95031" class or sex for "female" students record.  2 Select * FROM student where class = ' 95031 ' or ssex= N ' female ' 3 linq:4 from     s in Students 5     where s.class = "95031" 6        | | s.class = = "female" 7     Select s 8 lambda:9     students.where (s = = (S.class = = "95031" | | s.class = "female"))

※ Sort

Queries all records of the student table in descending order of class.  2 SELECT * FROM Student ORDER by Class DESC 3 linq:4 from     s in Students 5     s.class descending 6     Select S 7 lambda:8     students.orderbydescending (s = s.class)

※ Line number Query

Select COUNT (*) from student where class = ' 95031 ' 2 Linq:  3     (from    s in Students  4         where S.class = = "95031" 5         Select S  6     ). Count ()  7 Lambda:  8     students.where (s = = S.class = = "95031")  9                 . Select (s = = s) Ten                     . Count ()

※ Average Query

Check the average score for course ' 3-105 '. 2 Select AVG (degree) from score where cno = ' 3-105 ' 3 Linq:  4     (  5 from         s in Scores  6         where s.cno = = "3-105" 7         Select S.degree  8     ). Average ()  9 lambda:10     scores.where (s = = S.cno = "3-105") one by one             . Select (s = = S.degree)

※ Flight Enquiry

 Query the student number and course number of the highest score in the score table. 2 SELECT distinct S.SNO,C.CNO from student as s,course as C, score as SC 3 where s.sno= (select Sno from score where degre   E = (select Max (degree) from score)) 4 and C.cno = (select CNO from score where degree = (select Max (degree) from score)) 5 Linq:6 (7 from S in Students 8 from C in Courses 9 from SC in Scores ten let M Axdegree = (from SSS in Scores one-select SSS. Degree 12). Max () Sno = (from the SS in Scores, where SS. degree = = maxdegree-select SS. SNO). Single (). ToString () 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, 22 C.cno 23} 24). Distinct () 

※ Group

Check the average score for a course that has at least 5 students enrolled in the score table and starts with 3. 2 Select AVG (degree) from score where CNO like ' 3% ' GROUP by CNO have Count (*) >=5 3 Linq:  4 from         s in scores< C2/>5         where S.cno. StartsWith ("3")  6         Group s by S.cno  7 to         cc  8         where CC. Count () >= 5 9         Select CC. Average (c = c.degree) lambda:11     scores.where (s = = S.cno. StartsWith ("3"))             . GroupBy (s = s.cno)               . Where (cc = (cc. Count () >= 5))                 . Select (cc = CC. Average (c = c.degree)) The Linq:sqlmethod can also be written like this:     s.cno. StartsWith ("3") or Sqlmethods.like (S.cno, "%3")

※ Group Filter

Check the average score for a course that has at least 5 students enrolled in the score table and starts with 3. 2 Select AVG (degree) from score where CNO like ' 3% ' GROUP by CNO have Count (*) >=5 3 Linq:  4 from         s in scores< C2/>5         where S.cno. StartsWith ("3")  6         Group s by S.cno  7 to         cc  8         where CC. Count () >= 5 9         Select CC. Average (c = c.degree) lambda:11     scores.where (s = = S.cno. StartsWith ("3"))             . GroupBy (s = s.cno)               . Where (cc = (cc. Count () >= 5))                 . Select (cc = CC. Average (c = c.degree)) The Linq:sqlmethod can also be written like this:     s.cno. StartsWith ("3") or Sqlmethods.like (S.cno, "%3")

※ Multi-table joint query

Select Sc.sno,c.cname,sc.degree from course as c,score as SC where c.cno = sc.cno 2 Linq:  3 from     C in Courses  4     Join SC in Scores  5 on     c.cno equals SC. CNO 6     Select New  7     {  8         SC. Sno,c.cname,sc. Degree 9     } lambda:11     Courses.join (Scores, C = c.cno,                              sc = sc. CNO,                              (c, SC) = new,                                         {                                             SNO = sc. SNO,                                             CNAME = c.cname,                                             degree = sc. Degree                                         })                 . Average ()

These are the contents of SQL, LINQ, and lambda expressions, and please follow topic.alibabacloud.com (www.php.cn) for more information!

