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

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

 2.查询所有有定单的Customer 

 var query = from c in MyCustomers

             where (from o in MyOrders

                                   select o.BCSFindCustomerID).Contains(c.BCSFindCustomerID)

                            select c;

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

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

 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数目 

 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

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

 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)

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)

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

 

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

 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) 

 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)

 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)

 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) 

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)

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

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

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

 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)

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

 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

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

                        };