• 卡卡   2014/7/27 11:02:00
  • 超赞-SQL与Linq查询语法对照表
  • 关键字: linq 查询语法 sql语句
  • 如果你熟悉SQL语句,当使用LINQ时,会有似曾相识的感觉.但又略有不同.下面是SQL和LINQ,Lambda语法对照

    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

     

    from e in Employees

    select new {e.LoginID, e.JobTitle}

     

    Employees.Select (
          e => new {
                LoginID = e.LoginID, 
                JobTitle = e.JobTitle
             }
       )

     

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

    FROM HumanResources.Employee AS e

     

    from e in Employees

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

     

    Employees.Select (
          e => new 
             {
                ID = e.LoginID, 
                Title = e.JobTitle
             }
       )

     

    SELECT DISTINCT e.JobTitle

    FROM HumanResources.Employee AS e

     

    (from e in Employees

    select e.JobTitle).Distinct()

     

    Employees
       .Select (e => e.JobTitle)
       .Distinct ()

     

    SELECT e.*

    FROM HumanResources.Employee AS e

    WHERE e.LoginID = 'test'

     

    from e in Employees

    where e.LoginID == "test"

    select e

     

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

     

    SELECT e.*

    FROM HumanResources.Employee AS e

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

     

    from e in Employees

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

    select e

     

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

     

    SELECT e.* FROM HumanResources.Employee AS e

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

     

    from e in Employees

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

    select e

     

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

     

    SELECT e.* FROM HumanResources.Employee AS e
    ORDER BY e.NationalIDNumber

     

    from e in Employees

    orderby e.NationalIDNumber

    select e

     

    Employees
       .OrderBy (e => e.NationalIDNumber)

     

    SELECT e.* FROM HumanResources.Employee AS e

    ORDER BY e.HireDate DESC, e.NationalIDNumber

     

    from e in Employees

    orderby e.HireDate descending, e.NationalIDNumber

    select e

     

    Employees
       .OrderByDescending (e => e.HireDate)
       .ThenBy (e => e.NationalIDNumber)

     

    SELECT e.* FROM HumanResources.Employee AS e

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

     

    from e in Employees

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

    select e

     

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

     

    SELECT SUM(e.VacationHours)

    FROM HumanResources.Employee AS e

       

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

     

    SELECT COUNT(*) FROM HumanResources.Employee AS e

       

    Employees.Count();

     

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

    FROM HumanResources.Employee AS e

    GROUP BY e.JobTitle

     

    from e in Employees

    group e by e.JobTitle into g

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

     

    Employees
       .GroupBy (e => e.JobTitle)
       .Select (
          g => new 
             {
                JobTitle = g.Key, 
                TotalVacations = g.Sum (e => (Int32)(e.VacationHours))
             }
       )

     

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

    FROM HumanResources.Employee AS e

    GROUP BY e.JobTitle

    HAVING e.COUNT(*) > 2

     

    from e in Employees

    group e by e.JobTitle into g

    where g.Count() > 2

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

     

    Employees
       .GroupBy (e => e.JobTitle)
       .Where (g => (g.Count () > 2))
       .Select (
          g => 
             new 
             {
                JobTitle = g.Key, 
                TotalVacations = g.Sum (e => (Int32)(e.VacationHours))
             }
       )

     

    SELECT *

    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) => 
             new 
             {
                p = p, 
                pr = pr
             }
       )

     

    SELECT *

    FROM Production.Product AS p

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

     

    from p in Products

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

    select new {p, pr}

     

    Products
       .Join (
          ProductReviews, 
          p => p.ProductID, 
          pr => pr.ProductID, 
          (p, pr) => 
             new 
             {
                p = p, 
                pr = pr
             }
       )

     

    SELECT *

    FROM Production.Product AS p

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

     

    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}

     

    Products
       .Join (
          ProductCostHistories, 
          p => 
             new 
             {
                ProductID = p.ProductID,
                StartDate = p.SellStartDate
             }, 
          pch => 
             new 
             {
                ProductID = pch.ProductID, 
                StartDate = pch.StartDate
             }, 
          (p, pch) => 
             new 
             {
                p = p, 
                pch = pch
             }
       )

     

    SELECT *

    FROM Production.Product AS p

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

     

    from p in Products

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

    into prodrev

    select new {p, prodrev}

     

    Products
       .GroupJoin (
          ProductReviews, 
          p => p.ProductID, 
          pr => pr.ProductID, 
          (p, prodrev) => 
             new 
             {
                p = p, 
                prodrev = prodrev
             }
       )

     

    SELECT p.ProductID AS ID

    FROM Production.Product AS p

    UNION

    SELECT pr.ProductReviewID

    FROM Production.ProductReview AS pr

     

    (from p in Products

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

    from pr in ProductReviews

    select new {ID = pr.ProductReviewID})

     

    Products
       .Select (
          p => 
             new 
             {
                ID = p.ProductID
             }
       )
       .Union (
          ProductReviews
             .Select (
                pr => 
                   new 
                   {
                      ID = pr.ProductReviewID
                   }
             )
       )

     

    SELECT TOP (10) *

    FROM Production.Product AS p

    WHERE p.StandardCost < 100

     

    (from p in Products

    where p.StandardCost < 100

    select p).Take(10)

     

    Products
       .Where (p => (p.StandardCost < 100))
       .Take (10)

     

    SELECT *

    FROM [Production].[Product] AS p

    WHERE p.ProductID IN(

        SELECT pr.ProductID

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

        WHERE pr.[Rating] = 5

        )

     

    from p in Products

    where (from pr in ProductReviews

    where pr.Rating == 5

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

    select p

     

    Products
       .Where (
          p => 
             ProductReviews
                .Where (pr => (pr.Rating == 5))
                .Select (pr => pr.ProductID)
                .Contains (p.ProductID)
       )

    直接上实例,学习起来是不是更方便些!