How to: Aggregate Data (Entity Framework)
An aggregation operation computes a single value from a collection of values. An example of an aggregation operation is calculating the average daily temperature from a month's worth of daily temperature values. This topic shows how to group orders by contact ID and get the average total due for each contact ID.
The same example is shown using each of the following Entity Framework query technologies:
LINQ to Entities
Entity SQL with ObjectQuery<T>
The examples in this topic are based on the Adventure Works Sales Model. To run the code in this example, you must have already added the AdventureWorks Sales Model to your project and configured your project to use the Entity Framework. To do this, complete the procedures in How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework). You can also use the Entity Data Model Wizard to define the AdventureWorks Sales Model. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework).
Example
The following is the LINQ to Entities example.
Using AWEntities As New AdventureWorksEntities
Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
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 AWEntities = new AdventureWorksEntities())
{
ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;
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);
}
}
The following is the Entity SQL example.
Using advWorksContext As AdventureWorksEntities = New AdventureWorksEntities
Dim esqlQuery As String = "SELECT contactID, AVG(order.TotalDue) " & _
" FROM(AdventureWorksEntities.SalesOrderHeader)" & _
" AS order GROUP BY order.Contact.ContactID as contactID"
Try
Dim rec As DbDataRecord
Dim query As New ObjectQuery(Of DbDataRecord)(esqlQuery, advWorksContext)
For Each rec In query
Console.WriteLine("ContactID = {0} Average TotalDue = {1} ", _
rec.Item(0), rec.Item(1))
Next
Catch ex As EntityException
Console.WriteLine(ex.ToString())
Catch ex As InvalidOperationException
Console.WriteLine(ex.ToString())
End Try
End Using
using (AdventureWorksEntities advWorksContext =
new AdventureWorksEntities())
{
string esqlQuery = @"SELECT contactID, AVG(order.TotalDue)
FROM AdventureWorksEntities.SalesOrderHeader
AS order GROUP BY order.Contact.ContactID as contactID";
try
{
foreach (DbDataRecord rec in
new ObjectQuery<DbDataRecord>(esqlQuery, advWorksContext))
{
Console.WriteLine("ContactID = {0} Average TotalDue = {1} ",
rec[0], rec[1]);
}
}
catch (EntityException ex)
{
Console.WriteLine(ex.ToString());
}
catch (InvalidOperationException ex)
{
Console.WriteLine(ex.ToString());
}
}
See Also
Tasks
How to: Group Data (Entity Framework)