05 第五讲 LINQ to Entities.docx

1、05 第五讲 LINQ to Entities第五讲 LINQ to Entities通过对象服务，可以使用语言集成查询 (LINQ)、Entity SQL 或查询生成器方法对 实体数据模型 (EDM) 执行查询并以对象的形式返回数据。 LINQ to Entities: LINQ to Entities 允许开发人员通过使用 LINQ 表达式和 LINQ 标准查询运算符针对 实体数据模型 (EDM) 对象上下文创建灵活的强类型查询。这样，用户就可以直接从开发环境中以 Transact-SQL 类语法编写强类型的可编写查询。 查询生成器方法：ObjectQuery 实现了一组查询生成器方法，这

2、些方法可用于按顺序构造等效于 Entity SQL 的查询命令。 Entity SQL: Entity SQL 是 ADO.NET 实体框架 提供的 SQL 类语言，用于支持 实体数据模型 (EDM)。EDM 将应用程序数据表示为映射到已定义数据源的一组实体和关系。Entity SQL 支持 EDM 构造，从而使用户可以有效地查询实体模型所表示的数据。下面举例说明三种查询方式：LINQ to Entities:using (AdventureWorksEntities context = new AdventureWorksEntities() / Specify the order amou

3、nt. int orderCost = 2500; try / Define a LINQ query that returns only online orders / more than the specified amount. var onlineOrders = from order in context.SalesOrderHeader where order.OnlineOrderFlag = true & order.TotalDue orderCost select order; / Print order information. foreach (var onlineOr

4、der in onlineOrders) Console.WriteLine(Order ID: 0 Order date: + 1:d Order number: 2, onlineOrder.SalesOrderID, onlineOrder.OrderDate, onlineOrder.SalesOrderNumber); catch (EntitySqlException ex) Console.WriteLine(ex.ToString(); Entity SQL:using (AdventureWorksEntities context = new AdventureWorksEn

5、tities() / Specify the order amount. decimal orderCost = 2500; / Specify the Entity SQL query that returns only online orders / more than the specified amount. string queryString = SELECT VALUE o FROM SalesOrderHeader AS o WHERE o.OnlineOrderFlag = TRUE AND o.TotalDue ordercost; try / Define an Obje

6、ctQuery and pass the maxOrderCost parameter. ObjectQuery onlineOrders = new ObjectQuery(queryString, context); onlineOrders.Parameters.Add( new ObjectParameter(ordercost, orderCost); / Print order information. foreach (var onlineOrder in onlineOrders) Console.WriteLine(Order ID: 0 Order date: + 1:d

7、Order number: 2, onlineOrder.SalesOrderID, onlineOrder.OrderDate, onlineOrder.SalesOrderNumber); catch (EntitySqlException ex) Console.WriteLine(ex.ToString(); 查询生成器方法:using (AdventureWorksEntities context = new AdventureWorksEntities() / Specify the order amount. int orderCost = 2500; try / Define

8、an ObjectQuery that returns only online orders / more than the specified amount. ObjectQuery onlineOrders = context.SalesOrderHeader .Where(it.OnlineOrderFlag = TRUE AND it.TotalDue ordercost, new ObjectParameter(ordercost, orderCost); / Print order information. foreach (var onlineOrder in onlineOrd

9、ers) var items = (from item in context.SalesOrderDetail where item.SalesOrderHeader = onlineOrder select item).Take(3); Console.WriteLine(Order ID: 0 Order date: + 1:d Order number: 2, onlineOrder.SalesOrderID, onlineOrder.OrderDate, onlineOrder.SalesOrderNumber); catch (EntitySqlException ex) Conso

10、le.WriteLine(ex.ToString(); 本讲我们重点介绍lINQ to Entities查询。5.1 投影1.以下示例使用 Select 方法以返回 Product 表中的所有行并显示产品名称。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery products = AWEntities.Product; IQueryable productsQuery = from product in products select product; Console.Writ

11、eLine(Product Names:); foreach (var prod in productsQuery) Console.WriteLine(prod.Name); 2. 以下示例使用 Select 以只返回一系列产品名称。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery products = AWEntities.Product; IQueryable productNames = from p in products select p.Name; Console

12、.WriteLine(Product Names:); foreach (String productName in productNames) Console.WriteLine(productName); 3. 以下示例使用 Select 方法以将 Product.Name 和 Product.ProductID 属性投影到一系列匿名类型。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery products = AWEntities.Product; var query =

13、from product in products select new ProductId = product.ProductID, ProductName = product.Name ; Console.WriteLine(Product Info:); foreach (var productInfo in query) Console.WriteLine(Product Id: 0 Product name: 1 , productInfo.ProductId, productInfo.ProductName); 4. 以下示例使用 From From （与 SelectMany 方法

14、等效）以选择 TotalDue 低于 500.00 的所有订单。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery contacts = AWEntities.Contact; ObjectQuery orders = AWEntities.SalesOrderHeader;/注意三者是等效的/利用筛选 var query = from contact in contacts from order in orders where contact.ContactID = order

15、.Contact.ContactID & order.TotalDue 200000.00M select new ContactID = contact.ContactID, LastName = contact.LastName, FirstName = contact.FirstName, OrderID = order.SalesOrderID, Total = order.TotalDue ; /使用关联var query2 = from contact in contacts join or in orders on contact.ContactID equals or.Cont

16、act.ContactID where or.TotalDue 200000.00M select new ContactID = contact.ContactID, LastName = contact.LastName, FirstName = contact.FirstName, OrderID = or.SalesOrderID, Total = or.TotalDue ; foreach (var smallOrder in query) Console.WriteLine(Contact ID: 0 Name: 1, 2 Order ID: 3 Total Due: $4 , s

17、mallOrder.ContactID, smallOrder.LastName, smallOrder.FirstName, smallOrder.OrderID, smallOrder.Total); 5. 以下示例使用 From From （与 SelectMany 方法等效）以选择订单总计高于 10000.00 的所有订单并使用 From 赋值以避免两次请求总计。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery contacts = AWEntities.Contact

18、; ObjectQuery orders = AWEntities.SalesOrderHeader; var query = from contact in contacts from order in orders let total = order.TotalDue where contact.ContactID = order.Contact.ContactID & total = 10000.0M select new ContactID = contact.ContactID, LastName = contact.LastName, OrderID = order.SalesOr

19、derID, total ; foreach (var order in query) Console.WriteLine(Contact ID: 0 Last name: 1 Order ID: 2 Total: 3, order.ContactID, order.LastName, order.OrderID, order.total); 5.2 限制1. 以下示例返回订单数量大于 2 且小于 6 的订单。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery orders =

20、AWEntities.SalesOrderDetail; var query = from order in orders where order.OrderQty 2 & order.OrderQty 6 select new SalesOrderID = order.SalesOrderID, OrderQty = order.OrderQty ; foreach (var order in query) Console.WriteLine(Order ID: 0 Order quantity: 1, order.SalesOrderID, order.OrderQty); 2. 以下示例

21、使用 Where 方法以查找在 2003 年 12 月 1 日之后生成的订单，然后使用 order.SalesOrderDetail 导航属性以获取每个订单的详细信息。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery orders = AWEntities.SalesOrderHeader; IQueryable query = from order in orders where order.OrderDate = new DateTime(2003, 12, 1) sele

22、ct order; Console.WriteLine(Orders that were made after December 1, 2003:); foreach (SalesOrderHeader order in query) Console.WriteLine(OrderID 0 Order date: 1:d , order.SalesOrderID, order.OrderDate); foreach (SalesOrderDetail orderDetail in order.SalesOrderDetail) Console.WriteLine( Product ID: 0

23、Unit Price 1, orderDetail.ProductID, orderDetail.UnitPrice); 5.3 排序1. 以下示例使用 OrderBy 以返回按姓氏排序的联系人列表。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery contacts = AWEntities.Contact; IQueryable sortedNames = from n in contacts orderby n.LastName select n; Console.Writ

24、eLine(The sorted list of last names:); foreach (Contact n in sortedNames) Console.WriteLine(n.LastName); 2. 以下示例使用 orderby descending，以按照从高到低的顺序对价目表排序。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery products = AWEntities.Product; IQueryable sortedPrices = from p i

25、n products orderby p.ListPrice descending select p.ListPrice; Console.WriteLine(The list price from highest to lowest:); foreach (Decimal price in sortedPrices) Console.WriteLine(price); 3. 以下示例使用 OrderBy 和 ThenBy 以返回先按姓氏后按名字排序的联系人列表。using (AdventureWorksEntities AWEntities = new AdventureWorksEntit

26、ies() ObjectQuery contacts = AWEntities.Contact; IQueryable sortedContacts = from contact in contacts orderby contact.LastName, contact.FirstName select contact; Console.WriteLine(The list of contacts sorted by last name then by first name:); foreach (Contact sortedContact in sortedContacts) Console

27、.WriteLine(sortedContact.LastName + , + sortedContact.FirstName); 5.4 聚合运算符1. 以下示例使用 Average 方法以查找每种样式的产品的平均标价。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery products = AWEntities.Product; var query = from product in products group product by product.Style into g

28、 select new Style = g.Key, AverageListPrice = g.Average(product = product.ListPrice) ; foreach (var product in query) Console.WriteLine(Product style: 0 Average list price: 1, product.Style, product.AverageListPrice); 2以下示例使用 Average 以获取每个联系人 ID 的平均应付款总计。using (AdventureWorksEntities AWEntities = new AdventureWorksEntities() ObjectQuery orders = AWEntities.SalesOr