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