LINQ学习记（二）查询

1.基本查询

class Person
{
   public string name{get;set;}
   public int age{get;set;}    
}
//通过表达式初始化  不需要在类名后面加小括号
class Test
{
    //用匿名类型初始化集合
   var persons=new List<Person>{
        new Person{name="a",age=18},
        new Person{name="b",age=19},
        new Person{name="c",age=20}
   };
    
   //标准linq查询  
  var selectPersons=from p in persons where p.age>18  
                                select p.name; 
  //Lambda表达式写法      
  var selectPersons=persons.Where(p=>p.age>18).Select(p=>p.name);                           

}

 2.多条件查询

var 多条件 = from c in Customers

                  where c.Country == "France" && c.Orders.Count > 5

                  select new

                  {

                      国家 = c.Country,

                      城市 = c.City,

                      订单数 = c.Orders.Count

                  };      

3.排序查询orderby

  var 排序 = from emp in Employees

                 where emp.Employees.Count == 0

                 orderby emp.HireDate.Value.Year descending, emp.FirstName ascending

                 select new

                 {

                     雇用年 = emp.HireDate.Value.Year,

                     名 = emp.FirstName

                 };     

4.分页查询

var 分页 = (from c in Customers select c).Skip(10).Take(10);//Skip跳过，Take 查询

5.分组查询

        var 一般分组 = from c in ctx.Customers

                   group c by c.Country into g

                   where g.Count() > 5

                   orderby g.Count() descending

                   select new

                   {

                       国家 = g.Key,

                       顾客数 = g.Count()

                   };

 

6.多列分组、排序查询

     var 匿名类型分组 = from c in ctx.Customers

                     group c by new { c.City, c.Country } into g

                     orderby g.Key.Country, g.Key.City

                     select new

                     {

                         国家 = g.Key.Country,

                         城市 = g.Key.City

                     };

 

7.过滤

var 过滤相同项 = (from c in ctx.Customers orderby c.Country select c.Country).Distinct();

 

8.union联合（过滤相同项）

var 连接并且过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") select c).Union

            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

 

9.Concat连接（不过滤重复项）

var 连接并且不过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") selectc).Concat

            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

 

 

10.取相交项

var 取相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Intersect

            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

 

11.排除相交项

 

描述：查询城市包含A的顾客并从中删除城市以A开头的顾客，并按照顾客名字排序

查询句法：

 

var 排除相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Except

            (from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

 

12.

子查询

 

描述：查询订单数超过5的顾客信息

查询句法：

 

var 子查询 = from c in ctx.Customers

                   where

                       (from o in ctx.Orders group o by o.CustomerID into o where o.Count() > 5select o.Key).Contains(c.CustomerID)

                   select c;

 

13.

in操作

 

描述：查询指定城市中的客户

查询句法：

 

        var in操作 = from c in ctx.Customers

                    where new string[] { "Brandenburg", "Cowes", "Stavern"}.Contains(c.City)

                    select c;

 

14.

join

 

描述：内连接，没有分类的产品查询不到

查询句法：

 

var innerjoin = from p in ctx.Products

                        join c in ctx.Categories

                        on p.CategoryID equals c.CategoryID

                        select p.ProductName;

 

15.外连接

描述：外连接，没有分类的产品也能查询到

查询句法：

 

var leftjoin = from p in ctx.Products

                       join c in ctx.Categories

                       on p.CategoryID equals c.CategoryID

                       into pro

                       from x in pro.DefaultIfEmpty()

                       select p.ProductName;