Linq Learning (III)-basic query and linq learning Query

Linq Learning (III)-basic query and linq learning Query

I. Main introduction

Learn from the perspective of comparison between linq, SQL, and lambda.

Usage of select, orderby, paging, group by, distinct, subquery, and in

1. select

Query users and their self-introductions

Linq to SQL

From a in Blog_UserInfoselect new {real name = a. RealName, self-introduction = a. Introduce}

SQL

SELECT [t0]. [RealName] AS [real name], [t0]. [Introduce] AS [self-introduction] FROM [Blog_UserInfo] AS [t0]

Lambda

Blog_UserInfo. Select (a => new {real name = a. RealName, self-introduction = a. Introduce })

2. orderby

Query the users whose names contain friend and sort them.

Linq to SQL

From a in Blog_Userswhere. nickName. contains ("Friend") orderby. userId ascending,. createTime descendingselect a -- or from a in Blog_Userswhere. nickName. contains ("Friend") orderby. userId,. createTime select

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. Paging

Query the information of the 2nd page comment table based on 2 entries per page.

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 group 1 (group by field)

Group by user, query the number of user IDs and the number of corresponding messages with the number of messages greater than or equal to 3

Linq to SQL

From a in Blog_LeaveMsgs group a by a. LeaverId into bwhere B. Count ()> = 3 select new {friend ID = B. Key, number of messages = B. Count ()}

SQL

-- Region ParametersDECLARE @ p0 Int = 3 -- EndRegionSELECT [t1]. [LeaverId] AS [friend ID], [t1]. [value2] AS [number of messages] 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 group 2 (group by multiple fields)

Groups recipients and message recipients to view the covered recipients and message recipients.

Linq to SQL

From a in Blog_LeaveMsgsgroup a by new {a. ReceiverId, a. LeaverId} into bselect new {recipient ID = B. Key. ReceiverId, contact ID = B. Key. LeaverId}

SQL

SELECT [t0]. [ReceiverId] AS [recipient ID], [t0]. [LeaverId] AS [contact person ID] FROM [Blog_LeaveMsg] AS [t0] group by [t0]. [ReceiverId], [t0]. [LeaverId]

Lambda

Blog_LeaveMsgs. groupBy (a => new {ReceiverId =. receiverId, LeaverId =. leaverId }). select (B => new {recipient ID = B. key. receiverId, contact ID = B. key. leaverId })

5. distinct

View the number of people in the message table

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. subquery

Query user information with more than 4 messages

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 operation

Query users with nickname

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