Linq to SQL 语法记录....WHERE & ORDER BY & GROUP BY

WHERE

描述：查询顾客的国家、城市和订单数信息，要求国家是法国并且订单数大于5

查询句法：

var 多条件 = from c in ctx.Customers

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

                  select new

                  {

                      国家 = c.Country,

                      城市 = c.City,

                      订单数 = c.Orders.Count

                  };      

ORDER BY

描述：查询所有没有下属雇员的雇用年和名，按照雇用年倒序，按照名正序

查询句法：

  var 排序 = from emp in ctx.Employees

                 where emp.Employees.Count == 0

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

                 select new

                 {

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

                     名 = emp.FirstName

                 };     

GROUP BY

描述：根据顾客的国家分组，查询顾客数大于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()

                   };

描述：根据国家和城市分组，查询顾客覆盖的国家和城市

查询句法：

     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

                     };

描述：按照是否超重条件分组，分别查询订单数量

查询句法：

var 按照条件分组 = from o in ctx.Orders

                     group o by new { 条件 = o.Freight > 100 } into g

                     select new

                     {

                         数量 = g.Count(),

                         是否超重 = g.Key.条件 ? "是" : "否"

                     };