LINQ_to_EF语法及实例大全.pdf-资料库

基于方法的查询语法 2011 年 1 月 3 日 | 标签: C#, Entity Framework, LINQ to Entities 实体框架(Entity Framework )是 ADO.NET 中的一套支持开发面向数据的软件应用程序的技术。 LINQ to Entities 提供语言集成查询 (LINQ) 支持，它允许开发人员使用 Visual Basic 或 Visual C# 根据实体框架概念模型编写查询。针对实体框架的查询由针对对象上下文执行的命令目录树查询表示。 LINQ to Entities 将语言集成查询 (LINQ) 查询转换为命令目录树查询，针对实体框架执行这些查询，并返回可同时由实体框架和 LINQ 使用的对象。下面列出一些基于方法的查询语法示例(C#)：投影 Select | SelectMany 筛选 Where | Where…Contains 排序 ThenBy | ThenByDescending 聚合运算符 Average | Count | LongCount | Max | Min | Sum 分区 Skip | Take 转换 ToArray | ToDictionary | ToList 联接运算符 GroupJoin | Join 元素运算符 First 分组 GroupBy 导航关系 Select 以下示例使用 Select 方法以将 Product.Name 和 Product.ProductID 属性投影到一系列匿名类型。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 以下示例使用 Select 方法以只返回一系列产品名称。 1 2 3 4 5 6 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { var query = AWEntities.Products .Select(product => 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); } } using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { IQueryable productNames = AWEntities.Products .Select(p => p.Name); Console.WriteLine("Product Names:");

