再接再厉VS 2008 sp1 + .NET 3.5 sp1(2) - Entity Framework(实体框架)之详解 Linq To Entities 之一
作者：webabcd
介绍
以Northwind为示例数据库，ADO.NET Entity Framework之Linq To Entities
- First - 返回集合中的第一个成员；不延迟
- FirstOrDefault - 返回集合中的第一个成员（找不到则返回null）；不延迟
- All - 是否集合中所有成员都满足某一条件；不延迟
- Any - 集合中是否有成员满足某一条件；不延迟
- Average - 取平均值；不延迟
- Sum - 求和；不延迟
- Max - 取最大值；不延迟
- Min - 取最小值；不延迟
- Count - 取指定集合的成员数，返回值类型int；不延迟
- LongCount - 取指定集合的成员数，返回值类型long；不延迟
- Take - 获取集合的前 n 个成员；延迟
- Skip - 跳过集合的前 n 个成员；延迟（Linq To Entities 需要先排序才能 Skip）
- Distinct - 过滤集合中的相同项；延迟
- Union - 连接不同集合，自动过滤相同项；延迟
- UnionAll - 连接不同集合，不会自动过滤相同项；延迟
- Concat - 连接不同集合，不会自动过滤相同项；延迟
- Intersect - 获取不同集合的相同项（交集）；延迟
- Except - 从某集合中删除其与另一个集合中相同的项；延迟
示例
First
using (var ctx = new NorthwindEntities())
{
Products first = ctx.Products.First(p => p.ProductID > 3);
}SELECT [Limit1].[C1] AS [C1], [Limit1].[Discontinued] AS [Discontinued], [Limit1].[ProductID] AS [ProductID], [Limit1].[ProductName] AS [ProductName], [Limit1].[QuantityPerUnit] AS [QuantityPerUnit], [Limit1].[ReorderLevel] AS [ReorderLevel], [Limit1].[UnitPrice] AS [UnitPrice], [Limit1].[UnitsInStock] AS [UnitsInStock], [Limit1].[UnitsOnOrder] AS [UnitsOnOrder], [Limit1].[CategoryID] AS [CategoryID], [Limit1].[SupplierID] AS [SupplierID]FROM ( SELECT TOP (1) [Extent1].[CategoryID] AS [CategoryID], [Extent1].[Discontinued] AS [Discontinued], [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[SupplierID] AS [SupplierID], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 1 AS [C1] FROM [dbo].[Products] AS [Extent1] WHERE [Extent1].[ProductID] > 3) AS [Limit1]
FirstOrDefault
using (var ctx = new NorthwindEntities()){ Products firstOrDefault = ctx.Products.FirstOrDefault(p => p.ProductID > 100);}SELECT [Limit1].[C1] AS [C1], [Limit1].[Discontinued] AS [Discontinued], [Limit1].[ProductID] AS [ProductID], [Limit1].[ProductName] AS [ProductName], [Limit1].[QuantityPerUnit] AS [QuantityPerUnit], [Limit1].[ReorderLevel] AS [ReorderLevel], [Limit1].[UnitPrice] AS [UnitPrice], [Limit1].[UnitsInStock] AS [UnitsInStock], [Limit1].[UnitsOnOrder] AS [UnitsOnOrder], [Limit1].[CategoryID] AS [CategoryID], [Limit1].[SupplierID] AS [SupplierID]FROM ( SELECT TOP (1) [Extent1].[CategoryID] AS [CategoryID], [Extent1].[Discontinued] AS [Discontinued], [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[SupplierID] AS [SupplierID], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 1 AS [C1] FROM [dbo].[Products] AS [Extent1] WHERE [Extent1].[ProductID] > 100) AS [Limit1]
All
using (var ctx = new NorthwindEntities()){ bool all = ctx.Products.All(p => p.ProductID > 3);}SELECT CASE WHEN ( NOT EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[Products] AS [Extent1] WHERE ( NOT ([Extent1].[ProductID] > 3)) OR (CASE WHEN ([Extent1].[ProductID] > 3) THEN cast(1 as bit) WHEN ( NOT ([Extent1].[ProductID] > 3)) THEN cast(0 as bit) END IS NULL))) THEN cast(1 as bit) WHEN ( EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[Products] AS [Extent2] WHERE ( NOT ([Extent2].[ProductID] > 3)) OR (CASE WHEN ([Extent2].[ProductID] > 3) THEN cast(1 as bit) WHEN ( NOT ([Extent2].[ProductID] > 3)) THEN cast(0 as bit) END IS NULL))) THEN cast(0 as bit) END AS [C1]FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
Any
using (var ctx = new NorthwindEntities()){ bool any = ctx.Products.Any(p => p.ProductID > 3);}SELECT CASE WHEN ( EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[Products] AS [Extent1] WHERE [Extent1].[ProductID] > 3)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT cast(1 as bit) AS [C1] FROM [dbo].[Products] AS [Extent2] WHERE [Extent2].[ProductID] > 3)) THEN cast(0 as bit) END AS [C1]FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
Average
using (var ctx = new NorthwindEntities()){ decimal? average = ctx.Products.Average(p => p.UnitPrice);}SELECT [GroupBy1].[A1] AS [C1]FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]LEFT OUTER JOIN (SELECT AVG([Extent1].[UnitPrice]) AS [A1] FROM [dbo].[Products] AS [Extent1] ) AS [GroupBy1] ON 1 = 1
Sum
using (var ctx = new NorthwindEntities()){ decimal? sum = ctx.Products.Sum(p => p.UnitPrice);}SELECT [GroupBy1].[A1] AS [C1]FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]LEFT OUTER JOIN (SELECT SUM([Extent1].[UnitPrice]) AS [A1] FROM [dbo].[Products] AS [Extent1] ) AS [GroupBy1] ON 1 = 1
Max
using (var ctx = new NorthwindEntities()){ decimal? max = ctx.Products.Max(p => p.UnitPrice);}SELECT [GroupBy1].[A1] AS [C1]FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]LEFT OUTER JOIN (SELECT MAX([Extent1].[UnitPrice]) AS [A1] FROM [dbo].[Products] AS [Extent1] ) AS [GroupBy1] ON 1 = 1
Min
using (var ctx = new NorthwindEntities()){ decimal? min = ctx.Products.Min(p => p.UnitPrice);}SELECT [GroupBy1].[A1] AS [C1]FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]LEFT OUTER JOIN (SELECT MIN([Extent1].[UnitPrice]) AS [A1] FROM [dbo].[Products] AS [Extent1] ) AS [GroupBy1] ON 1 = 1
Count
using (var ctx = new NorthwindEntities()){ int count = ctx.Products.Count(p => p.ProductID > 3);}SELECT [GroupBy1].[A1] AS [C1]FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]LEFT OUTER JOIN (SELECT COUNT(cast(1 as bit)) AS [A1] FROM [dbo].[Products] AS [Extent1] WHERE [Extent1].[ProductID] > 3 ) AS [GroupBy1] ON 1 = 1
LongCount
using (var ctx = new NorthwindEntities()){ long longCount = ctx.Products.LongCount(p => p.ProductID > 3);}SELECT [GroupBy1].[A1] AS [C1]FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]LEFT OUTER JOIN (SELECT COUNT_BIG(cast(1 as bit)) AS [A1] FROM [dbo].[Products] AS [Extent1] WHERE [Extent1].[ProductID] > 3 ) AS [GroupBy1] ON 1 = 1
Take
using (var ctx = new NorthwindEntities()){ IQueryable<Products> take = ctx.Products.Take(3); take.ToList();}SELECT [Limit1].[C1] AS [C1], [Limit1].[Discontinued] AS [Discontinued], [Limit1].[ProductID] AS [ProductID], [Limit1].[ProductName] AS [ProductName], [Limit1].[QuantityPerUnit] AS [QuantityPerUnit], [Limit1].[ReorderLevel] AS [ReorderLevel], [Limit1].[UnitPrice] AS [UnitPrice], [Limit1].[UnitsInStock] AS [UnitsInStock], [Limit1].[UnitsOnOrder] AS [UnitsOnOrder], [Limit1].[CategoryID] AS [CategoryID], [Limit1].[SupplierID] AS [SupplierID]FROM ( SELECT TOP (3) [Extent1].[CategoryID] AS [CategoryID], [Extent1].[Discontinued] AS [Discontinued], [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[SupplierID] AS [SupplierID], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 1 AS [C1] FROM [dbo].[Products] AS [Extent1]) AS [Limit1]
Skip
using (var ctx = new NorthwindEntities()){ IQueryable<Products> skip = ctx.Products.OrderBy(p => p.UnitPrice).Skip(3); skip.ToList();}SELECT [Project1].[C1] AS [C1], [Project1].[Discontinued] AS [Discontinued], [Project1].[ProductID] AS [ProductID], [Project1].[ProductName] AS [ProductName], [Project1].[QuantityPerUnit] AS [QuantityPerUnit], [Project1].[ReorderLevel] AS [ReorderLevel], [Project1].[UnitPrice] AS [UnitPrice], [Project1].[UnitsInStock] AS [UnitsInStock], [Project1].[UnitsOnOrder] AS [UnitsOnOrder], [Project1].[CategoryID] AS [CategoryID], [Project1].[SupplierID] AS [SupplierID]FROM ( SELECT [Project1].[CategoryID] AS [CategoryID], [Project1].[Discontinued] AS [Discontinued], [Project1].[ProductID] AS [ProductID], [Project1].[ProductName] AS [ProductName], [Project1].[QuantityPerUnit] AS [QuantityPerUnit], [Project1].[ReorderLevel] AS [ReorderLevel], [Project1].[SupplierID] AS [SupplierID], [Project1].[UnitPrice] AS [UnitPrice], [Project1].[UnitsInStock] AS [UnitsInStock], [Project1].[UnitsOnOrder] AS [UnitsOnOrder], [Project1].[C1] AS [C1], row_number() OVER (ORDER BY [Project1].[UnitPrice] ASC) AS [row_number] FROM ( SELECT [Extent1].[CategoryID] AS [CategoryID], [Extent1].[Discontinued] AS [Discontinued], [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[SupplierID] AS [SupplierID], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], 1 AS [C1] FROM [dbo].[Products] AS [Extent1] ) AS [Project1]) AS [Project1]WHERE [Project1].[row_number] > 3ORDER BY [Project1].[UnitPrice] ASC
Distinct
using (var ctx = new NorthwindEntities()){ ObjectQuery<Products> distinct = ctx.Products.Distinct(); distinct.ToList();}SELECT 1 AS [C1], [Extent1].[Discontinued] AS [Discontinued], [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], [Extent1].[CategoryID] AS [CategoryID], [Extent1].[SupplierID] AS [SupplierID]FROM [dbo].[Products] AS [Extent1]
Union
using (var ctx = new NorthwindEntities()){ ObjectQuery<Products> union = ctx.Products.Union(ctx.Products); union.ToList();}SELECT 1 AS [C1], [Distinct1].[C1] AS [C2], [Distinct1].[C2] AS [C3], [Distinct1].[C3] AS [C4], [Distinct1].[C4] AS [C5], [Distinct1].[C5] AS [C6], [Distinct1].[C6] AS [C7], [Distinct1].[C7] AS [C8], [Distinct1].[C8] AS [C9], [Distinct1].[C9] AS [C10], [Distinct1].[C10] AS [C11]FROM ( SELECT DISTINCT [UnionAll1].[Discontinued] AS [C1], [UnionAll1].[ProductID] AS [C2], [UnionAll1].[ProductName] AS [C3], [UnionAll1].[QuantityPerUnit] AS [C4], [UnionAll1].[ReorderLevel] AS [C5], [UnionAll1].[UnitPrice] AS [C6], [UnionAll1].[UnitsInStock] AS [C7], [UnionAll1].[UnitsOnOrder] AS [C8], [UnionAll1].[CategoryID] AS [C9], [UnionAll1].[SupplierID] AS [C10] FROM (SELECT [Extent1].[Discontinued] AS [Discontinued], [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], [Extent1].[CategoryID] AS [CategoryID], [Extent1].[SupplierID] AS [SupplierID] FROM [dbo].[Products] AS [Extent1] UNION ALL SELECT [Extent2].[Discontinued] AS [Discontinued], [Extent2].[ProductID] AS [ProductID], [Extent2].[ProductName] AS [ProductName], [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], [Extent2].[ReorderLevel] AS [ReorderLevel], [Extent2].[UnitPrice] AS [UnitPrice], [Extent2].[UnitsInStock] AS [UnitsInStock], [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], [Extent2].[CategoryID] AS [CategoryID], [Extent2].[SupplierID] AS [SupplierID] FROM [dbo].[Products] AS [Extent2]) AS [UnionAll1]) AS [Distinct1]
UnionAll
using (var ctx = new NorthwindEntities()){ ObjectQuery<Products> unionAll = ctx.Products.UnionAll(ctx.Products); unionAll.ToList();}SELECT 1 AS [C1], [UnionAll1].[Discontinued] AS [C2], [UnionAll1].[ProductID] AS [C3], [UnionAll1].[ProductName] AS [C4], [UnionAll1].[QuantityPerUnit] AS [C5], [UnionAll1].[ReorderLevel] AS [C6], [UnionAll1].[UnitPrice] AS [C7], [UnionAll1].[UnitsInStock] AS [C8], [UnionAll1].[UnitsOnOrder] AS [C9], [UnionAll1].[CategoryID] AS [C10], [UnionAll1].[SupplierID] AS [C11]FROM (SELECT [Extent1].[Discontinued] AS [Discontinued], [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], [Extent1].[CategoryID] AS [CategoryID], [Extent1].[SupplierID] AS [SupplierID] FROM [dbo].[Products] AS [Extent1]UNION ALL SELECT [Extent2].[Discontinued] AS [Discontinued], [Extent2].[ProductID] AS [ProductID], [Extent2].[ProductName] AS [ProductName], [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], [Extent2].[ReorderLevel] AS [ReorderLevel], [Extent2].[UnitPrice] AS [UnitPrice], [Extent2].[UnitsInStock] AS [UnitsInStock], [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], [Extent2].[CategoryID] AS [CategoryID], [Extent2].[SupplierID] AS [SupplierID] FROM [dbo].[Products] AS [Extent2]) AS [UnionAll1]
Concat
using (var ctx = new NorthwindEntities()){ IQueryable<Products> concat = ctx.Products.Concat(ctx.Products); concat.ToList();}SELECT 1 AS [C1], [UnionAll1].[Discontinued] AS [C2], [UnionAll1].[ProductID] AS [C3], [UnionAll1].[ProductName] AS [C4], [UnionAll1].[QuantityPerUnit] AS [C5], [UnionAll1].[ReorderLevel] AS [C6], [UnionAll1].[UnitPrice] AS [C7], [UnionAll1].[UnitsInStock] AS [C8], [UnionAll1].[UnitsOnOrder] AS [C9], [UnionAll1].[CategoryID] AS [C10], [UnionAll1].[SupplierID] AS [C11]FROM (SELECT [Extent1].[Discontinued] AS [Discontinued], [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], [Extent1].[CategoryID] AS [CategoryID], [Extent1].[SupplierID] AS [SupplierID] FROM [dbo].[Products] AS [Extent1]UNION ALL SELECT [Extent2].[Discontinued] AS [Discontinued], [Extent2].[ProductID] AS [ProductID], [Extent2].[ProductName] AS [ProductName], [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], [Extent2].[ReorderLevel] AS [ReorderLevel], [Extent2].[UnitPrice] AS [UnitPrice], [Extent2].[UnitsInStock] AS [UnitsInStock], [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], [Extent2].[CategoryID] AS [CategoryID], [Extent2].[SupplierID] AS [SupplierID] FROM [dbo].[Products] AS [Extent2]) AS [UnionAll1]
Intersect
using (var ctx = new NorthwindEntities()){ ObjectQuery<Products> intersect = ctx.Products.Intersect(ctx.Products); intersect.ToList();}SELECT 1 AS [C1], [Intersect1].[Discontinued] AS [C2], [Intersect1].[ProductID] AS [C3], [Intersect1].[ProductName] AS [C4], [Intersect1].[QuantityPerUnit] AS [C5], [Intersect1].[ReorderLevel] AS [C6], [Intersect1].[UnitPrice] AS [C7], [Intersect1].[UnitsInStock] AS [C8], [Intersect1].[UnitsOnOrder] AS [C9], [Intersect1].[CategoryID] AS [C10], [Intersect1].[SupplierID] AS [C11]FROM (SELECT [Extent1].[Discontinued] AS [Discontinued], [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], [Extent1].[CategoryID] AS [CategoryID], [Extent1].[SupplierID] AS [SupplierID] FROM [dbo].[Products] AS [Extent1]INTERSECT SELECT [Extent2].[Discontinued] AS [Discontinued], [Extent2].[ProductID] AS [ProductID], [Extent2].[ProductName] AS [ProductName], [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], [Extent2].[ReorderLevel] AS [ReorderLevel], [Extent2].[UnitPrice] AS [UnitPrice], [Extent2].[UnitsInStock] AS [UnitsInStock], [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], [Extent2].[CategoryID] AS [CategoryID], [Extent2].[SupplierID] AS [SupplierID] FROM [dbo].[Products] AS [Extent2]) AS [Intersect1]
Except
using (var ctx = new NorthwindEntities()){ ObjectQuery<Products> except = ctx.Products.Except(ctx.Products); except.ToList();}SELECT 1 AS [C1], [Except1].[Discontinued] AS [C2], [Except1].[ProductID] AS [C3], [Except1].[ProductName] AS [C4], [Except1].[QuantityPerUnit] AS [C5], [Except1].[ReorderLevel] AS [C6], [Except1].[UnitPrice] AS [C7], [Except1].[UnitsInStock] AS [C8], [Except1].[UnitsOnOrder] AS [C9], [Except1].[CategoryID] AS [C10], [Except1].[SupplierID] AS [C11]FROM (SELECT [Extent1].[Discontinued] AS [Discontinued], [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], [Extent1].[QuantityPerUnit] AS [QuantityPerUnit], [Extent1].[ReorderLevel] AS [ReorderLevel], [Extent1].[UnitPrice] AS [UnitPrice], [Extent1].[UnitsInStock] AS [UnitsInStock], [Extent1].[UnitsOnOrder] AS [UnitsOnOrder], [Extent1].[CategoryID] AS [CategoryID], [Extent1].[SupplierID] AS [SupplierID] FROM [dbo].[Products] AS [Extent1]EXCEPT SELECT [Extent2].[Discontinued] AS [Discontinued], [Extent2].[ProductID] AS [ProductID], [Extent2].[ProductName] AS [ProductName], [Extent2].[QuantityPerUnit] AS [QuantityPerUnit], [Extent2].[ReorderLevel] AS [ReorderLevel], [Extent2].[UnitPrice] AS [UnitPrice], [Extent2].[UnitsInStock] AS [UnitsInStock], [Extent2].[UnitsOnOrder] AS [UnitsOnOrder], [Extent2].[CategoryID] AS [CategoryID], [Extent2].[SupplierID] AS [SupplierID] FROM [dbo].[Products] AS [Extent2]) AS [Except1]
OK
[源码下载]
