Linq to sql（四）：查询句法(四)

 

对应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%]