Linq to sql ：查询句法

最新推荐文章于 2013-09-01 19:03:47 发布

原创最新推荐文章于 2013-09-01 19:03:47 发布 · 654 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#Linq to sql #Linq #Linq查询句法 #Linq分页查询 #Linq查询

Linq 专栏收录该内容

8 篇文章

订阅专栏

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] 
　　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) [否]

distinct

描述：查询顾客覆盖的国家

　　查询句法：

var 过滤相同项 = (from c in ctx.Customers orderby c.Country select c.Country).Distinct();

对应SQL：

SELECT DISTINCT [t0].[Country] 
　　FROM [dbo].[Customers] AS [t0]

union

描述：查询城市是A打头和城市包含A的顾客并按照顾客名字排序

　　查询句法：

var 连接并且过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") select c).Union 
　　(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

对应SQL：

　　SELECT [t3].[CustomerID], [t3].[CompanyName], [t3].[ContactName], [t3].[ContactTitle], [t3].[Address], [t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country], [t3].[Phone], [t3].[Fax] 
　　FROM ( 
　　SELECT [t2].[CustomerID], [t2].[CompanyName], [t2].[ContactName], [t2].[ContactTitle], [t2].[Address], [t2].[City], [t2].[Region], [t2].[PostalCode], [t2].[Country], [t2].[Phone], [t2].[Fax] 
　　FROM ( 
　　SELECT [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] 
　　WHERE [t0].[City] LIKE @p0 
　　UNION 
　　SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax] 
　　FROM [dbo].[Customers] AS [t1] 
　　WHERE [t1].[ContactName] LIKE @p1 
　　) AS [t2] 
　　) AS [t3] 
　　ORDER BY [t3].[ContactName] 
　　-- @p0: Input String (Size = 3; Prec = 0; Scale = 0) [%A%] 
　　-- @p1: Input String (Size = 2; Prec = 0; Scale = 0) [A%]

concat

描述：查询城市是A打头和城市包含A的顾客并按照顾客名字排序，相同的顾客信息不会过滤

　　查询句法：

　var 连接并且不过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") select c).Concat 
　　(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

对应SQL：

SELECT [t3].[CustomerID], [t3].[CompanyName], [t3].[ContactName], [t3].[ContactTitle], [t3].[Address], [t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country], [t3].[Phone], [t3].[Fax] 
　　FROM ( 
　　SELECT [t2].[CustomerID], [t2].[CompanyName], [t2].[ContactName], [t2].[ContactTitle], [t2].[Address], [t2].[City], [t2].[Region], [t2].[PostalCode], [t2].[Country], [t2].[Phone], [t2].[Fax] 
　　FROM ( 
　　SELECT [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] 
　　WHERE [t0].[City] LIKE @p0 
　　UNION ALL 
　　SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax] 
　　FROM [dbo].[Customers] AS [t1] 
　　WHERE [t1].[ContactName] LIKE @p1 
　　) AS [t2] 
　　) AS [t3] 
　　ORDER BY [t3].[ContactName] 
　　-- @p0: Input String (Size = 3; Prec = 0; Scale = 0) [%A%] 
　　-- @p1: Input String (Size = 2; Prec = 0; Scale = 0) [A%]

取相交项

描述：查询城市是A打头的顾客和城市包含A的顾客的交集，并按照顾客名字排序

　　查询句法：

var 取相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Intersect 
　　(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

对应SQL：

　　SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax] 
　　FROM ( 
　　SELECT DISTINCT [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 (EXISTS( 
　　SELECT NULL AS [EMPTY] 
　　FROM [dbo].[Customers] AS [t2] 
　　WHERE ([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName] LIKE @p0) 
　　)) AND ([t1].[City] LIKE @p1) 
　　ORDER BY [t1].[ContactName] 
　　-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [A%] 
　　-- @p1: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]

排除相交项

描述：查询城市包含A的顾客并从中删除城市以A开头的顾客，并按照顾客名字排序

　　查询句法：

var 排除相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Except 
　　(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

对应SQL：

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax] 
　　FROM ( 
　　SELECT DISTINCT [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 (NOT (EXISTS( 
　　SELECT NULL AS [EMPTY] 
　　FROM [dbo].[Customers] AS [t2] 
　　WHERE ([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName] LIKE @p0) 
　　))) AND ([t1].[City] LIKE @p1) 
　　ORDER BY [t1].[ContactName] 
　　-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [A%] 
　　-- @p1: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]

子查询

描述：查询订单数超过5的顾客信息

　　查询句法：

　var 子查询 = from c in ctx.Customers 
　　where 
　　(from o in ctx.Orders group o by o.CustomerID into o where o.Count() > 5 select o.Key).Contains(c.CustomerID) 
　　select c;

对应SQL：

SELECT [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] 
　　WHERE EXISTS( 
　　SELECT NULL AS [EMPTY] 
　　FROM ( 
　　SELECT COUNT(*) AS [value], [t1].[CustomerID] 
　　FROM [dbo].[Orders] AS [t1] 
　　GROUP BY [t1].[CustomerID] 
　　) AS [t2] 
　　WHERE ([t2].[CustomerID] = [t0].[CustomerID]) AND ([t2].[value] > @p0) 
　　) 
　　-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]

in操作

描述：查询指定城市中的客户

　　查询句法：

　var in操作 = from c in ctx.Customers 
　　where new string[] { "Brandenburg", "Cowes", "Stavern" }.Contains(c.City) 
　　select c;

对应SQL：

SELECT [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] 
　　WHERE [t0].[City] IN (@p0, @p1, @p2) 
　　-- @p0: Input String (Size = 11; Prec = 0; Scale = 0) [Brandenburg] 
　　-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [Cowes] 
　　-- @p2: Input String (Size = 7; Prec = 0; Scale = 0) [Stavern]

join

描述：内连接，没有分类的产品查询不到

　　查询句法：

var innerjoin = from p in ctx.Products 
　　join c in ctx.Categories 
　　on p.CategoryID equals c.CategoryID 
　　select p.ProductName;

对应SQL：

　SELECT COUNT(*) AS [value] 
　　FROM [dbo].[Products] AS [t0] 
　　INNER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[CategoryID])

描述：外连接，没有分类的产品也能查询到

　　查询句法：

var leftjoin = from p in ctx.Products 
　　join c in ctx.Categories 
　　on p.CategoryID equals c.CategoryID 
　　into pro 
　　from x in pro.DefaultIfEmpty() 
　　select p.ProductName;

对应SQL：

SELECT COUNT(*) AS [value] 
　　FROM [dbo].[Products] AS [t0] 
　　LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[CategoryID])

你可能会很奇怪，原先很复杂的SQL使用查询句法会很简单(比如按照条件分组)。但是原先觉得很好理解的SQL使用查询句法会觉得很复杂(比如连接查询)。