[索引页]
[源码下载]
[源码下载]
再接再厉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);
}
{
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]
[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);
}
{
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]
[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);
}
{
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]
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);
}
{
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]
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);
}
{
decimal? average = ctx.Products.Average(p => p.UnitPrice);
}
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
[GroupBy1].[A1] AS [C1]
FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]