Linq学习(三)-基本查询

王朝学院·作者佚名  2016-08-28
窄屏简体版  字體: 小  |  中  |  大  |  超大  

一、本将主要介绍内容

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

 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
© 2005- 王朝網路 版權所有 導航