一步一步学Linq to sql（四）：查询语法

from 表中的元素 in表

where 查询条件

select new{

           new一个新对象的属性

           };

    protected void Page_Load(object sender, EventArgs e)    {        linqDBDataContext linqDB = new linqDBDataContext();        //一、select         var result1 = from c in linqDB.Customer                      select new                      {                          名称 = c.Name,                          城市 = c.City                      };        //查询职员的姓名和雇用年份        var result2 = from emp in linqDB.Employees                      select new                      {                          姓名 = emp.FirstName + emp.LastName,                          雇佣年 = emp.HireDate.Value.Year                      };        var result3 = from c in linqDB.Customers                      select new                      {                          ID = c.CustomerID,                          联系信息 = new                          {                              职位 = c.ContactTitle,                              联系人 = c.ContactName                          }                      };        //查询订单号和订单是否超重的信息        var result4 = from o in linqDB.Orders                      select new                      {                          订单号 = o.OrderID,                          是否超重 = o.Freight > 100 ? "是" : "否"                      };        //二、where         //要求国家是美国并且订单数大于5        var result5 = from c in linqDB.Customers                      where c.Country == "美国" && c.Orders > 5                      select new                      {                          国家 = c.Country,                          城市 = c.City,                          订单数 = c.Orders                      };        //三、orderby        // 按照雇用年倒序，按照名正序        var result6 = from emp in linqDB.Employees                      orderby emp.HireDate.Value.Year ascending, emp.FirstName descending                      select new                      {                          姓名 = emp.FirstName + emp.LastName,                          雇佣年 = emp.HireDate.Value.Year                      };        //四、分页        var result7 = (from c in linqDB.Customers select c).Skip(10).Take(10);        //五、分组        //根据顾客的国家分组，查询顾客数大于5的国家名和顾客数        var result8 = from c in linqDB.Customers                      group c by c.Country into g                      where g.Count() > 5                      orderby g.Count() descending                      select new                      {                          国家 = g.Key,                          顾客数 = g.Count()                      };        //根据国家和城市分组，查询顾客覆盖的国家和城市        var result9 = from c in linqDB.Customers                      group c by new { c.Country, c.City } into g                      orderby g.Key.Country, g.Key.City                      select new                      {                          国家 = g.Key.Country,                          城市 = g.Key.City                      };        //按照是否超重条件分组，分别查询订单数量        var result10 = from o in linqDB.Orders                       group o by new { 条件 = o.Freight > 100 } into g                       select new                       {                           数量 = g.Count(),                           是否超重 = g.Key.条件 ? "是" : "否"                       };        //六、distinct        //查询顾客覆盖的国家        var result11 = (from c in linqDB.Customers orderby c.Country select c.Country).Distinct();                //七、concat 连结        //查询城市是"旧"打头的顾客和顾客名称包含"小"的顾客，并按照顾客名字排序，相同的顾客信息不会过滤        var result12 = (from c in linqDB.Customers where c.City.StartsWith("旧") select c).Concat              (from c in linqDB.Customers where c.ContactName.Contains("小") select c).OrderBy(c => c.ContactName);        //八、取相交项        //查询城市是"旧"打头的顾客和顾客名称包含"小"的顾客，的交集，并按照顾客名字排序        var result13 = (from c in linqDB.Customers where c.City.StartsWith("旧") select c).Intersect              (from c in linqDB.Customers where c.ContactName.Contains("小") select c).OrderBy(c => c.ContactName);        //九、排除相交项        //查询城市是"旧"打头的顾客,并从中删除顾客名称包含"小"的顾客，并按照顾客名字排序        var result14 = (from c in linqDB.Customers where c.City.StartsWith("旧") select c).Except           (from c in linqDB.Customers where c.ContactName.Contains("小") select c).OrderBy(c => c.ContactName);        //十、子查询        //查询订单数超过1的顾客信息        var result15 = from c in linqDB.Customers                       where                       (from o in linqDB.Orders group o by o.CustomerID into o where o.Count() > 1 select o.Key).Contains(c.CustomerID)                       select c;        //十一、in操作        //描述：查询指定城市中的客户        var result16 = from c in linqDB.Customers                       where new string[] { "北京", "旧金山" }.Contains(c.City)                       select c;        //十二、join(相当于SQL语句的INNER JOIN)        //内连接，没有分类的产品查询不到        var result17 = from l in linqDB.lb                       join p in linqDB.Products                       on l.id equals p.lbId                       select p.ProductsName;        //十三、        //外连接，没有分类的产品也能查询到(相当于SQL语句的LEFT JOIN )        var result18 = from l in linqDB.lb                       join p in linqDB.Products                       on l.id equals p.lbId                       into pro                       from x in pro.DefaultIfEmpty()                       select new                       {                           l.lbName,                           x.ProductsName                       };        //十四        //视图        var result = from v in linqDB.View1                     select v;        GridView1.DataSource = result;        GridView1.DataBind();    }