Linq简单查询

创建Persons表和Cards表

if exists(select * form sysobjects where name='Persons') 
drop table Persons 
go Create table Persons
(
Id int primary key identity(1,1),
Name nvarchar(50),
Sex bit,
Age smallint
); 
go 
if exists(select * form sysobjects where name='Cards') 
drop table Cards 
go
 create table Cards
 ( 
Id int primary key identity(1,1),
 Name nvarchar(50), 
UserId int--外键 )
;

//查询所有的信息
Persons.Select
(
 p => 
    new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }
)
//查询指定字段的信息
Persons.Select
(
 p=>
    new 
    {
        Name = p.Name, 
        Sex = p.Sex 
    }
)
//查询所有性别为男的信息
Persons.Where(p=>p.Sex==true).Select
(
 p => 
    new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }
)
//查询所有性别为男的信息按年龄降序排序，默认升序
Persons.Where(p=>p.Sex==true).OrderByDescending(p=>p.Age).Select
(
p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }
)
//查询用户名为包含a的唯一值
Persons.Where(p=>p.Name=="aa").OrderByDescending(p=>p.Age).Select
(
p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }
).Distinct()
//查询第一条数据一般返回实体类没有数据返回null
Persons.Where(p=>p.Sex==true).OrderByDescending(p=>p.Age).Select
(
p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }
).FirstOrDefault()
//查询第一条数据一般返回实体类没有数据会报错
Persons.Where(p=>p.Sex==true).OrderByDescending(p=>p.Age).Select
(
p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }
).First()
//查询性别为男的人数
Persons.Where(p=>p.Sex==true).Select
(p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }).Count()
//查询性别为男的总年龄
Persons.Where(p=>p.Sex==true).Select
(p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }).Sum(p=>p.Age)
//查询性别为男的最大年龄
Persons.Where(p=>p.Sex==true).Select
(p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }).Max(p=>p.Age)
//查询性别为男的最小年龄
Persons.Where(p=>p.Sex==true).Select
(p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }).Min(p=>p.Age)
//查询性别为男的平均值
Persons.Where(p=>p.Sex==true).Select
(p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }).Average(p=>p.Age)
//查出性别为男年龄从大到小前3条的数据
Persons.Where(p=>p.Sex==true).OrderByDescending(p=>p.Age).Select(
p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }
    ).Take(3)
//分页PageIndex为当前页，PageSize为显示的数量
Persons.Where(p=>p.Sex==true).OrderByDescending(p=>p.Age).Select(
p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }
    ).Skip((1-1)*2).Take(2)
//分页公式
Persons.Where(p=>p.Sex==true).OrderByDescending(p=>p.Age).Select(
p=>
   new  
    {
       Id = p.Id, 
       Name = p.Name, 
       Sex = p.Sex, 
       Age = p.Age
    }
    ).Skip((PageIndex-1)*PageSize).Take(PageSize)
//按id分组性别为男,数量大于等于1   查询编号,数量,平均数,最大值,最小值,和
Persons.Where(p=>p.Sex==true).GroupBy (p => p.Id).Where (g => (g.Count () >=1))
       .OrderByDescending(g=>g.Average(p=>p.Age)).Select
(
      g => 
         new  
         {
            PersonsId=g.Key,
            PersonsCount = g.Count(),
            PersonsAvg=g.Average(p=>p.Age),
            PersonsSum=g.Sum(p=>p.Age),
            PersonsMax=g.Max(p=>p.Age),
            PersonsMin=g.Min(p=>p.Age)
         }
)
//无条件的多表查询 join                      
Persons.Join 
(
      Cards, 
      pp => (Int32?)(pp.Id), 
      cc => cc.UserId, 
      (pp, cc) => 
         new  
         {
            Id = pp.Id,
            Name=pp.Name,
            type=cc.Name
         }
 )
 //有条件的多表查询
 Persons.Join 
 (
      Cards, 
      a => (Int32?)(a.Id), 
      b => b.UserId, 
      (a, b) => 
         new  
         {
            a = a, 
            b = b
         }
  ).Where (temp => (temp.b.Name == "中国农业银行")).Select 
  (
      temp => 
         new  
         {
            id = temp.a.Id, 
            name = temp.b.Name,
            userName=temp.a.Name
         }
   )