Linq to sql ：查询句法---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]