SQL、Linq、lamda表达式 同一功能不同写法

一、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 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)    )

二、同一功能的几种不同写法的实例

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)";