SQL-LINQ-Lambda语法对照

川长思鸟来 2022-03-30 07:55 226阅读 0赞

<table> 
 <tbody> 
 <tr> 
 <td> SQL </td> 
 <td> LINQ </td> 
 <td> &nbsp; Lambda </td> 
 </tr> 
 <tr> 
 <td> SELECT *FROM HumanResources.Employee </td> 
 <td> &nbsp; from e in Employees select e </td> 
 <td> &nbsp; Employees .Select (e =&gt; e) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT e.LoginID, e.JobTitle FROM HumanResources.Employee AS e </td> 
 <td> &nbsp; from e in Employees select new {e.LoginID, e.JobTitle} </td> 
 <td> &nbsp; Employees.Select ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;e =&gt; new { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;LoginID = e.LoginID,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JobTitle = e.JobTitle &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; ) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT e.LoginID AS ID, e.JobTitle AS Title FROM HumanResources.Employee AS e </td> 
 <td> &nbsp; from e in Employees select new {ID = e.LoginID, Title = e.JobTitle} </td> 
 <td> &nbsp; Employees.Select ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;e =&gt; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ID = e.LoginID,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Title = e.JobTitle &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; ) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT DISTINCT e.JobTitle FROM HumanResources.Employee AS e </td> 
 <td> &nbsp; (from e in Employees select e.JobTitle).Distinct() </td> 
 <td> &nbsp; Employees &nbsp;&nbsp; .Select (e =&gt; e.JobTitle) &nbsp;&nbsp; .Distinct () </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT e.* FROM HumanResources.Employee AS e WHERE e.LoginID = 'test' </td> 
 <td> &nbsp; from e in Employees where e.LoginID == "test" select e </td> 
 <td> &nbsp; Employees &nbsp;&nbsp; .Where (e =&gt; (e.LoginID == "test")) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT e.* FROM HumanResources.Employee AS e WHERE e.LoginID = 'test' AND e.SalariedFlag = 1 </td> 
 <td> &nbsp; from e in Employees where e.LoginID == "test" &amp;&amp; e.SalariedFlag select e </td> 
 <td> &nbsp; Employees &nbsp;&nbsp; .Where (e =&gt; ((e.LoginID == "test") &amp;&amp; e.SalariedFlag)) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT e.* FROM HumanResources.Employee AS e WHERE e.VacationHours &gt;= 2 AND e.VacationHours &lt;= 10 </td> 
 <td> &nbsp; from e in Employees where e.VacationHours &gt;= 2 &amp;&amp; e.VacationHours &lt;= 10 select e </td> 
 <td> &nbsp; Employees &nbsp;&nbsp; .Where (e =&gt; (((Int32)(e.VacationHours) &gt;= 2) &amp;&amp; ((Int32)(e.VacationHours) &lt;= 10))) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT e.* FROM HumanResources.Employee AS e ORDER BY e.NationalIDNumber </td> 
 <td> &nbsp; from e in Employees orderby e.NationalIDNumber select e </td> 
 <td> &nbsp; Employees &nbsp;&nbsp; .OrderBy (e =&gt; e.NationalIDNumber) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT e.* FROM HumanResources.Employee AS e ORDER BY e.HireDate DESC, e.NationalIDNumber </td> 
 <td> &nbsp; from e in Employees orderby e.HireDate descending, e.NationalIDNumber select e </td> 
 <td> &nbsp; Employees &nbsp;&nbsp; .OrderByDescending (e =&gt; e.HireDate) &nbsp;&nbsp; .ThenBy (e =&gt; e.NationalIDNumber) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT e.* FROM HumanResources.Employee AS e WHERE e.JobTitle LIKE 'Vice%' OR SUBSTRING(e.JobTitle, 0, 3) = 'Pro' </td> 
 <td> &nbsp; from e in Employees where e.JobTitle.StartsWith("Vice") || e.JobTitle.Substring(0, 3) == "Pro" select e </td> 
 <td> &nbsp; Employees &nbsp;&nbsp; .Where (e =&gt; (e.JobTitle.StartsWith ("Vice") || (e.JobTitle.Substring (0, 3) == "Pro"))) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT SUM(e.VacationHours) FROM HumanResources.Employee AS e </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; Employees.Sum(e =&gt; e.VacationHours); </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT COUNT(*) FROM HumanResources.Employee AS e </td> 
 <td> &nbsp; </td> 
 <td> &nbsp; Employees.Count(); </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT SUM(e.VacationHours) AS TotalVacations, e.JobTitle FROM HumanResources.Employee AS e GROUP BY e.JobTitle </td> 
 <td> &nbsp; from e in Employees group e by e.JobTitle into g select new {JobTitle = g.Key, TotalVacations = g.Sum(e =&gt; e.VacationHours)} </td> 
 <td> &nbsp; Employees &nbsp;&nbsp; .GroupBy (e =&gt; e.JobTitle) &nbsp;&nbsp; .Select ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;g =&gt; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JobTitle = g.Key,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TotalVacations = g.Sum (e =&gt; (Int32)(e.VacationHours)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; ) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT e.JobTitle, SUM(e.VacationHours) AS TotalVacations FROM HumanResources.Employee AS e GROUP BY e.JobTitle HAVING e.COUNT(*) &gt; 2 </td> 
 <td> &nbsp; from e in Employees group e by e.JobTitle into g where g.Count() &gt; 2 select new {JobTitle = g.Key, TotalVacations = g.Sum(e =&gt; e.VacationHours)} </td> 
 <td> &nbsp; Employees &nbsp;&nbsp; .GroupBy (e =&gt; e.JobTitle) &nbsp;&nbsp; .Where (g =&gt; (g.Count () &gt; 2)) &nbsp;&nbsp; .Select ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;g =&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JobTitle = g.Key,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TotalVacations = g.Sum (e =&gt; (Int32)(e.VacationHours)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; ) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT * FROM Production.Product AS p, Production.ProductReview AS pr </td> 
 <td> &nbsp; from p in Products from pr in ProductReviews select new {p, pr} </td> 
 <td> &nbsp; Products &nbsp;&nbsp; .SelectMany ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p =&gt; ProductReviews,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(p, pr) =&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p = p,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pr = pr &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; ) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT * FROM Production.Product AS p INNER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID </td> 
 <td> &nbsp; from p in Products join pr in ProductReviews on p.ProductID equals pr.ProductID select new {p, pr} </td> 
 <td> &nbsp; Products &nbsp;&nbsp; .Join ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ProductReviews,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p =&gt; p.ProductID,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pr =&gt; pr.ProductID,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(p, pr) =&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p = p,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pr = pr &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; ) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT * FROM Production.Product AS p INNER JOIN Production.ProductCostHistory AS pch ON p.ProductID = pch.ProductID AND p.SellStartDate = pch.StartDate </td> 
 <td> &nbsp; from p in Products join pch in ProductCostHistories on new {p.ProductID, StartDate = p.SellStartDate} equals new {pch.ProductID, StartDate = pch.StartDate} select new {p, pch} </td> 
 <td> &nbsp; Products &nbsp;&nbsp; .Join ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ProductCostHistories,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p =&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ProductID = p.ProductID,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StartDate = p.SellStartDate &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; },&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pch =&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ProductID = pch.ProductID,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;StartDate = pch.StartDate &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; },&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(p, pch) =&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p = p,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pch = pch &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; ) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT * FROM Production.Product AS p LEFT OUTER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID </td> 
 <td> &nbsp; from p in Products join pr in ProductReviews on p.ProductID equals pr.ProductID into prodrev select new {p, prodrev} </td> 
 <td> &nbsp; Products &nbsp;&nbsp; .GroupJoin ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ProductReviews,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p =&gt; p.ProductID,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pr =&gt; pr.ProductID,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(p, prodrev) =&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p = p,&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;prodrev = prodrev &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; ) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT p.ProductID AS ID FROM Production.Product AS p UNION SELECT pr.ProductReviewID FROM Production.ProductReview AS pr </td> 
 <td> &nbsp; (from p in Products select new {ID = p.ProductID}).Union( from pr in ProductReviews select new {ID = pr.ProductReviewID}) </td> 
 <td> &nbsp; Products &nbsp;&nbsp; .Select ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p =&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ID = p.ProductID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp; ) &nbsp;&nbsp; .Union ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ProductReviews &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .Select ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;pr =&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; new&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ID = pr.ProductReviewID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) &nbsp;&nbsp; ) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT TOP (10) * FROM Production.Product AS p WHERE p.StandardCost &lt; 100 </td> 
 <td> &nbsp; (from p in Products where p.StandardCost &lt; 100 select p).Take(10) </td> 
 <td> &nbsp; Products &nbsp;&nbsp; .Where (p =&gt; (p.StandardCost &lt; 100)) &nbsp;&nbsp; .Take (10) </td> 
 </tr> 
 <tr> 
 <td> &nbsp; SELECT * FROM [Production].[Product] AS p WHERE p.ProductID IN( &nbsp;&nbsp;&nbsp;&nbsp;SELECT pr.ProductID &nbsp;&nbsp;&nbsp;&nbsp;FROM [Production].[ProductReview] AS [pr] &nbsp;&nbsp;&nbsp;&nbsp;WHERE pr.[Rating] = 5 &nbsp;&nbsp;&nbsp;&nbsp;) </td> 
 <td> &nbsp; from p in Products where (from pr in ProductReviews where pr.Rating == 5 select pr.ProductID).Contains(p.ProductID) select p </td> 
 <td> &nbsp; Products &nbsp;&nbsp; .Where ( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;p =&gt;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProductReviews &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Where (pr =&gt; (pr.Rating == 5)) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Select (pr =&gt; pr.ProductID) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Contains (p.ProductID) &nbsp;&nbsp; ) </td> 
 </tr> 
 </tbody> 
</table>