當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Sharepoint学习笔记---Linq to Sharepoint--查询语法

發布時間：2023/12/18 编程问答 29 豆豆

生活随笔收集整理的這篇文章主要介紹了 Sharepoint学习笔记---Linq to Sharepoint--查询语法小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

??Linq to sharepoint的引入的確給我們操作Sharepoint帶來了便利，首先就體現在對Sharepoint 的查詢優勢上。它基本可以照搬Linq to SQL的查詢語法，這就大大保護了你的學習成本,當然，它們之間有某些差異（如：在List間的Left Outer Join等處）
? 在實踐本處的知識點前，首先需要搭建我們的測試環境。這里我們仍舊引入Northwind數據庫的數據表:Customers,Orders,Order Details和Products。我們要用到它們是因為我們要利用它里面的數據快速創建出我們Sharepoint網站的List內容(我們要在Sharepoint網站上創建出4個CustomerLists: ACustomer,AOrders,AOrderDetails和AProducts)。
? 如何搭建此環境，請參照

??Sharepoint學習筆記---SPList--使用Linq to Sharepoint間接查詢External List(1.通過BCS創建External List)

??Sharepoint學習筆記---SPList--使用Linq to Sharepoint間接查詢External List(2.復制External List內容)

??Sharepoint學習筆記---SPList--使用Linq to Sharepoint間接查詢External List(3.使用Linq to Sharepoint查詢List內容)
? 當然你也可以想其它辦法創建相應的Sharepoint List環境(eg:通過Excel表導入)，總之，此處不再贅述如何搭建學習環境。
? 我們創建好的List如下

? 下面分別列舉查詢語法:
? 首先，在程序頂部我們定義了四個Entity變量并給它們賦值，即從Sharepoint網站的相應List中取出List的內容賦值給對應的Entity Classes?

EntityList<ACustomerItem>?MyCustomers;
????????EntityList<AOrdersItem>?MyOrders;
????????EntityList<AOrderDetailsItem>?MyOrderDetails;
????????EntityList<AProductsItem>?MyProducts;
???????var?dc?=?new?NorthWindEntityDataContext(SPContext.Current.Web.Url);
????????MyCustomers?=?dc.GetList<ACustomerItem>("ACustomer");
????????MyOrders?=?dc.GetList<AOrdersItem>("AOrders");
????????MyOrderDetails?=?dc.GetList<AOrderDetailsItem>("AOrderDetails");
????????MyProducts?=?dc.GetList<AProductsItem>("AProducts");

?接下來就是使用上面的 MyCustomers,MyOrders,MyOrderDetails,MyProducts進行各種查詢。?

?1.ACustomer中所有的CustomerID(Distinct查詢)??

View Code var?distinctCustomers?=?(from?dcustom?in?MyCustomers?select?dcustom.BCSFindCustomerID).Distinct();

?2.查詢所有有定單的Customer?

View Code var?query?=?from?c?in?MyCustomers
?????????????where?(from?o?in?MyOrders
???????????????????????????????????select?o.BCSFindCustomerID).Contains(c.BCSFindCustomerID)
????????????????????????????select?c;

?3.查詢所有沒有定單的Customer?

View Code var?query?=?from?c?in?MyCustomers
????????????where?!(from?o?in?MyOrders
????????????????????????????????select?o.BCSFindCustomerID).Contains(c.BCSFindCustomerID)
????????????????????????select?new
????????????????????????{
????????????????????????????CopanyName?=?c.BCSFindCompanyName,
????????????????????????????ContanctName?=?c.BCSFindContactName,
????????????????????????????Address?=?new
????????????????????????????{
????????????????????????????????Country?=?c.BCSFindCountry,
????????????????????????????????City?=?c.BCSFindCity,
????????????????????????????????PostalCode?=?c.BCSFindPostalCode
????????????????????????????}

????????????????????????};

?4.判斷Customer的Country是否屬于歐洲國家

View Code var?query?=?from?c?in?MyCustomers
??????????????select?new
????????????????????????{
????????????????????????????CustomerName?=?c.BCSFindContactName,
????????????????????????????Country?=?c.BCSFindCountry,
????????????????????????????IsEuropeCountry?=?new?string[]?{?"Belgium",?"Denmark",?"Finland",?"France",?"Germany",?"Ireland",?"Italy",?"Norway",?"Poland",?"Portugal",?"Spain",?"Sweden",?"Switzerland",?"UK"?}.Contains(c.BCSFindCountry)???"Yes"?:?"No"
????????????????????????};