foreach (String productName in productNames) { Console.WriteLine(productName); } } 7 8 9 10 11 SelectMany 以下示例使用 SelectMany 方法以选择 TotalDue 低于 500.00 的所有订单。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet contacts = AWEntities.Contacts; ObjectSet orders = AWEntities.SalesOrderHeaders; var query = contacts.SelectMany( contact => orders.Where(order => (contact.ContactID == order.Contact.ContactID) && order.TotalDue < 500.00M) .Select(order => new { ContactID = contact.ContactID, LastName = contact.LastName, FirstName = contact.FirstName, OrderID = order.SalesOrderID, Total = order.TotalDue })); foreach (var smallOrder in query) { Console.WriteLine("Contact ID:{0} Name:{1},{2} Order ID:{3} Total Due: ${4} ", smallOrder.ContactID, smallOrder.LastName, smallOrder.FirstName, smallOrder.OrderID, smallOrder.Total); } } 以下示例使用 SelectMany 方法以选择在 2002 年 10 月 1 或此日期之后发出的所有订单。 1 2 3 4 5 6 7 8 9 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet contacts = AWEntities.Contacts; ObjectSet orders = AWEntities.SalesOrderHeaders; var query = contacts.SelectMany( contact => orders.Where(order => (contact.ContactID == order.Contact.ContactID)

10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 && order.OrderDate >= new DateTime(2002, 10, 1)) .Select(order => new { ContactID = contact.ContactID, LastName = contact.LastName, FirstName = contact.FirstName, OrderID = order.SalesOrderID, OrderDate = order.OrderDate })); foreach (var order in query) { Console.WriteLine("Contact ID:{0} Name:{1},{2} Order ID:{3} Order date: {4:d} ", order.ContactID, order.LastName, order.FirstName, order.OrderID, order.OrderDate); } } Where 以下示例返回所有联机订单。 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { var onlineOrders = AWEntities.SalesOrderHeaders .Where(order => order.OnlineOrderFlag == true) .Select(s => new { s.SalesOrderID, s.OrderDate, s.SalesOrderNumber }); foreach (var onlineOrder in onlineOrders) { Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}", onlineOrder.SalesOrderID, onlineOrder.OrderDate, onlineOrder.SalesOrderNumber); } } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 以下示例返回订单数量大于 2 且小于 6 的订单。 1 2 3 4 5 6 7 8 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { var query = AWEntities.SalesOrderDetails .Where(order => order.OrderQty > 2 && order.OrderQty < 6) .Select(s => new { s.SalesOrderID, s.OrderQty }); foreach (var order in query) {

Console.WriteLine("Order ID: {0} Order quantity: {1}", order.SalesOrderID, order.OrderQty); } } 9 10 11 12 以下示例返回所有红色产品。 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { var query = AWEntities.Products .Where(product => product.Color == "Red") .Select(p => new { p.Name, p.ProductNumber, p.ListPrice }); foreach (var product in query) { Console.WriteLine("Name: {0}", product.Name); Console.WriteLine("Product number: {0}", product.ProductNumber); Console.WriteLine("List price: ${0}", product.ListPrice); Console.WriteLine(""); } } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Where…Contains 以下示例将一个数组用作 Where¡Contains 子句的一部分，以查找 ProductModelID 与数组中的值匹配的所有产品。 1 2 3 4 5 6 7 8 9 10 11 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { int?[] productModelIds = { 19, 26, 118 }; var products = AWEntities.Products. Where(p => productModelIds.Contains(p.ProductModelID)); foreach (var product in products) { Console.WriteLine("{0}: {1}", product.ProductModelID, product.ProductID); } } 作为 Where¡Contains 子句中谓词的一部分，您可以使用 Array、List<(Of <(<'T>)>)> 或实现 IEnumerable<(Of <(<'T>)>)> 接口的任何类型的集合。还可以在 LINQ to Entities 查询中声明和初始化集合。以下示例声明并初始化 Where¡Contains 子句中的数组，以查找 ProductModelID 或 Size 与数组中的值匹配的所有产品。 1 2 3 4 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { var products = AWEntities.Products. Where(p => (new int?[] { 19, 26, 18 }).Contains(p.ProductModelID) ||

(new string[] { "L", "XL" }).Contains(p.Size)); foreach (var product in products) { Console.WriteLine("{0}: {1}, {2}", product.ProductID, product.ProductModelID, product.Size); } } 5 6 7 8 9 10 11 12 13 ThenBy 采用基于方法的查询语法的以下示例使用 OrderBy 和 ThenBy 以返回先按姓氏后按名字排序的联系人列表。 1 2 3 4 5 6 7 8 9 10 11 12 13 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { IQueryable sortedContacts = AWEntities.Contacts .OrderBy(c => c.LastName) .ThenBy(c => c.FirstName) .Select(c => c); Console.WriteLine("The list of contacts sorted by last name then by first name:"); foreach (Contact sortedContact in sortedContacts) { Console.WriteLine(sortedContact.LastName + ", " + sortedContact.FirstName); } } ThenByDescending 以下示例使用 OrderBy 和 ThenByDescending 方法以首先按标价排序，然后执行产品名称的降序排序。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet products = AWEntities.Products; IOrderedQueryable query = products .OrderBy(product => product.ListPrice) .ThenByDescending(product => product.Name); foreach (Product product in query) { Console.WriteLine("Product ID: {0} Product Name: {1} List Price {2}", product.ProductID, product.Name, product.ListPrice);

} } using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet products = AWEntities.Products; Decimal averageListPrice = products.Average(product => product.ListPrice); Console.WriteLine("The average list price of all the products is ${0}", averageListPrice); } 15 16 Average 以下示例使用 Average 方法来查找产品的平均标价。 1 2 3 4 5 6 7 8 9 10 以下示例使用 Average 方法以查找每种样式的产品的平均标价。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 以下示例使用 Average 方法以查找平均应付款总计。 1 2 3 4 5 6 7 以下示例使用 Average 方法以获取每个联系人 ID 的平均应付款总计。 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet products = AWEntities.Products; var query = from product in products group product by product.Style into g 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); } } using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet orders = AWEntities.SalesOrderHeaders; Decimal averageTotalDue = orders.Average(order => order.TotalDue); Console.WriteLine("The average TotalDue is {0}.", averageTotalDue); }

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet orders = AWEntities.SalesOrderHeaders; var query = from order in orders group order by order.Contact.ContactID into g select new { Category = g.Key, averageTotalDue = g.Average(order => order.TotalDue) }; foreach (var order in query) { Console.WriteLine("ContactID = {0} \t Average TotalDue = {1}", order.Category, order.averageTotalDue); } } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 以下示例使用 Average 方法以针对每个联系人获取具有平均应付款总计的订单。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet orders = AWEntities.SalesOrderHeaders; var query = from order in orders group order by order.Contact.ContactID into g let averageTotalDue = g.Average(order => order.TotalDue) select new { Category = g.Key, CheapestProducts = g.Where(order => order.TotalDue == averageTotalDue) }; foreach (var orderGroup in query) { Console.WriteLine("ContactID: {0}", orderGroup.Category); foreach (var order in orderGroup.CheapestProducts) { Console.WriteLine("Average total due for SalesOrderID {1} is: {0}", order.TotalDue, order.SalesOrderID); } Console.Write("\n");

} 25 26 } Count 以下示例使用 Count 方法以返回 Product 表中的产品数量。 1 2 3 4 5 6 7 8 以下示例使用 Count 方法以返回联系人 ID 的列表和每个联系人 ID 所具有的订单数。 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 以下示例按颜色对产品进行分组，并使用 Count 方法以返回每个颜色组中的产品数量。 1 2 3 4 5 6 7 8 9 using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet contacts = AWEntities.Contacts; //Can't find field SalesOrderContact var query = from contact in contacts select new { CustomerID = contact.ContactID, OrderCount = contact.SalesOrderHeaders.Count() }; foreach (var contact in query) { Console.WriteLine("CustomerID = {0} \t OrderCount = {1}", contact.CustomerID, contact.OrderCount); } } using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet products = AWEntities.Products; int numProducts = products.Count(); Console.WriteLine("There are {0} products.", numProducts); } using (AdventureWorksEntities AWEntities = new AdventureWorksEntities()) { ObjectSet products = AWEntities.Products; var query = from product in products group product by product.Color into g select new { Color = g.Key, ProductCount = g.Count() };

资料库

LINQ_to_EF语法及实例大全.pdf

相关推荐

开发技术

热门标签

最新资料