一步一步学Linq to sql（四）：查询句法
[2] 一步一步学Linq to sql（四）：查询句法
[3] 一步一步学Linq to sql（四）：查询句法
[4] 一步一步学Linq to sql（四）：查询句法
[5] 一步一步学Linq to sql（四）：查询句法
[6] 一步一步学Linq to sql（四）：查询句法
系列文章导航：
一步一步学Linq to sql（二）：DataContext与实体
分页
描述：按照每页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.条件 ? "是" : "否" }; |
对应SQL：
|
SELECT (CASE WHEN [t2].[value2] = 1 THEN @p1 ELSE @p2 END) AS [value], [t2].[value] AS [数量] FROM ( SELECT COUNT(*) AS [value], [t1].[value] AS [value2] FROM ( SELECT (CASE WHEN [t0].[Freight] > @p0 THEN 1 WHEN NOT ([t0].[Freight] > @p0) THEN 0 ELSE NULL END) AS [value] FROM [dbo].[Orders] AS [t0] ) AS [t1] GROUP BY [t1].[value] ) AS [t2] -- @p0: Input Currency (Size = 0; Prec = 19; Scale = 4) [100] -- @p1: Input String (Size = 1; Prec = 0; Scale = 0) [是] -- @p2: Input String (Size = 1; Prec = 0; Scale = 0) [否] |