linq中文教程(十八)

最新推荐文章于 2025-12-22 14:34:57 发布

转载最新推荐文章于 2025-12-22 14:34:57 发布 · 437 阅读

文章标签：

#linq #string #input #c #sql #null

Linq 专栏收录该内容

21 篇文章

订阅专栏

描述：查询城市是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%]