博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
Sharepoint学习笔记---Linq to Sharepoint--查询语法
阅读量：5840 次
发布时间：2019-06-18

本文共 8864 字，大约阅读时间需要 29 分钟。

  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 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查询)  

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

 2.查询所有有定单的Customer 

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

 3.查询所有没有定单的Customer 

ExpandedBlockStart.gif
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是否属于欧洲国家

ExpandedBlockStart.gif
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数目 

ExpandedBlockStart.gif
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

ExpandedBlockStart.gif
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

ExpandedBlockStart.gif
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)

ExpandedBlockStart.gif
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)

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

 

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

ExpandedBlockStart.gif
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) 

ExpandedBlockStart.gif
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)

ExpandedBlockStart.gif
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)

ExpandedBlockStart.gif
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) 

ExpandedBlockStart.gif
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)

ExpandedBlockStart.gif
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

ExpandedBlockStart.gif
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

ExpandedBlockStart.gif
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

ExpandedBlockStart.gif
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)

ExpandedBlockStart.gif
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"的用户

ExpandedBlockStart.gif
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

ExpandedBlockStart.gif
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
                        };

 

 

转载地址：http://cpvcx.baihongyu.com/

你可能感兴趣的文章
java.lang.NoClassDefFoundError: org/apache/juli/logging/LogFactory的解决
查看>>
eclipse介绍
查看>>
题解 201809021测试 T2羊羊吃草
查看>>
.NET Mvc
查看>>
【规范】yii2 resetful 授权验证
查看>>
【天天数据结构和算法】PHP实现二分查找的两种方法
查看>>
java 基本数据类型跟封装类型的差距
查看>>
访问控制 - C++快速入门18
查看>>
使用泛型与不使用泛型的Map的遍历
查看>>
arduino
查看>>
第六章学习小结_初识图
查看>>
112. Path Sum(判断路径和是否等于一个数)
查看>>
java基础 - 综合训练二
查看>>
（转）成为优秀技术人员的两点建议
查看>>
VNC Viewer连接Cent OS 时的 复制粘帖 功能
查看>>
20162329张旭升 2018-2019-2《网络对抗技术》第1周 Kali的安装
查看>>
【转帖】噪声库（noiseX-92）
查看>>
排序算法--桶排序
查看>>
mybatis 高级映射和spring整合之逆向工程（7）
查看>>
机器学习进阶-案例实战-停车场车位识别
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2024-12-24 10:50:27   当前IP: 3.141.12.236   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我