一、本將主要介紹內容
從linq，sql，lambda三個角度比較來學習
select、orderby、分頁、group by、distinct、子查詢、in的用法
1.select
查詢用戶和它們的自我介紹
Linq to sql
from a in Blog_UserInfoselect new{ 真實名字=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
from a in Blog_Userswhere a.NickName.Contains("Friend")orderby a.UserId ascending,a.CreateTime descendingselect a--或者from a in Blog_Userswhere a.NickName.Contains("Friend")orderby a.UserId,a.CreateTime select a
sql
-- Region ParametersDECLARE @p0 NVarChar(1000) = '%Friend%'-- EndRegionSELECT [t0].[UserId], [t0].[NickName], [t0].[CreateTime]FROM [Blog_User] AS [t0]WHERE [t0].[NickName] LIKE @p0ORDER BY [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
(from a in Blog_LeaveMsgs select a).Skip(2).Take(2)
sql
-- Region ParametersDECLARE @p0 Int = 2DECLARE @p1 Int = 2-- EndRegionSELECT [t1].[ID], [t1].[ReceiverId], [t1].[LeaverId], [t1].[CreateTime], [t1].[Content]FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [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 + 1 AND @p0 + @p1ORDER BY [t1].[ROW_NUMBER]
Lambda
Blog_LeaveMsgs .Select (a => a) .Skip (2) .Take (2)
4.1分組1（group by字段）
根據(jù)用戶來分組，查詢留言數(shù)大于等于3條的用戶ID和相應留言數(shù)量
Linq to sql
from a in Blog_LeaveMsgs group a by a.LeaverId into bwhere b.Count() >=3select new{ 朋友ID = b.Key, 留言數(shù) = b.Count()}
sql
-- Region ParametersDECLARE @p0 Int = 3-- EndRegionSELECT [t1].[LeaverId] AS [朋友ID], [t1].[value2] AS [留言數(shù)]FROM ( SELECT COUNT(*) AS [value], COUNT(*) AS [value2], [t0].[LeaverId] FROM [Blog_LeaveMsg] AS [t0] GROUP BY [t0].[LeaverId] ) AS [t1]WHERE [t1].[value] >= @p0
4.2分組2（group by多個字段）
按照接收人和留言人進行分組，查看覆蓋的接收人和留言人情況
Linq to sql
from a in Blog_LeaveMsgsgroup a by new{a.ReceiverId,a.LeaverId} into bselect new{ 接收人ID=b.Key.ReceiverId, 留言人ID=b.Key.LeaverId}
sql
SELECT [t0].[ReceiverId] AS [接收人ID], [t0].[LeaverId] AS [留言人ID]FROM [Blog_LeaveMsg] AS [t0]GROUP BY [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
查看留言表中的留言人人數(shù)
Linq to sql
(from a in Blog_LeaveMsgsselect a.LeaverId).Distinct()
sql
SELECT DISTINCT [t0].[LeaverId]FROM [Blog_LeaveMsg] AS [t0]
Lambda
Blog_LeaveMsgs .Select (a => a.LeaverId) .Distinct ()
6.子查詢
查詢留言數(shù)量超過4條的用戶信息
Linq to sql
from a in Blog_Userswhere(from b in Blog_LeaveMsgs group b by b.LeaverId into b where b.Count()>=4select b.Key).Contains(a.UserId)select a
sql
-- Region ParametersDECLARE @p0 Int = 4-- EndRegionSELECT [t0].[UserId], [t0].[NickName], [t0].[CreateTime]FROM [Blog_User] AS [t0]WHERE EXISTS( SELECT NULL AS [EMPTY] FROM ( SELECT COUNT(*) AS [value], [t1].[LeaverId] FROM [Blog_LeaveMsg] AS [t1] GROUP BY [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
from a in Blog_Userswhere new string[]{"Kimisme","FriendLee"}.Contains(a.NickName)select a
sql
-- Region ParametersDECLARE @p0 NVarChar(1000) = 'Kimisme'DECLARE @p1 NVarChar(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 => new String[] { "Kimisme", "FriendLee" } .Contains (a.NickName))
新聞熱點
疑難解答
