SQL/LINQ/Lamda 寫法[轉發]

SQL

LINQ

Lambda

SELECT *

FROM HumanResources.Employee

from e in Employees

select e

Employees

   .Select (e => e)

SELECT e.LoginID, e.JobTitle

FROM HumanResources.Employee AS e

select new {e.LoginID, e.JobTitle}

   .Select (

      e =>

         new

         {

            LoginID = e.LoginID,

            JobTitle = e.JobTitle

         }

   )

SELECT e.LoginID AS ID, e.JobTitle AS Title

select new {ID = e.LoginID, Title = e.JobTitle}

            ID = e.LoginID,

            Title = e.JobTitle

SELECT DISTINCT e.JobTitle

(from e in Employees

select e.JobTitle).Distinct()

   .Select (e => e.JobTitle)

   .Distinct ()

SELECT e.*

WHERE e.LoginID = 'test'

where e.LoginID == "test"

   .Where (e => (e.LoginID == "test"))

WHERE e.LoginID = 'test' AND e.SalariedFlag = 1

where e.LoginID == "test" && e.SalariedFlag

   .Where (e => ((e.LoginID == "test") && e.SalariedFlag))

WHERE e.VacationHours >= 2 AND e.VacationHours <= 10

where e.VacationHours >= 2 && e.VacationHours <= 10

   .Where (e => (((Int32)(e.VacationHours) >= 2) && ((Int32)(e.VacationHours) <= 10)))

ORDER BY e.NationalIDNumber

orderby e.NationalIDNumber

   .OrderBy (e => e.NationalIDNumber)

ORDER BY e.HireDate DESC, e.NationalIDNumber

orderby e.HireDate descending, e.NationalIDNumber

   .OrderByDescending (e => e.HireDate)

   .ThenBy (e => e.NationalIDNumber)

WHERE e.JobTitle LIKE 'Vice%' OR SUBSTRING(e.JobTitle, 0, 3) = 'Pro'

where e.JobTitle.StartsWith("Vice") || e.JobTitle.Substring(0, 3) == "Pro"

   .Where (e => (e.JobTitle.StartsWith ("Vice") || (e.JobTitle.Substring (0, 3) == "Pro")))

SELECT SUM(e.VacationHours)

Employees.Sum(e => e.VacationHours);

SELECT COUNT(*)

Employees.Count();

SELECT SUM(e.VacationHours) AS TotalVacations, e.JobTitle

GROUP BY e.JobTitle

group e by e.JobTitle into g

select new {JobTitle = g.Key, TotalVacations = g.Sum(e => e.VacationHours)}

   .GroupBy (e => e.JobTitle)

      g =>

            JobTitle = g.Key,

            TotalVacations = g.Sum (e => (Int32)(e.VacationHours))

SELECT e.JobTitle, SUM(e.VacationHours) AS TotalVacations

HAVING e.COUNT(*) > 2

where g.Count() > 2

   .Where (g => (g.Count () > 2))

FROM Production.Product AS p, Production.ProductReview AS pr

from p in Products

from pr in ProductReviews

select new {p, pr}

Products

   .SelectMany (

      p => ProductReviews,

      (p, pr) =>

            p = p,

            pr = pr

FROM Production.Product AS p

INNER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID

join pr in ProductReviews on p.ProductID equals pr.ProductID

   .Join (

      ProductReviews,

      p => p.ProductID,

      pr => pr.ProductID,

INNER JOIN Production.ProductCostHistory AS pch ON p.ProductID = pch.ProductID AND p.SellStartDate = pch.StartDate

join pch in ProductCostHistories on new {p.ProductID, StartDate = p.SellStartDate} equals new {pch.ProductID, StartDate = pch.StartDate}

select new {p, pch}

      ProductCostHistories,

      p =>

            ProductID = p.ProductID,

            StartDate = p.SellStartDate

         },

      pch =>

            ProductID = pch.ProductID,

            StartDate = pch.StartDate

      (p, pch) =>

            pch = pch

LEFT OUTER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID

into prodrev

select new {p, prodrev}

   .GroupJoin (

      (p, prodrev) =>

            prodrev = prodrev

SELECT p.ProductID AS ID

UNION

SELECT pr.ProductReviewID

FROM Production.ProductReview AS pr

(from p in Products

select new {ID = p.ProductID}).Union(

select new {ID = pr.ProductReviewID})

            ID = p.ProductID

   .Union (

      ProductReviews

         .Select (

            pr =>

               new

               {

                  ID = pr.ProductReviewID

               }

         )

SELECT TOP (10) *

WHERE p.StandardCost < 100

where p.StandardCost < 100

select p).Take(10)

   .Where (p => (p.StandardCost < 100))

   .Take (10)

FROM [Production].[Product] AS p

WHERE p.ProductID IN(

    SELECT pr.ProductID

    FROM [Production].[ProductReview] AS [pr]

    WHERE pr.[Rating] = 5

    )

where (from pr in ProductReviews

where pr.Rating == 5

select pr.ProductID).Contains(p.ProductID)

select p

   .Where (

         ProductReviews

            .Where (pr => (pr.Rating == 5))

            .Select (pr => pr.ProductID)

            .Contains (p.ProductID)

學習交流群：364976091