SQL,LINQ,Lambda语法对照图(转载) Love The Way You Lie 2021-11-27 06:46 200阅读 0赞 如果你熟悉SQL语句,当使用LINQ时,会有似曾相识的感觉.但又略有不同.下面是SQL和LINQ,Lambda语法对照图 <table> <tbody> <tr> <th> <p>SQL</p> </th> <th>LINQ</th> <th>Lambda</th> </tr> <tr> <td> <p> </p> <p>SELECT *</p> <p>FROM HumanResources.Employee</p> </td> <td> <p>from e in Employees</p> <p>select e</p> </td> <td> <p>Employees<br>.Select (e => e)</p> </td> </tr> <tr> <td> <p>SELECT e.LoginID, e.JobTitle</p> <p>FROM HumanResources.Employee AS e</p> </td> <td> <p>from e in Employees</p> <p>select new {e.LoginID, e.JobTitle}</p> </td> <td> <p>Employees<br>.Select (<br>e => <br>new<br>{ <br>LoginID = e.LoginID, <br>JobTitle = e.JobTitle<br>}<br>)</p> </td> </tr> <tr> <td> <p>SELECT e.LoginID AS ID, e.JobTitle AS Title</p> <p>FROM HumanResources.Employee AS e</p> </td> <td> <p>from e in Employees</p> <p>select new {ID = e.LoginID, Title = e.JobTitle}</p> </td> <td> <p>Employees<br>.Select (<br>e => <br>new<br>{ <br>ID = e.LoginID, <br>Title = e.JobTitle<br>}<br>)</p> </td> </tr> <tr> <td> <p>SELECT DISTINCT e.JobTitle</p> <p>FROM HumanResources.Employee AS e</p> </td> <td> <p>(from e in Employees</p> <p>select e.JobTitle).Distinct()</p> </td> <td> <p>Employees<br>.Select (e => e.JobTitle)<br>.Distinct ()</p> </td> </tr> <tr> <td> <p>SELECT e.*</p> <p>FROM HumanResources.Employee AS e</p> <p>WHERE e.LoginID = 'test'</p> </td> <td> <p>from e in Employees</p> <p>where e.LoginID == "test"</p> <p>select e</p> </td> <td> <p>Employees<br>.Where (e => (e.LoginID == "test"))</p> </td> </tr> <tr> <td> <p>SELECT e.*</p> <p>FROM HumanResources.Employee AS e</p> <p>WHERE e.LoginID = 'test' AND e.SalariedFlag = 1</p> </td> <td> <p>from e in Employees</p> <p>where e.LoginID == "test" && e.SalariedFlag</p> <p>select e</p> </td> <td> <p>Employees<br>.Where (e => ((e.LoginID == "test") && e.SalariedFlag))</p> </td> </tr> <tr> <td> <p>SELECT e.*<br>FROM HumanResources.Employee AS e</p> <p>WHERE e.VacationHours >= 2 AND e.VacationHours <= 10</p> </td> <td> <p>from e in Employees</p> <p>where e.VacationHours >= 2 && e.VacationHours <= 10</p> <p>select e</p> </td> <td> <p>Employees<br>.Where (e => (((Int32)(e.VacationHours) >= 2) && ((Int32)(e.VacationHours) <= 10)))</p> </td> </tr> <tr> <td> <p>SELECT e.*</p> <p>FROM HumanResources.Employee AS e<br>ORDER BY e.NationalIDNumber</p> </td> <td> <p>from e in Employees</p> <p>orderby e.NationalIDNumber</p> <p>select e</p> </td> <td> <p>Employees<br>.OrderBy (e => e.NationalIDNumber)</p> </td> </tr> <tr> <td> <p>SELECT e.*</p> <p>FROM HumanResources.Employee AS e</p> <p>ORDER BY e.HireDate DESC, e.NationalIDNumber</p> </td> <td> <p>from e in Employees</p> <p>orderby e.HireDate descending, e.NationalIDNumber</p> <p>select e</p> </td> <td> <p>Employees<br>.OrderByDescending (e => e.HireDate)<br>.ThenBy (e => e.NationalIDNumber)</p> </td> </tr> <tr> <td> <p>SELECT e.*<br>FROM HumanResources.Employee AS e</p> <p>WHERE e.JobTitle LIKE 'Vice%' OR SUBSTRING(e.JobTitle, 0, 3) = 'Pro'</p> </td> <td> <p>from e in Employees</p> <p>where e.JobTitle.StartsWith("Vice") || e.JobTitle.Substring(0, 3) == "Pro"</p> <p>select e</p> </td> <td> <p>Employees<br>.Where (e => (e.JobTitle.StartsWith ("Vice") || (e.JobTitle.Substring (0, 3) == "Pro")))</p> </td> </tr> <tr> <td> <p>SELECT SUM(e.VacationHours)</p> <p>FROM HumanResources.Employee AS e</p> </td> <td> </td> <td> <p>Employees.Sum(e => e.VacationHours);</p> </td> </tr> <tr> <td> <p>SELECT COUNT(*)</p> <p>FROM HumanResources.Employee AS e</p> </td> <td> </td> <td> <p>Employees.Count();</p> </td> </tr> <tr> <td> <p>SELECT SUM(e.VacationHours) AS TotalVacations, e.JobTitle</p> <p>FROM HumanResources.Employee AS e</p> <p>GROUP BY e.JobTitle</p> </td> <td> <p>from e in Employees</p> <p>group e by e.JobTitle into g</p> <p>select new {JobTitle = g.Key, TotalVacations = g.Sum(e => e.VacationHours)}</p> </td> <td> <p>Employees<br>.GroupBy (e => e.JobTitle)<br>.Select (<br>g => <br>new<br>{ <br>JobTitle = g.Key, <br>TotalVacations = g.Sum (e => (Int32)(e.VacationHours))<br>}<br>)</p> </td> </tr> <tr> <td> <p>SELECT e.JobTitle, SUM(e.VacationHours) AS TotalVacations</p> <p>FROM HumanResources.Employee AS e</p> <p>GROUP BY e.JobTitle</p> <p>HAVING e.COUNT(*) > 2</p> </td> <td> <p>from e in Employees</p> <p>group e by e.JobTitle into g</p> <p>where g.Count() > 2</p> <p>select new {JobTitle = g.Key, TotalVacations = g.Sum(e => e.VacationHours)}</p> </td> <td> <p>Employees<br>.GroupBy (e => e.JobTitle)<br>.Where (g => (g.Count () > 2))<br>.Select (<br>g => <br>new<br>{ <br>JobTitle = g.Key, <br>TotalVacations = g.Sum (e => (Int32)(e.VacationHours))<br>}<br>)</p> </td> </tr> <tr> <td> <p>SELECT *</p> <p>FROM Production.Product AS p, Production.ProductReview AS pr</p> </td> <td> <p>from p in Products</p> <p>from pr in ProductReviews</p> <p>select new {p, pr}</p> </td> <td> <p>Products<br>.SelectMany (<br>p => ProductReviews, <br>(p, pr) => <br>new<br>{ <br>p = p, <br>pr = pr<br>}<br>)</p> </td> </tr> <tr> <td> <p>SELECT *</p> <p>FROM Production.Product AS p</p> <p>INNER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID</p> </td> <td> <p>from p in Products</p> <p>join pr in ProductReviews on p.ProductID equals pr.ProductID</p> <p>select new {p, pr}</p> </td> <td> <p>Products<br>.Join (<br>ProductReviews, <br>p => p.ProductID, <br>pr => pr.ProductID, <br>(p, pr) => <br>new<br>{ <br>p = p, <br>pr = pr<br>}<br>)</p> </td> </tr> <tr> <td> <p>SELECT *</p> <p>FROM Production.Product AS p</p> <p>INNER JOIN Production.ProductCostHistory AS pch ON p.ProductID = pch.ProductID AND p.SellStartDate = pch.StartDate</p> </td> <td> <p>from p in Products</p> <p>join pch in ProductCostHistories on new {p.ProductID, StartDate = p.SellStartDate} equals new {pch.ProductID, StartDate = pch.StartDate}</p> <p>select new {p, pch}</p> </td> <td> <p>Products<br>.Join (<br>ProductCostHistories, <br>p => <br>new<br>{ <br>ProductID = p.ProductID, <br>StartDate = p.SellStartDate<br>}, <br>pch => <br>new<br>{ <br>ProductID = pch.ProductID, <br>StartDate = pch.StartDate<br>}, <br>(p, pch) => <br>new<br>{ <br>p = p, <br>pch = pch<br>}<br>)</p> </td> </tr> <tr> <td> <p>SELECT *</p> <p>FROM Production.Product AS p</p> <p>LEFT OUTER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID</p> </td> <td> <p>from p in Products</p> <p>join pr in ProductReviews on p.ProductID equals pr.ProductID</p> <p>into prodrev</p> <p>select new {p, prodrev}</p> </td> <td> <p>Products<br>.GroupJoin (<br>ProductReviews, <br>p => p.ProductID, <br>pr => pr.ProductID, <br>(p, prodrev) => <br>new<br>{ <br>p = p, <br>prodrev = prodrev<br>}<br>)</p> </td> </tr> <tr> <td> <p>SELECT p.ProductID AS ID</p> <p>FROM Production.Product AS p</p> <p>UNION</p> <p>SELECT pr.ProductReviewID</p> <p>FROM Production.ProductReview AS pr</p> </td> <td> <p>(from p in Products</p> <p>select new {ID = p.ProductID}).Union(</p> <p>from pr in ProductReviews</p> <p>select new {ID = pr.ProductReviewID})</p> </td> <td> <p>Products<br>.Select (<br>p => <br>new<br>{ <br>ID = p.ProductID<br>}<br>)<br>.Union (<br>ProductReviews<br>.Select (<br>pr => <br>new<br>{ <br>ID = pr.ProductReviewID<br>}<br>)<br>)</p> </td> </tr> <tr> <td> <p>SELECT TOP (10) *</p> <p>FROM Production.Product AS p</p> <p>WHERE p.StandardCost < 100</p> </td> <td> <p>(from p in Products</p> <p>where p.StandardCost < 100</p> <p>select p).Take(10)</p> </td> <td> <p>Products<br>.Where (p => (p.StandardCost < 100))<br>.Take (10)</p> </td> </tr> <tr> <td> <p>SELECT *</p> <p>FROM [Production].[Product] AS p</p> <p>WHERE p.ProductID IN(</p> <p>SELECT pr.ProductID</p> <p>FROM [Production].[ProductReview] AS [pr]</p> <p>WHERE pr.[Rating] = 5</p> <p>)</p> </td> <td> <p>from p in Products</p> <p>where (from pr in ProductReviews</p> <p>where pr.Rating == 5</p> <p>select pr.ProductID).Contains(p.ProductID)</p> <p>select p</p> </td> <td> <p>Products<br>.Where (<br>p => <br>ProductReviews<br>.Where (pr => (pr.Rating == 5))<br>.Select (pr => pr.ProductID)<br>.Contains (p.ProductID)<br>)</p> </td> </tr> </tbody> </table> 转载于:https://www.cnblogs.com/CielWater/p/3528718.html
还没有评论,来说两句吧...