?5.按Customer的Country是否屬于歐洲國家進行分組（Group),并統計每組包含的Customer數目?

View Code var?query?=?from?c?in?MyCustomers
?????????????group?c?by?new?{?IsEuropeCountry?=?new?string[]?{?"Belgium",?"Denmark",?"Finland",?"France",?"Germany",?"Ireland",?"Italy",?"Norway",?"Poland",?"Portugal",?"Spain",?"Sweden",?"Switzerland",?"UK"?}.Contains(c.BCSFindCountry)???"Yes"?:?"No"?}?into?g
????????????????????????select?new
????????????????????????{
????????????????????????????CountryAmount?=?g.Count(),
????????????????????????????IsEuropeCountry?=?g.Key.IsEuropeCountry
????????????????????????};

?6.按Customer的不同Country進行分組，并顯示每個分組的Customer

View Code var?query?=?from?c?in?MyCustomers
?????????????join?g?in?query.ToList()?on?c.BCSFindCountry?equals?g.Country
?????????????????????????orderby?g.Country?descending
?????????????????????????select?new
?????????????????????????{
?????????????????????????????g.Country,
?????????????????????????????g.CustomerNumbers,
?????????????????????????????c.BCSFindContactName
?????????????????????????};

7.顯示含有5個以上Customer的Country

View Code var?query?=?from?c?in?MyCustomers
?????????????group?c?by?c.BCSFindCountry?into?g
????????????????????????where?g.Count()?>?5
????????????????????????orderby?g.Count()?descending
????????????????????????select?new
????????????????????????{
????????????????????????????Country?=?g.Key,
????????????????????????????CustomerNumbers?=?g.Count()
????????????????????????};

8.按Customer的Country與City進行分組(Group)

View Code var?query?=?from?c?in?MyCustomers
????????????group?c?by?new?{?c.BCSFindCity,?c.BCSFindCountry?}?into?g
????????????????????????orderby?g.Key.BCSFindCountry,?g.Key.BCSFindCity
????????????????????????select?new
????????????????????????{
????????????????????????????Country?=?g.Key.BCSFindCountry,
????????????????????????????City?=?g.Key.BCSFindCity
????????????????????????};

9.對Customer進行分頁，并提取第二頁的Customer(Skip, Take)

View Code var?query?=?(from?c?in?MyCustomers
?????????????select?c).Skip(10).Take(10);

10.提取Country包含字母"A",ContactName以"A"開頭的Customer (UnionJoin)

View Code var?query?=?(from?c?in?MyCustomers.ToList()
??????????????where?c.BCSFindCity.Contains("A")
?????????????????????????select?c).Union
???????????????????????????(from?c?in?MyCustomers
????????????????????????????where?c.BCSFindContactName.StartsWith("A")
????????????????????????????select?c).OrderBy(c?=>?c.BCSFindContactName).OrderBy(c?=>?c.BCSFindContactName);

11.提取Country包含字母"A"，ContactName以"A"開頭的Customer (ConcatJoin)?

View Code var?query?=?(from?c?in?MyCustomers.ToList()
??????????????where?c.BCSFindCity.Contains("A")
?????????????????????????select?c).Concat
??????????????????????????(from?c?in?MyCustomers
???????????????????????????where?c.BCSFindContactName.StartsWith("A")
???????????????????????????select?c).OrderBy(c?=>?c.BCSFindContactName).OrderBy(c?=>?c.BCSFindContactName);

12.提取Country包含字母"A"，ContactName以"A"開頭的Customer (InterSectJoin)

View Code var?query?=?(from?c?in?MyCustomers.ToList()
??????????????where?c.BCSFindCity.Contains("A")
?????????????????????????select?c).Intersect
??????????????????????????(from?c?in?MyCustomers
???????????????????????????where?c.BCSFindContactName.StartsWith("A")
???????????????????????????select?c).OrderBy(c?=>?c.BCSFindContactName).OrderBy(c?=>?c.BCSFindContactName);

13.提取Country包含字母"A"，ContactName以"A"開頭的Customer (ExceptJoin)

View Code var?query?=?(from?c?in?MyCustomers
???????????????where?c.BCSFindCity.Contains("A")
?????????????????????????select?c).Except
??????????????????????????(from?c?in?MyCustomers
???????????????????????????where?c.BCSFindContactName.StartsWith("A")
???????????????????????????select?c).OrderBy(c?=>?c.BCSFindContactName).OrderBy(c?=>?c.BCSFindContactName);

