???????????г????? LINQ?????????????С????SQL ????л???????????????????????? ???????????????????????????
???????е?????? LINQ ? C#?????? VB???????????????????????????????????????????蹵?????????????????????????????????????Щ?????????????????μ?????????????????????????????в???????LINQ ?????????????? SQL ??????Ч??
????? SQL ???? LINQ ?????????????????????????????? C# ? C++ ?????????????????????? C++ ????????????????? SQL ??????????????????????У??????????????????????????????????????????
????SQL ????????????????—?????? 1974 ????????????????????????????????б???????????????????е??????—?????? VB6 ???? Visual FoxPro?????????????????????????????????κδ??????!
?????????????????????????????д?????????????????????????￡?
????SELECT UPPER(Name)
????FROM Customer
????WHERE Name LIKE 'A%'
????ORDER BY Name
????????????????????????Щ?????????????????????????????? 21 ?? 30 ???????????????????????????
????SELECT UPPER(Name) FROM
????(
????SELECT *?? RN = row_number()
????OVER (ORDER BY Name)
????FROM Customer
????WHERE Name LIKE 'A%'
????) A
????WHERE RN BETWEEN 21 AND 30
????ORDER BY Name
?????????????????汾???? SQL Server 2005 ????????????????????????:
????SELECT TOP 10 UPPER (c1.Name)
????FROM Customer c1
????WHERE
????c1.Name LIKE 'A%'
????AND c1.ID NOT IN
????(
????SELECT TOP 20 c2.ID
????FROM Customer c2
????WHERE c2.Name LIKE 'A%'
????ORDER BY c2.Name
????)
????ORDER BY c1.Name
?????????????????????????????Υ???? DRY ???????????? LINQ ??????????????????????????????
????var query =
????from c in db.Customers
????where c.Name.StartsWith ("A")
????orderby c.Name
????select c.Name.ToUpper();
????var thirdPage = query.Skip(20).Take(10);
??????е????????? thirdPage ??????????????С???? LINQ ?? SQL ???? Entity Framework ??????У????????????????????????????????????????????? SQL ?????????????????????????????????????????????
???????????
???????????????? LINQ ?????????????????????????????????????????е???????????裺
????IQueryable Paginate (this IQueryable query?? int skip?? int take)
????{
????return query.Skip(skip).Take(take);
????}
???????????????????
????var query = ...
????var thirdPage = query.Paginate (20?? 10);
???????????????????????????????????????????????????? LINQ ????????????????????????????ó????????á?
????????
????LINQ ???????????????? JOIN ????й???????????磬????????г????й????? $1000 ?????????????????????????????????ù???????????????????/????????????????????й??????????????????????????????????????Purchase?? Customer?? Address ??? PurchaseItem???????в??????? LINQ??????????????????????
????from p in db.Purchases
????where p.Customer.Address.State == "WA" || p.Customer == null
????where p.PurchaseItems.Sum (pi => pi.SaleAmount) > 1000
????select p
????????????????? SQL ?????
????SELECT p.*
????FROM Purchase p
????LEFT OUTER JOIN
????Customer c INNER JOIN Address a ON c.AddressID = a.ID
????ON p.CustomerID = c.ID
????WHERE
????(a.State = 'WA' || p.CustomerID IS NULL)
????AND p.ID in
????(
????SELECT PurchaseID FROM PurchaseItem
????GROUP BY PurchaseID HAVING SUM (SaleAmount) > 1000
????)
????????????????????????????????????????????????????У????????????????????????????????????????????????????????????????????Щ??????????????
????from p in db.Purchases
????where p.Customer.Address.State == "WA" || p.Customer == null
????let purchaseValue = p.PurchaseItems.Sum (pi => pi.SaleAmount)
????where purchaseValue > 1000
????orderby purchaseValue descending
????select new
????{
????p.Description??
????p.Customer.SalesPerson.Name??
????PurchaseItemCount = p.PurchaseItems.Count()
????}
????????????? SQL ????????????
????SELECT
????p.Description??
????s.Name??
????(SELECT COUNT(*) FROM PurchaseItem pi WHERE p.ID = pi.PurchaseID) PurchaseItemCount
????FROM Purchase p
????LEFT OUTER JOIN
????Customer c
????INNER JOIN Address a ON c.AddressID = a.ID
????LEFT OUTER JOIN SalesPerson s ON c.SalesPersonID = s.ID
????ON p.CustomerID = c.ID
????WHERE
????(a.State = 'WA' OR p.CustomerID IS NULL)
????AND p.ID in
????(
????SELECT PurchaseID FROM PurchaseItem
????GROUP BY PurchaseID HAVING SUM (SaleAmount) > 1000
????)
????ORDER BY
????(SELECT SUM (SaleAmount) FROM PurchaseItem pi WHERE p.ID = pi.PurchaseID) DESC
????????????????????? SQL ????????? LINQ??????????????鶼?????????????????????????????????????????????汾??——?????? SQL ??????????????? LINQ ?????????????????????? Fortran ????????? C# 6 ???? GOTO ???????????
????????????
?????????????д???????????? – ????????????????????????λ????顣?????????????? SQL???????????????2?????????????——????????????????????y??????????????????á????????????????????????????????LINQ ?????????????????????????????????????????y??????????????????????????????????????????????????????磬??????????????????????????????????????????????????? LINQ???????????????
????from c in db.Customers
????where c.Address.State == "WA"
????select new
????{
????c.Name??
????c.CustomerNumber??
????HighValuePurchases = c.Purchases.Where (p => p.Price > 1000)
????}
????HighValuePurchases?????????????????????????????????????????????????????????????????????????????????????????????????????????????У?????????? SQL??????????????????LINQ ????????????????????????????С?????????????????????????????????????????????????:
????from c in db.Customers
????where c.Address.State == "WA"
????let HighValuePurchases = c.Purchases.Where (p => p.Price > 1000)where HighValuePurchases.Any()select new
????{
????c.Name??
????c.CustomerNumber??
????HighValuePurchases
????}