[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);
}

<Description("This sample uses COUNT to get the number of Orders.")>
Public Sub LinqToEntities23()
 Dim query = _em1.Orders.Count()
  Assert.IsTrue(query = 830)
End Sub

<Description("This sample uses COUNT to get the number of Orders placed by Customers in Mexico.")>
Public Sub LinqToEntities24()
 Dim query = _em1.Orders.Where(Function(o) o.Customer.Address.Country = "Mexico").Count()
  Assert.IsTrue(query = 28)
End Sub

<Description("This sample uses COUNT to get the number of Orders shipped to Mexico.")>
Public Sub LinqToEntities25()
 Dim query = _em1.Orders.Where(Function(o) o.ShipCountry = "Mexico").Count()
  Assert.IsTrue(query = 28)
End Sub

<Description("This sample uses SUM to find the total freight over all Orders.")>
Public Sub LinqToEntities26()
 Dim query = _em1.Orders.Select(Function(o) o.Freight).Sum()
  Assert.IsTrue(query Is 64942.69D)
End Sub

<Description("This sample uses SUM to find the total number of units on order over all Products.")>
Public Sub LinqToEntities27()
 Dim query = _em1.Products.Sum(Function(p) p.UnitsOnOrder)
  Assert.IsTrue(query = 780)
End Sub

<Description("This sample uses SUM to find the total number of units on order over all Products out-of-stock.")>
Public Sub LinqToEntities28()
 Dim query = _em1.Products.Where(Function(p) p.UnitsInStock = 0).Sum(Function(p) p.UnitsOnOrder)
  Assert.IsTrue(query = 70)
End Sub

<Description("This sample uses MIN to find the lowest unit price of any Product.")>
Public Sub LinqToEntities29()
 Dim query = _em1.Products.Select(Function(p) p.UnitPrice).Min()
  Assert.IsTrue(query Is 2.5D)
End Sub

<Description("This sample uses MIN to find the lowest freight of any Order.")>
Public Sub LinqToEntities30()
 Dim query = _em1.Orders.Min(Function(o) o.Freight)
  Assert.IsTrue(query Is 0.02D)
End Sub

<Description("This sample uses MIN to find the lowest freight of any Order shipped to Mexico.")>
Public Sub LinqToEntities31()
 Dim query = _em1.Orders.Where(Function(o) o.ShipCountry = "Mexico").Min(Function(o) o.Freight)
  Assert.IsTrue(query Is 0.4D)
 Dim query2 = _em1.Orders.Where(Function(o) o.ShipCountry = "Mexico").Select(Function(o) o.Freight).Min()
  Assert.IsTrue(query2 Is 0.4D)
End Sub

<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 Sub LinqToEntities32()
 Dim query = From p In _em1.Products
              Group p By p.Category.CategoryID Into g = Group
              Order By CategoryID
             Select New With {Key .CategoryID = CategoryID, Key .CheapestProducts =
                  From p2 In g
                  Where p2.UnitPrice = g.Min(Function(p3) p3.UnitPrice)
                 Select p2}

 Dim r = query.ToList()
  Assert.IsTrue(r.Count() = 8)
  Assert.IsTrue(r.First().CategoryID = 1)
  Assert.IsTrue(r.First().CheapestProducts.First().UnitPrice = 4.5D)
End Sub

<Description("This sample uses MAX to find the latest hire date of any Employee.")>
Public Sub LinqToEntities33()
 Dim query = _em1.Employees.Select(Function(e) e.HireDate).Max()
  Assert.IsTrue(query Is New Date(1994, 11, 15))
End Sub

<Description("This sample uses MAX to find the most units in stock of any Product.")>
Public Sub LinqToEntities34()
 Dim query = _em1.Products.Max(Function(p) p.UnitsInStock)
  Assert.IsTrue(query = 125)
End Sub

<Description("This sample uses MAX to find the most units in stock of any Product with CategoryID = 1.")>
Public Sub LinqToEntities35()
 Dim query = _em1.Products.Where(Function(p) p.Category.CategoryID = 2).Max(Function(p) p.UnitsInStock)
  Assert.IsTrue(query = 120)
End Sub

<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 Sub LinqToEntities36()
 Dim query = From p In _em1.Products
              Group p By p.Category.CategoryID Into g = Group
              Order By CategoryID
             Select New With {Key CategoryID, Key .MostExpensiveProducts =
                  From p2 In g
                  Where p2.UnitPrice = g.Max(Function(p3) p3.UnitPrice)
                  Order By p2.UnitPrice
                 Select p2}

 Dim r = query.ToList()
  Assert.IsTrue(r.Count() = 8)
  Assert.IsTrue(r.First().Key = 1)
  Assert.IsTrue(r.First().MostExpensiveProducts.First().UnitPrice = 263.5D)
End Sub

<Description("This sample uses AVERAGE to find the average freight of all Orders.")>
Public Sub LinqToEntities37()
 Dim query = _em1.Orders.Select(Function(o) o.Freight).Average()
  Assert.IsTrue(query Is 78.2442D)
End Sub

<Description("This sample uses AVERAGE to find the average unit price of all Products.")>
Public Sub LinqToEntities38()
 Dim query = _em1.Products.Average(Function(p) p.UnitPrice)
  Assert.IsTrue(query Is 28.8663D)
End Sub

<Description("This sample uses AVERAGE to find the average unit price of all Products with CategoryID = 1.")>
Public Sub LinqToEntities39()
 Dim query = _em1.Products.Where(Function(p) p.Category.CategoryID = 1).Average(Function(p) p.UnitPrice)
  Assert.IsTrue(query Is 37.9791D)
End Sub

<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 Sub LinqToEntities40()

 Dim query = From p In _em1.Products
              Group p By p.Category.CategoryID Into g = Group
              Order By CategoryID Descending
             Select New With {Key CategoryID, Key .ExpensiveProducts =
                  From p2 In g
                  Where p2.UnitPrice > g.Average(Function(p3) p3.UnitPrice)
                  Order By p2.UnitPrice
                 Select p2}

 Dim r = query.ToList()
  Assert.IsTrue(r.Count() = 8)
  Assert.IsTrue(r.First().Key = 8)
End Sub

<Description("This sample uses AVERAGE to find the average unit price of each category.")>
Public Sub LinqToEntities41()
 Dim query = From p In _em1.Products
              Group p By p.Category.CategoryID Into g = Group
              Order By CategoryID Descending
             Select New With {Key CategoryID, Key .Average = g.Average(Function(p) p.UnitPrice)}
  Assert.IsTrue(query.ToList().First().Key = 8)
  Assert.IsTrue(query.First().Average = 20.6825D)
End Sub