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

                        };