If you are already working with SQL and are familiar with SQL queries
then you may find you at time are thinking of converting SQL syntax to
LINQ syntax when writing LINQ. Following cheat sheet should help you
with some of the common queries
|
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) ) |
Also, here is an excellent LINQ query comprehension diagram http://www.albahari.com/nutshell/linqsyntax.emf
No comments :
Post a Comment