一步一步学Linq to sql（四）：查询句法

转自：http://kb.cnblogs.com/page/42688/

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
                      };

注：emp.HireDate.Value.Year应为emp.HireDate.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])

系列文章

转自：http://kb.cnblogs.com/page/42688/

本系列文章导航

一步一步学Linq to sql（一）：预备知识

一步一步学Linq to sql（二）：DataContext与实体

一步一步学Linq to sql（三）：增删改

一步一步学Linq to sql（四）：查询句法

一步一步学Linq to sql（五）：存储过程

一步一步学Linq to sql（六）：探究特性

一步一步学Linq to sql（七）：并发与事务

一步一步学Linq to sql（八）：继承与关系

一步一步学Linq to sql（九）：其它补充

一步一步学Linq to sql（十）：分层构架的例子