HOW TO:彙總資料 (Entity Framework)
彙總運算會從值集合計算出單一值。 彙總運算的一個範例是,當您使用一個月中每天的溫度值來計算每天平均溫度時。 本主題顯示如何依連絡人識別碼分組訂單,以及如何取得每個連絡人識別碼的平均總金額。
使用 LINQ to Entities 和 Entity SQL 顯示相同範例:
LINQ to Entities
Entity SQL with ObjectQuery<T>
本主題的範例根據 Adventure Works Sales Model。若要執行此主題中的程式碼,您必須已經將 Adventure Works Sales Model 加入到專案中,並設定您的專案使用 Entity Framework。如需詳細資訊,請參閱 HOW TO:使用實體資料模型精靈 (Entity Framework) 或 HOW TO:手動設定 Entity Framework 專案及 HOW TO:手動設定 Entity Framework 專案。
範例
以下是 LINQ to Entities 範例。
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
From ord In orders _
Let contID = ord.Contact.ContactID _
Group ord By contID Into g = Group _
Select New With _
{ _
.Category = contID, _
.averageTotalDue = _
g.Average(Function(ord) ord.TotalDue) _
}
For Each ord In query
Console.WriteLine("ContactID = {0} " & vbTab & _
" Average TotalDue = {1}", _
ord.Category, ord.averageTotalDue)
Next
End Using
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);
}
}
以下是 Entity SQL 範例。
Using context As New AdventureWorksEntities()
Dim esqlQuery As String = "SELECT contactID, AVG(order.TotalDue) FROM AdventureWorksEntities.SalesOrderHeaders " & _
" AS order GROUP BY order.Contact.ContactID as contactID"
For Each rec As DbDataRecord In New ObjectQuery(Of DbDataRecord)(esqlQuery, context)
Console.WriteLine("ContactID = {0} Average TotalDue = {1} ", rec(0), rec(1))
Next
End Using
using (AdventureWorksEntities context =
new AdventureWorksEntities())
{
string esqlQuery = @"SELECT contactID, AVG(order.TotalDue)
FROM AdventureWorksEntities.SalesOrderHeaders
AS order GROUP BY order.Contact.ContactID as contactID";
foreach (DbDataRecord rec in
new ObjectQuery<DbDataRecord>(esqlQuery, context))
{
Console.WriteLine("ContactID = {0} Average TotalDue = {1} ",
rec[0], rec[1]);
}
}