Linq学习(三)-基本查询

一、本将主要介绍内容

从linq，sql，lambda三个角度比较来学习

select、orderby、分页、group by、distinct、子查询、in的用法

1.select

查询用户和它们的自我介绍

Linq to sql

from a in Blog_UserInfo

select 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_Users

where a.NickName.Contains("Friend")

orderby a.UserId ascending,

a.CreateTime descending

select a

--或者

from a in Blog_Users

where a.NickName.Contains("Friend")

orderby a.UserId,a.CreateTime

select a

sql

-- Region Parameters

DECLARE @p0 NVarChar(1000) = '%Friend%'

-- EndRegion

SELECT [t0].[UserId], [t0].[NickName], [t0].[CreateTime]

FROM [Blog_User] AS [t0]

WHERE [t0].[NickName] LIKE @p0

ORDER 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 Parameters

DECLARE @p0 Int = 2

DECLARE @p1 Int = 2

-- EndRegion

SELECT [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 + @p1

ORDER BY [t1].[ROW_NUMBER]

Lambda

Blog_LeaveMsgs

   .Select (a => a)

   .Skip (2)

   .Take (2)

4.1分组1（group by字段）

根据用户来分组，查询留言数大于等于3条的用户ID和相应留言数量

Linq to sql

from a in Blog_LeaveMsgs

group a by a.LeaverId into b

where b.Count() >=3

select new

{

    朋友ID = b.Key,

    留言数 = b.Count()

}

sql

-- Region Parameters

DECLARE @p0 Int = 3

-- EndRegion

SELECT [t1].[LeaverId] AS [朋友ID], [t1].[value2] AS [留言数]

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_LeaveMsgs

group a by new{a.ReceiverId,a.LeaverId} into b

select 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

查看留言表中的留言人人数

Linq to sql

(from a in Blog_LeaveMsgs

select a.LeaverId)

.Distinct()

sql

SELECT DISTINCT [t0].[LeaverId]

FROM [Blog_LeaveMsg] AS [t0]

Lambda

Blog_LeaveMsgs

   .Select (a => a.LeaverId)

   .Distinct ()

6.子查询

查询留言数量超过4条的用户信息

Linq to sql

from a in Blog_Users

where

(from b in Blog_LeaveMsgs

group b by b.LeaverId into b

where b.Count()>=4

select b.Key).Contains(a.UserId)

select a

sql

-- Region Parameters

DECLARE @p0 Int = 4

-- EndRegion

SELECT [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_Users

where new string[]{"Kimisme","FriendLee"}

.Contains(a.NickName)

select a

sql

-- Region Parameters

DECLARE @p0 NVarChar(1000) = 'Kimisme'

DECLARE @p1 NVarChar(1000) = 'FriendLee'

-- EndRegion

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

巴特西

Linq学习(三)-基本查询

最新文章

热门文章