发布：2021/7/9 16:23:22作者：管理员 来源：本站 浏览次数：1120

本主题中的示例演示如何使用 Aggregate 、 Average 、、、 Count 、 LongCount Max Min 和 Sum 方法，通过基于方法的查询语法来查询 AdventureWorks 销售模型 。 这些示例中使用的 AdventureWorks 销售模型从 AdventureWorks 示例数据库中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 等表生成。

本主题中的示例使用以下 using / Imports 语句：

C#

using System;

using System.Data;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Objects;

using System.Globalization;

using System.Data.EntityClient;

using System.Data.SqlClient;

using System.Data.Common;

平均值

示例

以下示例使用 Average 方法以查找产品的平均标价。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<Product> products = context.Products;

   Decimal averageListPrice =

       products.Average(product => product.ListPrice);

   Console.WriteLine("The average list price of all the products is ${0}",

       averageListPrice);

}

示例

以下示例使用 Average 方法以查找每种样式的产品的平均标价。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<Product> products = context.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);

   }

}

示例

以下示例使用 Average 方法以查找平均应付款总计。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

   Decimal averageTotalDue = orders.Average(order => order.TotalDue);

   Console.WriteLine("The average TotalDue is {0}.", averageTotalDue);

}

示例

以下示例使用 Average 方法以获取每个联系人 ID 的平均应付款总计。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderHeader> orders = context.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);

   }

}

示例

以下示例使用 Average 方法以针对每个联系人获取具有平均应付款总计的订单。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderHeader> orders = context.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");

   }

}

计数

示例

以下示例使用 Count 方法以返回 Product 表中的产品数量。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<Product> products = context.Products;

   int numProducts = products.Count();

   Console.WriteLine("There are {0} products.", numProducts);

}

示例

以下示例使用 Count 方法以返回联系人 ID 的列表和每个联系人 ID 所具有的订单数。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<Contact> contacts = context.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);

   }

}

示例

以下示例按颜色对产品进行分组，并使用 Count 方法以返回每个颜色组中的产品数量。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<Product> products = context.Products;

   var query =

       from product in products

       group product by product.Color into g

       select new { Color = g.Key, ProductCount = g.Count() };

   foreach (var product in query)

   {

       Console.WriteLine("Color = {0} \t ProductCount = {1}",

           product.Color,

           product.ProductCount);

   }

}

LongCount

示例

以下示例以长整型获取联系人计数。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<Contact> contacts = context.Contacts;

   long numberOfContacts = contacts.LongCount();

   Console.WriteLine("There are {0} Contacts", numberOfContacts);

}

Max

示例

以下示例使用 Max 方法以获取最大应付款总计。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

   Decimal maxTotalDue = orders.Max(w => w.TotalDue);

   Console.WriteLine("The maximum TotalDue is {0}.",

       maxTotalDue);

}

示例

以下示例使用 Max 方法以获取每个联系人 ID 的最大应付款总计。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

   var query =

       from order in orders

       group order by order.Contact.ContactID into g

       select new

       {

           Category = g.Key,

           maxTotalDue =

               g.Max(order => order.TotalDue)

       };

   foreach (var order in query)

   {

       Console.WriteLine("ContactID = {0} \t Maximum TotalDue = {1}",

           order.Category, order.maxTotalDue);

   }

}

示例

以下示例使用 Max 方法以针对每个联系人 ID 获取具有最大应付款总计的订单。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

   var query =

       from order in orders

       group order by order.Contact.ContactID into g

       let maxTotalDue = g.Max(order => order.TotalDue)

       select new

       {

           Category = g.Key,

           CheapestProducts =

               g.Where(order => order.TotalDue == maxTotalDue)

       };

   foreach (var orderGroup in query)

   {

       Console.WriteLine("ContactID: {0}", orderGroup.Category);

       foreach (var order in orderGroup.CheapestProducts)

       {

           Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ",

               order.TotalDue,

               order.SalesOrderID);

       }

       Console.Write("\n");

   }

}

Min

示例

以下示例使用 Min 方法以获取最小应付款总计。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

   Decimal smallestTotalDue = orders.Min(totalDue => totalDue.TotalDue);

   Console.WriteLine("The smallest TotalDue is {0}.",

       smallestTotalDue);

}

示例

以下示例使用 Min 方法以获取每个联系人 ID 的最小应付款总计。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

   var query =

       from order in orders

       group order by order.Contact.ContactID into g

       select new

       {

           Category = g.Key,

           smallestTotalDue =

               g.Min(order => order.TotalDue)

       };

   foreach (var order in query)

   {

       Console.WriteLine("ContactID = {0} \t Minimum TotalDue = {1}",

           order.Category, order.smallestTotalDue);

   }

}

示例

以下示例使用 Min 方法以针对每个联系人获取具有最小应付款总计的订单。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

   var query =

       from order in orders

       group order by order.Contact.ContactID into g

       let minTotalDue = g.Min(order => order.TotalDue)

       select new

       {

           Category = g.Key,

           smallestTotalDue =

               g.Where(order => order.TotalDue == minTotalDue)

       };

   foreach (var orderGroup in query)

   {

       Console.WriteLine("ContactID: {0}", orderGroup.Category);

       foreach (var order in orderGroup.smallestTotalDue)

       {

           Console.WriteLine("Mininum TotalDue {0} for SalesOrderID {1}: ",

               order.TotalDue,

               order.SalesOrderID);

       }

       Console.Write("\n");

   }

}

Sum

示例

以下示例使用 Sum 方法以获取 SalesOrderDetail 表中订单数量的总数。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderDetail> orders = context.SalesOrderDetails;

   double totalOrderQty = orders.Sum(o => o.OrderQty);

   Console.WriteLine("There are a total of {0} OrderQty.",

       totalOrderQty);

}

示例

以下示例使用 Sum 方法以获取每个联系人 ID 的应付款总计。

C#

using (AdventureWorksEntities context = new AdventureWorksEntities())

{

   ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

   var query =

       from order in orders

       group order by order.Contact.ContactID into g

       select new

       {

           Category = g.Key,

           TotalDue = g.Sum(order => order.TotalDue)

       };

   foreach (var order in query)

   {

       Console.WriteLine("ContactID = {0} \t TotalDue sum = {1}",

           order.Category, order.TotalDue);

   }

}

请参阅

   LINQ to Entities 中的查询