14.顯示有Order的Customer及他的Orders(Join)?

View Code var?query?=?from?c?in?MyCustomers.ToList()
????????????join?o?in?MyOrders?on?c.BCSFindCustomerID?equals?o.BCSFindCustomerID
????????????????????????select?new
????????????????????????{
????????????????????????????c.BCSFindCustomerID,
????????????????????????????c.BCSFindCompanyName,
????????????????????????????c.BCSFindContactName,
????????????????????????????c.BCSFindCountry,
????????????????????????????c.BCSFindCity,
????????????????????????????o.BCSFindOrderID,
????????????????????????????o.BCSFindEmployeeID,
????????????????????????????o.BCSFindShipCity,
????????????????????????????o.BCSFindShipCountry,
????????????????????????????o.BCSFindShipVia,
????????????????????????????o.BCSFindRequiredDate
????????????????????????};

15.顯示Customer及他的Orders(LeftJoin)

View Code var?query?=?from?c?in?MyCustomers.ToList()
?????????????join?o?in?MyOrders?on?c.BCSFindCustomerID?equals?o.BCSFindCustomerID
????????????????????????into?leftjoin
????????????????????????from?fnresult?in?leftjoin.DefaultIfEmpty()
????????????????????????select?fnresult;

16.顯示Order數大于5的Customer

View Code var?query?=?(from?c?in?MyCustomers.ToList()
??????????????join?o?in?MyOrders?on?c.BCSFindCustomerID?equals?o.BCSFindCustomerID
?????????????????????????where?(from?o1?in?MyOrders
????????????????????????????????group?o1?by?o1.BCSFindCustomerID?into?g
????????????????????????????????where?g.Count()?>?5
????????????????????????????????select?g.Key).Contains(c.BCSFindCustomerID)
?????????????????????????select?new
?????????????????????????{
?????????????????????????????c.BCSFindCustomerID,
?????????????????????????????c.BCSFindContactName,
?????????????????????????????o.BCSFindOrderID
?????????????????????????}).OrderBy(c?=>?c.BCSFindContactName);

17.獲取指定用戶"ALFKI"的Order

View Code var?query?=?from?c?in?MyCustomers.ToList()
????????????join?o?in?MyOrders?on?c.BCSFindCustomerID?equals?o.BCSFindCustomerID
????????????????????????where?c.BCSFindCustomerID?==?"ALFKI"
????????????????????????select?new
????????????????????????{
????????????????????????????c.BCSFindCustomerID,
????????????????????????????c.BCSFindCompanyName,
????????????????????????????c.BCSFindContactName,
????????????????????????????c.BCSFindCountry,
????????????????????????????c.BCSFindCity,
????????????????????????????o.BCSFindOrderID,
????????????????????????????o.BCSFindEmployeeID,
????????????????????????????o.BCSFindShipCity,
????????????????????????????o.BCSFindShipCountry,
????????????????????????????o.BCSFindShipVia,
????????????????????????????o.BCSFindRequiredDate
????????????????????????};

18.獲取指定用戶"ALFKI"的Order，并計算每個Order的總金額BCSFindUnitPrice * BCSFindQuantity

View Code var?query?=?from?c?in?MyCustomers.ToList()
?????????????join?o?in?MyOrders?on?c.BCSFindCustomerID?equals?o.BCSFindCustomerID
????????????????????????join?cd?in?MyOrderDetails?on?o.BCSFindOrderID?equals?cd.BCSFindOrderID
????????????????????????where?c.BCSFindCustomerID?==?CustomerIDstr
????????????????????????select?new
????????????????????????{
????????????????????????????c.BCSFindCustomerID,
????????????????????????????c.BCSFindCompanyName,
????????????????????????????c.BCSFindContactName,
????????????????????????????c.BCSFindCountry,
????????????????????????????c.BCSFindCity,
????????????????????????????o.BCSFindOrderID,
????????????????????????????o.BCSFindEmployeeID,
????????????????????????????o.BCSFindShipCity,
????????????????????????????o.BCSFindShipCountry,
????????????????????????????o.BCSFindShipVia,
????????????????????????????o.BCSFindRequiredDate,
????????????????????????????cd.BCSFindProductID,
????????????????????????????cd.BCSFindQuantity,
????????????????????????????cd.BCSFindDiscount,
????????????????????????????cd.BCSFindUnitPrice,
????????????????????????????TotalCost?=?cd.BCSFindUnitPrice?*?cd.BCSFindQuantity
????????????????????????};

