linq中文教程(十五)

最新推荐文章于 2017-09-01 11:47:16 发布

mane_yao

最新推荐文章于 2017-09-01 11:47:16 发布

阅读量416

点赞数

分类专栏： Linq 文章标签： linq input sql string c

Linq 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

描述：查询顾客的国家、城市和订单数信息，要求国家是法国并且订单数大于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]