初识LINQ 2

初识LINQ 中已经对Linq有了一个基本的认识，也学习了Linq的 条件查询，基本函数（count，max，min），排序等一些基本语法，接下来我们接着上节继续学习Linq的剩下的语法知识

取第一条数据

//用法一
var res=(from r in db.UserInfo
         select r).FirstOrDefault();
//用法二
var res=db.UserInfo.FirstOrDefault();
//用法三
string res="select top(1) from UserInfo";

 跳过前面n条数据，取n条之后的数据

//跳过前10条，取10条之后的所有数据
//方法一
var res=(from r in db.UserInfo
orderby r.UserID desending
select r).Skip(10);
//方法二
var res=db.UserInfo.OrderbyDescending(p=>p.UserInfo).Skip(10).ToList();
//方法三
var res="select * from (select Row_Number() over (order by UserID desc) rowNum,* from UserInfo) as t where rowNum>10";

模糊查询 like '%%'

//查询姓王的用户
方法一
var res=from r in db.UserInfo
where r.UserName.Contains("王")
select r;
方法二
var res=db.UserInfo.Where(p=>p.UserName.Contains("王")).ToList();
方法三
var res="select * from UserInfo where UserName like '%王%'";

in 

//方法一
var  res=from r in db.UserInfo
        where (new int?[] {24,25,26}).Contains(r.UserInfo)
        select r;

//方法二
string res="select * from UserInfo where UserID in (24,25,26)";

分组

//方法一
var res=from r in db.Userinfo
        orderby r.UserID descending
        groupb r by r.UserSex into n
        select new 
        {
        n.Key,//Key 是UserSex 
        UserID=n.sum(r=>r.UserID),//组内UserID的和
        MaxUserID=n.Max(r=>r.UserID)，//组内最大的的和
        MinUserID=n.Min(r=>r.UserID)//组内最小的的和
        };
foreach (var t in res)
{
    Response.Write(t.Key + "--" + t.UserID+ "--" + t.MaxUserID + "--" + t.MaxUserID);
}
//方法二
var res= from r in db.Userinfo
        orderby r.UserID descending
        groupb r by r.UserSex into n
        select n;
foreach (var t in res)
{
     Response.Write(t.Key + "--" + t.Min(p => p.UserID ));
}
//方法三
var res = db.Userinfo.GroupBy(p => p.UserSex );
foreach (var t in res )
{
    Response.Write(t.Key + "--" + t.Min(p => p.UserID ));
}
//方法四
string res = "select UserSex ,min(UserID ),max(UserID ),sum(UserID ) from Userinfogroup by UserSex";

连接查询

//方法一
var res =from r in db.UerInfo
        join d db.UserDepartment on r.UserID equals d.UserID
        orderby r.UserID descending
        select r;
//方法二
var res=db.UserInfo.join(db.UserDepartment,p=>p.UserID,r=>r.UserID,(p,r)=>p).OrderByDescending(p=>p.UserID).ToList();
//方法三
string res="select r.* from UserInfo as r inner join UserDepartment as d on r.UserID=d.UserID order by UserID desc";

分页

//方法一
var res=(from r in db.UserInfo
        where r.UserID>10
        orderby r.UserID descening
        select r).Skip(10).Take(10);//从11条取到20条数据
//方法二
var res=db.UserInfo.OrderByDescending(p=>p.UserID>10).Skip(10).Take(10).ToList();
//方法三
strng res="select * from (select ROW_Number() over (order by UserID desc) as rownumber,* from UserInfo) as t where rowNumber>10 and rowNumber<=20";