Linq to sql（四）：查询句法(三)

分页

 

描述：按照每页10条记录，查询第二页的顾客

查询句法：

var 分页 = (from c in ctx.Customers select c).Skip(10).Take(10);

对应SQL：

SELECT TOP 10 [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]

FROM (

    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]) AS [ROW_NUMBER], [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]

    FROM [dbo].[Customers] AS [t0]

    ) AS [t1]

WHERE [t1].[ROW_NUMBER] > @p0

-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10]

 

分组

 

描述：根据顾客的国家分组，查询顾客数大于5的国家名和顾客数

查询句法：

        var 一般分组 = from c in ctx.Customers

                   group c by c.Country into g

                   where g.Count() > 5

                   orderby g.Count() descending

                   select new

                   {

                       国家 = g.Key,

                       顾客数 = g.Count()

                   };

对应SQL：

SELECT [t1].[Country], [t1].[value3] AS [顾客数]

FROM (

    SELECT COUNT(*) AS [value], COUNT(*) AS [value2], COUNT(*) AS [value3], [t0].[Country]

    FROM [dbo].[Customers] AS [t0]

    GROUP BY [t0].[Country]

    ) AS [t1]

WHERE [t1].[value] > @p0

ORDER BY [t1].[value2] DESC

-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]

 

描述：根据国家和城市分组，查询顾客覆盖的国家和城市

查询句法：

     var 匿名类型分组 = from c in ctx.Customers

                     group c by new { c.City, c.Country } into g

                     orderby g.Key.Country, g.Key.City

                     select new

                     {

                         国家 = g.Key.Country,

                         城市 = g.Key.City

                     };

对应SQL：

SELECT [t1].[Country], [t1].[City]

FROM (

    SELECT [t0].[City], [t0].[Country]

    FROM [dbo].[Customers] AS [t0]

    GROUP BY [t0].[City], [t0].[Country]

    ) AS [t1]

ORDER BY [t1].[Country], [t1].[City]

 

描述：按照是否超重条件分组，分别查询订单数量

查询句法：

var 按照条件分组 = from o in ctx.Orders

                     group o by new { 条件 = o.Freight > 100 } into g

                     select new

                     {

                         数量 = g.Count(),

                         是否超重 = g.Key.条件 ? "是" : "否"

                     };