Query操作符详解

 1.Where，过滤条件

比如：

var expr =
    customers
    .Where((c, index) => (c.Country == Countries.Italy && index >= 1))
    .Select(c => c.Name);
2.选择，包括Select, SelectMany

比如：

var orders =
    customers
    .Where(c => c.Country == Countries.Italy)
    .Select(c => c.Orders);

 

IEnumerable<Order> orders =
    customers
    .Where(c => c.Country == Countries.Italy)
    .SelectMany(c => c.Orders);
区别在于如果返回数组时，SelectMany可以自动进行合并结果。

 

3.排序，包括Orderby, OrderByDescending,ThenBy, ThenByDescending,Reverse

比如：

var expr =
    customers
    .Where(c => c.Country == Countries.Italy)
    .OrderByDescending(c => c.Name)
    .ThenBy(c => c.City)
    .Select(c => new { c.Name, c.City } )
    .Reverse();

4.分组，包括GroupBy

var expr =
    customers
    .GroupBy(c => c.Country, c => c.Name);

5.连接，包括Join,GroupJoin(类似LeftJoin,RightJoin)

var expr =
    customers
    .SelectMany(c => c.Orders)
    .Join( products,
           o => o.IdProduct,
           p => p.IdProduct,
           (o, p) => new {o.Month, o.Shipped, p.IdProduct, p.Price });

 从SQL的角度，可以理解为：

SELECT     o.Month, o.Shipped, p.IdProduct, p.Price
FROM       Orders AS o
INNER JOIN Products AS p
      ON   o.IdProduct = p.IdProduct

6.集合操作符，包括Distinct,Union/Intersect/Except

比如：

var expr =
    customers
    .SelectMany(c => c.Orders)
    .Join(products,
          o => o.IdProduct,
          p => p.IdProduct,
          (o, p) => p)
    .Distinct();
 

7.聚集操作符，包括Sum,Count,Min/Max,Average,Aggregate（如果缺少基本的操作符时，可以构造复杂的计算结果）

比如：

var expr =
    from   c in customers
    select new {c.Name, c.City, c.Country, OrdersCount = c.Orders.Count() };
 

var expr =
    from   c in customers
    join   o in (
           from c in customers
               from   o in c.Orders
               join   p in products
                      on o.IdProduct equals p.IdProduct
               select new { c.Name, o.IdProduct,
                            OrderAmount = o.Quantity * p.Price }
           ) on c.Name equals o.Name
           into orders
    select new { c.Name,
                 MaxOrderAmount =
                     orders
                     .Aggregate((t, s) => t.OrderAmount > s.OrderAmount ?
                                          t : s)
                     .OrderAmount };

8.产生操作符，包括Range,Repeat,Empty

比如：

var expr =
    Enumerable.Repeat( (from   c in customers
                      select c.Name), 2)
    .SelectMany(x => x);

9.数量操作符，存在不存在，包括Any,All,Contains

比如：

bool result =
    (from c in customers
         from   o in c.Orders
         select o)
    .Any(o => o.IdProduct == 1);

result = Enumerable.Empty<Order>().Any(o => o.IdProduct == 1);

10.循环操作符，包括Take,TakeWhile,Skip,SkipWhile

比如：

var topTwoCustomers =
    (from    c in customers
     join    o in (
             from c in customers
                 from   o in c.Orders
                 join   p in products
                        on o.IdProduct equals p.IdProduct
                 select new { c.Name, OrderAmount = o.Quantity * p.Price }
             ) on c.Name equals o.Name
             into customersWithOrders
     let     TotalAmount = customersWithOrders.Sum(o => o.OrderAmount)
     orderby TotalAmount descending
     select  new { c.Name, TotalAmount }
    ).Take(2);

11.元素操作符，包括First, FirstOrDefault,Last,LastOrDefault,Single,SingleOrDefault,ElementAt, ElementAtOrDefault, DefaultEmpty

比如：

var item = customers.FirstOrDefault(c => c.City == "Las Vegas");
Console.WriteLine(item == null ? "null" : item.ToString()); // returns null

IEnumerable<Customer> emptyCustomers = Enumerable.Empty<Customer>();
item = emptyCustomers.FirstOrDefault(c => c.City == "Las Vegas");
Console.WriteLine(item == null ? "null" : item.ToString()); // returns null

12.连接操作符，合并结果，包括：Concat,SequenceEqual

比如：

 

var italianCustomers =
    from   c in customers
    where  c.Country == Countries.Italy
    select c;

var americanCustomers =
    from   c in customers
    where  c.Country == Countries.USA
    select c;

var expr = italianCustomers.Concat(americanCustomers);