一、本将主要介绍内容
从linq,sql,lambda三个角度比较来学习
select、orderby、分页、group by、distinct、子查询、in的用法
1.select
查询用户和它们的自我介绍
Linq to sql
fromainBlog_UserInfoselectnew{
真实名字=a.RealName,
自我介绍=a.Introduce
}
sql
SELECT[t0].[RealName]AS[真实名字],[t0].[Introduce]AS[自我介绍]FROM[Blog_UserInfo]AS[t0]
Lambda
Blog_UserInfo
.Select (
a=>new{
真实名字=a.RealName,
自我介绍=a.Introduce
}
)
2.orderby
查询名字里带friend的用户,并排序
Linq to sql
fromainBlog_Userswherea.NickName.Contains("Friend")
orderby a.UserId ascending,
a.CreateTime descendingselecta--或者fromainBlog_Userswherea.NickName.Contains("Friend")
orderby a.UserId,a.CreateTimeselecta
sql
-- RegionParametersDECLARE@p0NVarChar(1000)='%Friend%'-- EndRegionSELECT[t0].[UserId],[t0].[NickName],[t0].[CreateTime]FROM[Blog_User]AS[t0]WHERE[t0].[NickName]LIKE@p0ORDERBY[t0].[UserId],[t0].[CreateTime]DESC
Lambda
Blog_Users
.Where (a=> a.NickName.Contains ("Friend"))
.OrderBy (a=>a.UserId)
.ThenByDescending (a=> a.CreateTime)
3.分页
按照每页2条 ,查询第2页的留言表的信息
Linq to sql
(fromainBlog_LeaveMsgsselecta).Skip(2).Take(2)
sql
-- RegionParametersDECLARE@p0Int=2DECLARE@p1Int=2-- EndRegionSELECT[t1].[ID],[t1].[ReceiverId],[t1].[LeaverId],[t1].[CreateTime],[t1].[Content]FROM(SELECTROW_NUMBER()OVER(ORDERBY[t0].[ID],[t0].[ReceiverId],[t0].[LeaverId],[t0].[CreateTime],[t0].[Content])AS[ROW_NUMBER],[t0].[ID],[t0].[ReceiverId],[t0].[LeaverId],[t0].[CreateTime],[t0].[Content]FROM[Blog_LeaveMsg]AS[t0])AS[t1]WHERE[t1].[ROW_NUMBER]BETWEEN@p0+1AND@p0+@p1ORDERBY[t1].[ROW_NUMBER]
Lambda
Blog_LeaveMsgs
.Select (a=>a)
.Skip (2)
.Take (2)
4.1分组1(group by字段)
根据用户来分组,查询留言数大于等于3条的用户ID和相应留言数量
Linq to sql
fromainBlog_LeaveMsgs
group a by a.LeaverId into bwhereb.Count() >=3selectnew{
朋友ID=b.Key,
留言数=b.Count()
}
sql
-- RegionParametersDECLARE@p0Int=3-- EndRegionSELECT[t1].[LeaverId]AS[朋友ID],[t1].[value2]AS[留言数]FROM(SELECTCOUNT(*)AS[value],COUNT(*)AS[value2],[t0].[LeaverId]FROM[Blog_LeaveMsg]AS[t0]GROUPBY[t0].[LeaverId])AS[t1]WHERE[t1].[value]>=@p0
4.2分组2(group by多个字段)
按照接收人和留言人进行分组,查看覆盖的接收人和留言人情况
Linq to sql
fromainBlog_LeaveMsgs
group a bynew{a.ReceiverId,a.LeaverId} into bselectnew{
接收人ID=b.Key.ReceiverId,
留言人ID=b.Key.LeaverId
}
sql
SELECT[t0].[ReceiverId]AS[接收人ID],[t0].[LeaverId]AS[留言人ID]FROM[Blog_LeaveMsg]AS[t0]GROUPBY[t0].[ReceiverId],[t0].[LeaverId]
Lambda
Blog_LeaveMsgs
.GroupBy (
a=>new{
ReceiverId=a.ReceiverId,
LeaverId=a.LeaverId
}
)
.Select (
b=>new{
接收人ID=b.Key.ReceiverId,
留言人ID=b.Key.LeaverId
}
)
5.distinct
查看留言表中的留言人人数
Linq to sql
(fromainBlog_LeaveMsgsselecta.LeaverId)
.Distinct()
sql
SELECTDISTINCT[t0].[LeaverId]FROM[Blog_LeaveMsg]AS[t0]
Lambda
Blog_LeaveMsgs
.Select (a=>a.LeaverId)
.Distinct ()
6.子查询
查询留言数量超过4条的用户信息
Linq to sql
fromainBlog_Userswhere(frombinBlog_LeaveMsgs
group b by b.LeaverId into bwhereb.Count()>=4selectb.Key).Contains(a.UserId)selecta
sql
-- RegionParametersDECLARE@p0Int=4-- EndRegionSELECT[t0].[UserId],[t0].[NickName],[t0].[CreateTime]FROM[Blog_User]AS[t0]WHEREEXISTS(SELECTNULLAS[EMPTY]FROM(SELECTCOUNT(*)AS[value],[t1].[LeaverId]FROM[Blog_LeaveMsg]AS[t1]GROUPBY[t1].[LeaverId])AS[t2]WHERE([t2].[LeaverId]=([t0].[UserId]))AND([t2].[value]>=@p0)
)
Lambda
Blog_Users
.Where (
a=>Blog_LeaveMsgs
.GroupBy (b=>b.LeaverId)
.Where (b=> (b.Count () >=4))
.Select (b=>b.Key)
.Contains ((Int32?)(a.UserId))
)
7.in操作
查询制定用户昵称的用户
Linq to sql
fromainBlog_Userswherenewstring[]{"Kimisme","FriendLee"}
.Contains(a.NickName)selecta
sql
-- RegionParametersDECLARE@p0NVarChar(1000)='Kimisme'DECLARE@p1NVarChar(1000)='FriendLee'-- EndRegionSELECT[t0].[UserId],[t0].[NickName],[t0].[CreateTime]FROM[Blog_User]AS[t0]WHERE[t0].[NickName]IN(@p0,@p1)
Lambda
Blog_Users
.Where (a=>newString[] {"Kimisme","FriendLee"} .Contains (a.NickName))