19.獲取指定用戶"ALFKI"所Order的產品的有關信息(3 tables)

View Code var?query1?=?from?c?in?MyCustomers.ToList()
?????????????join?o?in?MyOrders?on?c.BCSFindCustomerID?equals?o.BCSFindCustomerID
?????????????????????????join?cd?in?MyOrderDetails?on?o.BCSFindOrderID?equals?cd.BCSFindOrderID
?????????????????????????join?p?in?MyProducts?on?cd.BCSFindProductID?equals?p.BCSFindProductID
?????????????????????????where?c.BCSFindCustomerID?==?"ALFKI"
?????????????????????????select?new
?????????????????????????{
?????????????????????????????c.BCSFindCustomerID,
?????????????????????????????o.BCSFindOrderID,
?????????????????????????????p.BCSFindProductName,
?????????????????????????????cd.BCSFindQuantity,
?????????????????????????????c.BCSFindCompanyName,
?????????????????????????????c.BCSFindContactName,
?????????????????????????????c.BCSFindCountry,
?????????????????????????????c.BCSFindCity,
?????????????????????????????o.BCSFindEmployeeID,
?????????????????????????????o.BCSFindShipCity,
?????????????????????????????o.BCSFindShipCountry,
?????????????????????????????o.BCSFindShipVia,
?????????????????????????????o.BCSFindRequiredDate,
?????????????????????????????cd.BCSFindProductID,
?????????????????????????????cd.BCSFindDiscount,
?????????????????????????????cd.BCSFindUnitPrice,
?????????????????????????????p.BCSFindSupplierID
?????????????????????????};

20.獲取Order了產品"CHAI"的用戶

View Code var?query?=?from?c?in?MyCustomers
?????????????where?(from?c1?in?MyCustomers.ToList()
???????????????????????????????join?o?in?MyOrders?on?c1.BCSFindCustomerID?equals?o.BCSFindCustomerID
???????????????????????????????join?cd?in?MyOrderDetails?on?o.BCSFindOrderID?equals?cd.BCSFindOrderID
???????????????????????????????join?p?in?MyProducts?on?cd.BCSFindProductID?equals?p.BCSFindProductID
???????????????????????????????where?p.BCSFindProductName?==?"Chai"
???????????????????????????????select?c1.BCSFindCustomerID).Contains(c.BCSFindCustomerID)
????????????????????????select?c;

21.獲取Order了產品"CHAI"的用戶以及他們所Order的產品"CHAI"的OrderDetails

View Code var?query?=?from?c?in?MyCustomers.ToList()
????????????join?o?in?MyOrders?on?c.BCSFindCustomerID?equals?o.BCSFindCustomerID
????????????????????????join?cd?in?MyOrderDetails?on?o.BCSFindOrderID?equals?cd.BCSFindOrderID
????????????????????????join?p?in?MyProducts?on?cd.BCSFindProductID?equals?p.BCSFindProductID
????????????????????????where?(from?c1?in?MyCustomers.ToList()
???????????????????????????????join?o1?in?MyOrders?on?c1.BCSFindCustomerID?equals?o1.BCSFindCustomerID
???????????????????????????????join?cd1?in?MyOrderDetails?on?o1.BCSFindOrderID?equals?cd1.BCSFindOrderID
???????????????????????????????join?p1?in?MyProducts?on?cd1.BCSFindProductID?equals?p1.BCSFindProductID
???????????????????????????????where?p1.BCSFindProductName?==?"Chai"
???????????????????????????????select?c1.BCSFindCustomerID).Contains(c.BCSFindCustomerID)
???????????????????????????????&&
???????????????????????????????p.BCSFindProductName?==?"Chai"
????????????????????????select?new
????????????????????????{
????????????????????????????customerName?=?c.BCSFindContactName,
????????????????????????????ProductName?=?p.BCSFindProductName,
????????????????????????????UnitPrice?=?cd.BCSFindUnitPrice,
????????????????????????????Quantity?=?cd.BCSFindQuantity,
????????????????????????????SellTotal?=?cd.BCSFindUnitPrice?*?cd.BCSFindQuantity

????????????????????????};

轉載于:https://www.cnblogs.com/wsdj-ITtech/archive/2011/11/03/2232530.html

總結

以上是生活随笔為你收集整理的Sharepoint学习笔记---Linq to Sharepoint--查询语法的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：解决dataTable 报错：canno
下一篇： Linux whoami命令、Linux