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