LoveCherry

select

描述：查询顾客的公司名、地址信息

查询句法：

var 构建匿名类型1 = from c in ctx.Customers                       select new                       {                           公司名 = c.CompanyName,                           地址 = c.Address                       };

对应SQL：

SELECT [t0].[CompanyName], [t0].[Address] FROM [dbo].[Customers] AS [t0]

描述：查询职员的姓名和雇用年份

查询句法：

var 构建匿名类型2 = from emp in ctx.Employees                       select new                       {                           姓名 = emp.LastName + emp.FirstName,                           雇用年 = emp.HireDate.Value.Year                       };

对应SQL：

SELECT [t0].[LastName] + [t0].[FirstName] AS [value], DATEPART(Year, [t0].[HireDate]) AS [value2] FROM [dbo].[Employees] AS [t0]

描述：查询顾客的ID以及联系信息（职位和联系人）

查询句法：

var 构建匿名类型3 = from c in ctx.Customers                       select new                       {                           ID = c.CustomerID,                           联系信息 = new                           {                               职位 = c.ContactTitle,                               联系人 = c.ContactName                           }                       };

对应SQL：

SELECT [t0].[CustomerID], [t0].[ContactTitle], [t0].[ContactName] FROM [dbo].[Customers] AS [t0]

描述：查询订单号和订单是否超重的信息

查询句法：

var select带条件 = from o in ctx.Orders                         select new                         {                             订单号 = o.OrderID,                             是否超重 = o.Freight > 100 ? "是" : "否"                         };

对应SQL：

SELECT [t0].[OrderID],     (CASE         WHEN [t0].[Freight] > @p0 THEN @p1         ELSE @p2      END) AS [value] FROM [dbo].[Orders] AS [t0] -- @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) [否]

where

描述：查询顾客的国家、城市和订单数信息，要求国家是法国并且订单数大于5

查询句法：

var 多条件 = from c in ctx.Customers                   where c.Country == "France" && c.Orders.Count > 5                   select new                   {                       国家 = c.Country,                       城市 = c.City,                       订单数 = c.Orders.Count                   };

对应SQL：

SELECT [t0].[Country], [t0].[City], (     SELECT COUNT(*)     FROM [dbo].[Orders] AS [t2]     WHERE [t2].[CustomerID] = [t0].[CustomerID]     ) AS [value] FROM [dbo].[Customers] AS [t0] WHERE ([t0].[Country] = @p0) AND (((     SELECT COUNT(*)     FROM [dbo].[Orders] AS [t1]     WHERE [t1].[CustomerID] = [t0].[CustomerID]     )) > @p1) -- @p0: Input String (Size = 6; Prec = 0; Scale = 0) [France] -- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]

orderby

描述：查询所有没有下属雇员的雇用年和名，按照雇用年倒序，按照名正序

查询句法：

var 排序 = from emp in ctx.Employees                  where emp.Employees.Count == 0                  orderby emp.HireDate.Value.Year descending, emp.FirstName ascending                  select new                  {                      雇用年 = emp.HireDate.Value.Year,                      名 = emp.FirstName                  };

对应SQL：

SELECT DATEPART(Year, [t0].[HireDate]) AS [value], [t0].[FirstName] FROM [dbo].[Employees] AS [t0] WHERE ((     SELECT COUNT(*)     FROM [dbo].[Employees] AS [t1]     WHERE [t1].[ReportsTo] = [t0].[EmployeeID]     )) = @p0 ORDER BY DATEPART(Year, [t0].[HireDate]) DESC, [t0].[FirstName] -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0]

分页

描述：按照每页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]