LINQ 查询Select

1、最简单

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

2、自定义返回的结果

var 构建匿名类型2 = from emp in ctx.Employees

                      select new

                      {

                          姓名 = emp.LastName + emp.FirstName,

                          雇用年 = emp.HireDate.Value.Year

                      };

3、比较复杂一点的自定义返回结果

var 构建匿名类型3 = from c in ctx.Customers

                      select new

                      {

                          ID = c.CustomerID,

                          联系信息 = new

                          {

                              职位 = c.ContactTitle,

                              联系人 = c.ContactName

                          }

                      };

4、有条件判断的自定义返回结果

var select带条件 = from o in ctx.Orders

                        select new

                        {

                            订单号 = o.OrderID,

                            是否超重 = o.Freight > 100 ? "是" : "否"

                        };

5、简单的where，限制国家以及订单数量

var 多条件 = from c in ctx.Customers

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

                  select new

                  {

                      国家 = c.Country,

                      城市 = c.City,

                      订单数 = c.Orders.Count

                  };  

6、orderby语句

 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

                 };   

7、分页，skip跳过几条，take选择几条， pageSize*(pageIndex-1)  pageSize

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

8、简单分组 group

  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()

                   };

9、自定义分组

     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

                     };

10、按照条件分组

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

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

                     select new

                     {

                         数量 = g.Count(),

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

                     };

11、Distinct过滤重复项

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

12、Union 连接并且过滤相同项

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

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

13、Concat 连接并且不过滤相同项

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

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

14、Intersect 取相交项

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

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

15、Except 排除相交项

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

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

16、子查询

var 子查询 = from c in ctx.Customers

                   where

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

                   select c;

17、in查询

       var in操作 = from c in ctx.Customers

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

                    select c;

18、join 内连接，没有分类的查不到

var innerjoin = from p in ctx.Products

                        join c in ctx.Categories

                        on p.CategoryID equals c.CategoryID

                        select p.ProductName;

19、join 外连接，没有分类的也可以查到

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;

转载于:https://www.cnblogs.com/lrogi/archive/2011/05/05/2038262.html