Entity Framework 学习总结之九：LinqToEntities
介绍
LINQ to Entities 使开发人员能够通过使用 LINQ 表达式和 LINQ 标准查询运算符，直接从开发环境中针对实体框架对象上下文创建灵活的强类型查询。LINQ to Entities 查询使用对象服务基础结构。ObjectContext 类是作为 CLR 对象与 实体数据模型 进行交互的主要类。开发人员通过 ObjectContext 构造泛型 ObjectQuery 实例。ObjectQuery 泛型类表示一个查询，此查询返回一个由类型化实体组成的实例或集合。返回的实体对象可供更新并位于对象上下文中。以下是创建和执行 LINQ to Entities 查询的过程：
1) 从 ObjectContext 构造 ObjectQuery 实例。
2) 通过使用 ObjectQuery 实例在 C# 或 Visual Basic 中编写 LINQ to Entities 查询。
3) 将 LINQ 标准查询运算符和表达式将转换为命令目录树。
4) 对数据源执行命令目录树表示形式的查询。执行过程中在数据源上引发的任何异常都将直接向上传递到客户端。
5) 将查询结果返回到客户端。
查询使用
相关的查询语法可以使用基于表达式或基于方法的语法。
1、投影
代码片断：
using (var edm = new NorthwindEntities())
{
ObjectQuery<Orders> orders = edm.Orders;
IQueryable<Orders> result = from order in orders
select order;
foreach (Orders o in result)
{
Console.WriteLine("{0},{1}", o.OrderID, o.OrderDate);
}
Console.WriteLine(orders.ToTraceString());
}
2、条件限制
代码片断：
DateTime orderDate =new DateTime(1996,7,9);
//第一种方法在表达式中进行Where
//ObjectQuery<Orders> orders = edm.Orders;
//IQueryable<Orders> result = from order in orders
//where order.OrderDate <= orderDate
//select order ;
//可以直接在edm.Orders对象上使用Where扩展方法
var orders = edm.Orders.Where(order => order.OrderDate <= orderDate);
IQueryable<Orders> result = from order in orders
select order;
foreach (Orders o in result)
{
Console.WriteLine("{0},{1}", o.OrderID, o.OrderDate);
}
//Console.WriteLine(orders.ToTraceString());
//对比生成的SQL语句，此种在本质上筛选，而前种方法是在内存中筛选。
Console.WriteLine(((ObjectQuery)orders).ToTraceString());
结果：
3、排序和分页
在使用Skip和Take方法实现分页时，必须先对数据进行排序，否则将会抛异常。
代码片断：
DateTime orderDate = new DateTime(1996, 7, 9);
//第一种方法在表达式中进行分页
ObjectQuery<Orders> orders = edm.Orders;
IQueryable<Orders> result = (from order in orders
where order.OrderDate > orderDate
orderby order.OrderID
select order).Skip(0).Take(2);
//可以直接在edm.Orders对象上使用Where、OrderBy、Skip、Take扩展方法。
//var orders = edm.Orders.Where(order => order.OrderDate <= orderDate).OrderBy(order => order.OrderID).Skip(0).Take(2);
//可以直接foreach (Orders o in orders)
foreach (Orders o in result)
{
Console.WriteLine("{0},{1}", o.OrderID, o.OrderDate);
}
Console.WriteLine(orders.ToTraceString());
//对比生成的SQL语句，此种在本质上筛选，而前种方法是在内存中筛选。
//Console.WriteLine(((ObjectQuery)orders).ToTraceString());
第一种方法输出结果：
10253,1996/7/10 0:00:00
10254,1996/7/11 0:00:00
SELECT
[Extent1].[OrderID] AS [OrderID],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[EmployeeID] AS [EmployeeID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[RequiredDate] AS [RequiredDate],
[Extent1].[ShippedDate] AS [ShippedDate],
[Extent1].[ShipVia] AS [ShipVia],
[Extent1].[Freight] AS [Freight],
[Extent1].[ShipName] AS [ShipName],
[Extent1].[ShipAddress] AS [ShipAddress],
[Extent1].[ShipCity] AS [ShipCity],
[Extent1].[ShipRegion] AS [ShipRegion],
[Extent1].[ShipPostalCode] AS [ShipPostalCode],
[Extent1].[ShipCountry] AS [ShipCountry]
FROM [dbo].[Orders] AS [Extent1]
第二种方法输出结果：
10248,1996/7/4 0:00:00
10249,1996/7/5 0:00:00
SELECT TOP (2)
[Project1].[OrderID] AS [OrderID],
[Project1].[CustomerID] AS [CustomerID],
[Project1].[EmployeeID] AS [EmployeeID],
[Project1].[OrderDate] AS [OrderDate],
[Project1].[RequiredDate] AS [RequiredDate],
[Project1].[ShippedDate] AS [ShippedDate],
[Project1].[ShipVia] AS [ShipVia],
[Project1].[Freight] AS [Freight],
[Project1].[ShipName] AS [ShipName],
[Project1].[ShipAddress] AS [ShipAddress],
[Project1].[ShipCity] AS [ShipCity],
[Project1].[ShipRegion] AS [ShipRegion],
[Project1].[ShipPostalCode] AS [ShipPostalCode],
[Project1].[ShipCountry] AS [ShipCountry]
FROM ( SELECT [Project1].[OrderID] AS [OrderID], [Project1].[CustomerID] AS [Cus
tomerID], [Project1].[EmployeeID] AS [EmployeeID], [Project1].[OrderDate] AS [Or
derDate], [Project1].[RequiredDate] AS [RequiredDate], [Project1].[ShippedDate]
AS [ShippedDate], [Project1].[ShipVia] AS [ShipVia], [Project1].[Freight] AS [Fr
eight], [Project1].[ShipName] AS [ShipName], [Project1].[ShipAddress] AS [ShipAd
dress], [Project1].[ShipCity] AS [ShipCity], [Project1].[ShipRegion] AS [ShipReg
ion], [Project1].[ShipPostalCode] AS [ShipPostalCode], [Project1].[ShipCountry]
AS [ShipCountry], row_number() OVER (ORDER BY [Project1].[OrderID] ASC) AS [row_
number]
FROM ( SELECT
[Extent1].[OrderID] AS [OrderID],
[Extent1].[CustomerID] AS [CustomerID],
[Extent1].[EmployeeID] AS [EmployeeID],
[Extent1].[OrderDate] AS [OrderDate],
[Extent1].[RequiredDate] AS [RequiredDate],
[Extent1].[ShippedDate] AS [ShippedDate],
[Extent1].[ShipVia] AS [ShipVia],
[Extent1].[Freight] AS [Freight],
[Extent1].[ShipName] AS [ShipName],
[Extent1].[ShipAddress] AS [ShipAddress],
[Extent1].[ShipCity] AS [ShipCity],
[Extent1].[ShipRegion] AS [ShipRegion],
[Extent1].[ShipPostalCode] AS [ShipPostalCode],
[Extent1].[ShipCountry] AS [ShipCountry]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[OrderDate] <= @p__linq__0
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[OrderID] ASC
4、聚合
可使用的聚合运算符有Average、Count、Max、Min 和 Sum。
代码片断：
var maxuprice = edm.Products.Max(p => p.UnitPrice);
Console.WriteLine(maxuprice.Value);
5、连接
可以的连接有Join 和 GroupJoin 方法。GroupJoin组联接等效于左外部联接，它返回第一个（左侧）数据源的每个元素（即使其他数据源中没有关联元素）。
代码片断：
var query = from d in edm.Order_Details
join order in edm.Orders
on d.OrderID equals order.OrderID
select new
{
OrderId = order.OrderID,
ProductId = d.ProductID,
UnitPrice = d.UnitPrice
};
foreach (var q in query)
{
Console.WriteLine("{0},{1},{2}", q.OrderId, q.ProductId, q.UnitPrice);
}
详细的使用方法可以参考Linq to SQL。
有关LINQ可以了解：http://msdn.microsoft.com/zh-cn/magazine/cc337893.aspx
本文严重参考：http://www.cnblogs.com/xray2005/archive/2009/05/09/1453036.html
