一步一步学Linq to sql(8)

查询句法： var 连接并且过滤相同项 = (from c in ctx.Customers where c.City.Contains(\) select c).Union (from c in ctx.Customers where c.ContactName.StartsWith(\) 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(\) select c).Concat (from c in ctx.Customers where c.ContactName.StartsWith(\) 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(\) select c).Intersect (from c in ctx.Customers where c.ContactName.StartsWith(\) 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(\) select c).Except (from c in ctx.Customers where c.ContactName.StartsWith(\) 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.Customer …… 此处隐藏：3970字，全部文档内容请下载后查看。喜欢就下载吧 ……