一、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
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 eORDER 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) )
二、同一功能的几种不同写法的实例
1、简单的函数计算（count，min，max，sum）
C# 代码 复制 //1 获取最大的rpId var ss = (from r in db.Am_recProScheme select r).Max(p => p.rpId); 获取最小的rpId var ss = (from r in db.Am_recProScheme select r).Min(p => p.rpId); //获取结果集的总数 var ss = (from r in db.Am_recProScheme select r).Count(); //获取rpId的和 var ss = (from r in db.Am_recProScheme select r).Sum(p => p.rpId); //2 var ss1 = db.Am_recProScheme.Max(p=>p.rpId); var ss1 = db.Am_recProScheme.Min(p => p.rpId); var ss1 = db.Am_recProScheme.Count() ; var ss1 = db.Am_recProScheme.Sum(p => p.rpId); Response.Write(ss); //3 string sssql = "select max(rpId) from Am_recProScheme"; sssql = "select min(rpId) from Am_recProScheme"; sssql = "select count(1) from Am_recProScheme"; sssql = "select sum(rpId) from Am_recProScheme";
2、排序order by desc/asc
C# 代码 复制 var ss = from r in db.Am_recProScheme where r.rpId > 10 orderby r.rpId descending //倒序 // orderby r.rpId ascending //正序 select r; //正序 var ss1 = db.Am_recProScheme.OrderBy(p => p.rpId).Where(p => p.rpId > 10).ToList(); //倒序 var ss2 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).ToList(); string sssql = "select * from Am_recProScheme where rpid>10 order by rpId [desc|asc]";
3、top
C# 代码 复制 //1 //如果取最后一个可以按倒叙排列再取值 var ss = (from r in db.Am_recProScheme select r).FirstOrDefault(); //2 string sssql = "select top(1) * from Am_recProScheme";
4、跳过前面多少条数据取余下的数据
C# 代码 复制 //1 var ss = (from r in db.Am_recProScheme orderby r.rpId descending select r).Skip(10); //跳过前10条数据，取10条之后的所有数据 //2 var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Skip(10).ToList(); //3 string sssql = "select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10";
5、分页数据查询
C# 代码 复制 //1 var ss = (from r in db.Am_recProScheme where r.rpId > 10 orderby r.rpId descending select r).Skip(10).Take(10); //取第11条到第20条数据 //2 Take(10): 数据从开始获取，获取指定数量（10）的连续数据 var ss1 = db.Am_recProScheme.OrderByDescending(p => p.rpId).Where(p => p.rpId > 10).Skip(10).Take(10).ToList(); //3 string sssql = "select * from (select ROW_NUMBER()over(order by rpId desc) as rowNum, * from [Am_recProScheme]) as t where rowNum>10 and rowNum<=20";
6、包含，类似like '%%'
C# 代码 复制 //1 var ss = from r in db.Am_recProScheme where r.SortsText.Contains("张") select r; //2 var ss1 = db.Am_recProScheme.Where(p => p.SortsText.Contains("张")).ToList(); //3 string sssql = "select * from Am_recProScheme where SortsText like '%张%'";
7、分组group by
C# 代码 复制 //1 var ss = from r in db.Am_recProScheme orderby r.rpId descending group r by r.recType into n select new { n.Key, //这个Key是recType rpId = n.Sum(r => r.rpId), //组内rpId之和 MaxRpId = n.Max(r => r.rpId),//组内最大rpId MinRpId = n.Min(r => r.rpId), //组内最小rpId }; foreach (var t in ss) { Response.Write(t.Key + "--" + t.rpId + "--" + t.MaxRpId + "--" + t.MinRpId); } //2 var ss1 = from r in db.Am_recProScheme orderby r.rpId descending group r by r.recType into n select n; foreach (var t in ss1) { Response.Write(t.Key + "--" + t.Min(p => p.rpId)); } //3 var ss2 = db.Am_recProScheme.GroupBy(p => p.recType); foreach (var t in ss2) { Response.Write(t.Key + "--" + t.Min(p => p.rpId)); } //4 string sssql = "select recType,min(rpId),max(rpId),sum(rpId) from Am_recProScheme group by recType";
8、连接查询
C# 代码 复制 //1 var ss = from r in db.Am_recProScheme join w in db.Am_Test_Result on r.rpId equals w.rsId orderby r.rpId descending select r; //2 var ss1 = db.Am_recProScheme.Join(db.Am_Test_Result, p => p.rpId, r => r.rsId, (p, r) => p).OrderByDescending(p => p.rpId).ToList(); //3 string sssql = "select r.* from [Am_recProScheme] as r inner join [dbo].[Am_Test_Result] as t on r.[rpId] = t.[rsId] order by r.[rpId] desc";
9、sql中的In
C# 代码 复制 //1 var ss = from p in db.Am_recProScheme where (new int?[] { 24, 25,26 }).Contains(p.rpId) select p; foreach (var p in ss) { Response.Write(p.Sorts); } //2 string st = "select * from Am_recProScheme where rpId in(24,25,26)";转载于:https://www.cnblogs.com/c-x-a/p/6179513.html
相关资源：JAVA上百实例源码以及开源项目