• SQL-LINQ-Lambda语法对照

    SQL

    LINQ

     

    Lambda

    		 
    SELECT *FROM Employee
    		 
    from e in Employees select e

     

    		 
     Employees .Select (e => e)

     SELECT LoginID,JobTitle FROM Employee

    from e in Employees select new {e.LoginID, e.JobTitle}

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

     SELECT LoginID AS ID, JobTitle AS Title FROM Employee

    from e in Employees

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

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

    		 
     SELECT DISTINCT JobTitle FROM Employee 
    (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 Product AS p INNER JOIN ProductCostHistory AS c 
    　　ON p.ProductID     = c.ProductID AND 
    　　　　p.SellStartDate = c.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)
       )
  • 相关阅读:
    css 分类+选择器
    emmet语法
    程序员能力矩阵
    时间管理
    学
    java 内存 解析
    SQL 查询优化
    2016 书单计划
    ssh框架；
    Mybatis;
  • 原文地址：https://www.cnblogs.com/epsoft/p/8971521.html
Copyright © 2020-2023  润新知