Database Reference
In-Depth Information
Solution
Suppose that we have a model for customers and their orders, as shown in Figure
11-8
.
Figure 11-8.
A model for customers and their orders
You want to retrieve the number of orders and the total purchase amount made by customers who have placed
orders above the average order.
To create and use this query, follow the pattern shown in Listing 11-15.
Listing 11-15.
Querying the Model in eSQL Using the Sum(), Count(), and Avg() Functions
class Program
{
static void Main(string[] args)
{
RunExample();
}
static void RunExample()
{
using (var context = new EFRecipesEntities())
{
var c1 = new Customer { Name = "Jill Masters", City = "Raytown" };
var c2 = new Customer { Name = "Bob Meyers", City = "Austin" };
var c3 = new Customer { Name = "Robin Rosen", City = "Dallas" };
var o1 = new Order { OrderAmount = 12.99M, Customer = c1 };
var o2 = new Order { OrderAmount = 99.39M, Customer = c2 };
var o3 = new Order { OrderAmount = 101.29M, Customer = c3 };
context.Orders.Add(o1);
context.Orders.Add(o2);
context.Orders.Add(o3);
context.SaveChanges();
}
using (var context = new EFRecipesEntities())
{
Console.WriteLine("Customers with above average total purchases");
var esql = @"select o.Customer.Name, count(o.OrderId) as TotalOrders,
Sum(o.OrderAmount) as TotalPurchases
