LinQ 查询

查询公司名称及地址

var 构建匿名类型=from c in ctx.Customers
                           select new
                          {
                                公司名=c.CompanyName,
                   地址=c.Address
                          };                    

查询订单号和订单是否超重

var select 带条件=from o in ctx.Orders
              select new
              {
                  订单号=o.OrderID,
                  是否超重=o.Freight>100?"":""
              };

查询雇员雇佣年份和名,按照年倒序,按照名正序

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
    };

分页(每页十条记录,查询第二页顾客)

var 分页=(from c in ctx.Customers select c).Skip(10).Take(10);

查询顾客覆盖国家

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

查询A开头城市包含A的顾客按照顾客名字排序

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

同上,相同顾客信息不过滤

var 连接并且不过滤相同项=(from c in ctx.Customers where c.City.Contains("A") select c).Concat
            (from c in ctx.Customers where c.ContactName.StartWith("A") select c).OrderBy(c=>c.ContactName);

查询A开头城市包含A的顾客的交集,按照顾客名字排序

var 连接并且不过滤相同项=(from c in ctx.Customers where c.City.Contains("A") select c).Intersect
            (from c in ctx.Customers where c.ContactName.StartWith("A") select c).OrderBy(c=>c.ContactName);

查询包含A的顾客并从中删除A开头城市的顾客,按照顾客名字排序  

var 连接并且不过滤相同项=(from c in ctx.Customers where c.City.Contains("A") select c).Except
            (from c in ctx.Customers where c.ContactName.StartWith("A") select c).OrderBy(c=>c.ContactName);

子查询

var 子查询=from c in ctx.Customers
    where
      (from o in ctx.Orders group o by o.CustomerID into o where o.Count()>5 select o.Key).Contains(c.CustomerID)
    select c;

内链接,没有分类的商品查询不到

var innerjoin=from p in ctx.Products
        join c in ctx.Categories
        on p.CategoryID equals c.CategoryID
        select p.ProductName;

外连接,没有分类的商品也能查询到

var lefrjoin=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;

 

posted @ 2014-02-26 19:15  狰狞蛋子  阅读(201)  评论(0编辑  收藏  举报