LINQ Examples 7

Aggregate operators- LINQ Count, Sum, Min, Max and Average operators 

[Description("This sample uses COUNT to get the number of Orders.")]
public void LinqToEntities23() {
  var query = _em1.Orders.Count();
  Assert.IsTrue(query == 830);
}

[Description("This sample uses COUNT to get the number of Orders placed by Customers in Mexico.")]
public void LinqToEntities24() {
  var query = _em1.Orders.Where(o => o.Customer.Address.Country == "Mexico").Count();
  Assert.IsTrue(query == 28);
}

[Description("This sample uses COUNT to get the number of Orders shipped to Mexico.")]
public void LinqToEntities25() {
  var query = _em1.Orders
      .Where(o => o.ShipCountry == "Mexico").Count();
  Assert.IsTrue(query == 28);
}

[Description("This sample uses SUM to find the total freight over all Orders.")]
public void LinqToEntities26() {
  var query = _em1.Orders.Select(o => o.Freight).Sum();
  Assert.IsTrue(query == 64942.69M);
}

[Description("This sample uses SUM to find the total number of units on order over all Products.")]
public void LinqToEntities27() {
  var query = _em1.Products.Sum(p => p.UnitsOnOrder);
  Assert.IsTrue(query == 780);
}

[Description("This sample uses SUM to find the total number of units on order over all Products out-of-stock.")]
public void LinqToEntities28() {
  var query = _em1.Products.Where(p => p.UnitsInStock == 0).Sum(p => p.UnitsOnOrder);
  Assert.IsTrue(query == 70);
}

[Description("This sample uses MIN to find the lowest unit price of any Product.")]
public void LinqToEntities29() {
  var query = _em1.Products.Select(p => p.UnitPrice).Min();
  Assert.IsTrue(query == 2.5M);
}

[Description("This sample uses MIN to find the lowest freight of any Order.")]
public void LinqToEntities30() {
  var query = _em1.Orders.Min(o => o.Freight);
  Assert.IsTrue(query == 0.02M);
}

[Description("This sample uses MIN to find the lowest freight of any Order shipped to Mexico.")]
public void LinqToEntities31() {
  var query = _em1.Orders.Where(o => o.ShipCountry == "Mexico").Min(o => o.Freight);
  Assert.IsTrue(query == 0.4M);
  var query2 = _em1.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o.Freight).Min();
  Assert.IsTrue(query2 == 0.4M);
}

[Description("This sample uses Min to find the Products that have the lowest unit price " +
    "in each category, and returns the result as an anonoymous type.")]
public void LinqToEntities32() {
  var query = from p in _em1.Products
              group p by p.Category.CategoryID into g
              orderby g.Key
              select new {
                CategoryID = g.Key,
                CheapestProducts =
                    from p2 in g
                    where p2.UnitPrice == g.Min(p3 => p3.UnitPrice)
                    select p2
              };

  var r = query.ToList();
  Assert.IsTrue(r.Count() == 8);
  Assert.IsTrue(r.First().CategoryID == 1);
  Assert.IsTrue(r.First().CheapestProducts.First().UnitPrice == 4.5M);
}

[Description("This sample uses MAX to find the latest hire date of any Employee.")]
public void LinqToEntities33() {
  var query = _em1.Employees.Select(e => e.HireDate).Max();
  Assert.IsTrue(query == new DateTime(1994, 11, 15));
}

[Description("This sample uses MAX to find the most units in stock of any Product.")]
public void LinqToEntities34() {
  var query = _em1.Products.Max(p => p.UnitsInStock);
  Assert.IsTrue(query == 125);
}

[Description("This sample uses MAX to find the most units in stock of any Product with CategoryID = 1.")]
public void LinqToEntities35() {
  var query = _em1.Products.Where(p => p.Category.CategoryID == 2).Max(p => p.UnitsInStock);
  Assert.IsTrue(query == 120);
}

[Description("This sample uses MAX to find the Products that have the " +
    "highest unit price in each category, and returns the result as an anonoymous type.")]
public void LinqToEntities36() {
  var query = from p in _em1.Products
              group p by p.Category.CategoryID into g
              orderby g.Key
              select new {
                g.Key,
                MostExpensiveProducts =
                    from p2 in g
                    where p2.UnitPrice == g.Max(p3 => p3.UnitPrice)
                    orderby p2.UnitPrice
                    select p2
              };

  var r = query.ToList();
  Assert.IsTrue(r.Count() == 8);
  Assert.IsTrue(r.First().Key == 1);
  Assert.IsTrue(r.First().MostExpensiveProducts.First().UnitPrice == 263.5M);
}

[Description("This sample uses AVERAGE to find the average freight of all Orders.")]
public void LinqToEntities37() {
  var query = _em1.Orders.Select(o => o.Freight).Average();
  Assert.IsTrue(query == 78.2442M);
}

[Description("This sample uses AVERAGE to find the average unit price of all Products.")]
public void LinqToEntities38() {
  var query = _em1.Products.Average(p => p.UnitPrice);
  Assert.IsTrue(query == 28.8663M);
}

[Description("This sample uses AVERAGE to find the average unit price of all Products with CategoryID = 1.")]
public void LinqToEntities39() {
  var query = _em1.Products.Where(p => p.Category.CategoryID == 1)
      .Average(p => p.UnitPrice);
  Assert.IsTrue(query == 37.9791M);
}

[Description("This sample uses AVERAGE to find the Products that have unit price higher than the average unit price of the category for each category.")]
public void LinqToEntities40() {

  var query = from p in _em1.Products
              group p by p.Category.CategoryID into g
              orderby g.Key descending
              select new {
                g.Key,
                ExpensiveProducts =
                    from p2 in g
                    where p2.UnitPrice > g.Average(p3 => p3.UnitPrice)
                    orderby p2.UnitPrice
                    select p2
              };

  var r = query.ToList();
  Assert.IsTrue(r.Count() == 8);
  Assert.IsTrue(r.First().Key == 8);
}

[Description("This sample uses AVERAGE to find the average unit price of each category.")]
public void LinqToEntities41() {
  var query = from p in _em1.Products
              group p by p.Category.CategoryID into g
              orderby g.Key descending
              select new {
                g.Key,
                Average = g.Average(p => p.UnitPrice)
              };
  Assert.IsTrue(query.ToList().First().Key == 8);
  Assert.IsTrue(query.First().Average == 20.6825M);
}