學Linq to sql（四）：查詢句法

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使用查詢句法會覺得很復雜（比如連接查詢）。其實，我們還可以通過其它方式進行連接操作，在以後說DataLoadOptions類型的時候會再說。雖然Linq to sql已經非常智能了，但是對於非常復雜的查詢還是建議通過存儲過程實現，下次講解如何調用存儲過程。