[Description("Select a sequence of all the orders for a customer using Select.")]
public void LinqToEntities70() {
  var query = _em1.Customers
      .Where(cust => cust.CustomerID == "ALFKI")
      .Select(c => c.Orders.OrderBy(o => o.OrderDate).Select(o => o));

  Assert.IsTrue(query.Count() == 1);
 //query.First() is not available because this doesn't return a list
}

[Description("Select all the orders for a customer using SelectMany.")]
public void LinqToEntities71() {
  var query = _em1.Customers
      .Where(cust => cust.CustomerID == "ALFKI")
      .SelectMany(c => c.Orders);
  Assert.IsTrue(query.Count() == 6);
  Assert.IsTrue(query.First().OrderDate == new DateTime(1997, 8, 25));
}

[Description("Select number of orders placed in 1998 for a customer.")]
public void LinqToEntities74() {
  var query = _em1.Customers
      .Where(cust => cust.CustomerID == "ALFKI")
      .SelectMany(c => c.Orders)
      .Where(o => o.OrderDate.HasValue == true && o.OrderDate.Value.Year == 1998);
  Assert.IsTrue(query.Count() == 3);

  var query2 = query.OrderBy(o => o.OrderDate);
  Assert.IsTrue(query2.First().OrderDate == new DateTime(1998, 1, 15));
}

[Description("Select a customer and the sum of the freight of thier orders.")]
public void LinqToEntities73() {
  var query = _em1.Customers
      .Where(cust => cust.CustomerID == "ALFKI")
      .Select(c => c.Orders.Sum(o => o.Freight));
  Assert.IsTrue(query.First() == 225.58M);
}

[Description("Select customers with an order where the shipping address is the same as the customers.")]
public void LinqToEntities75() {
  var query = _em1.Customers
      .Where(cust => cust.Orders
          .Any(o => o.ShipAddress == cust.Address.Address))
          .Select(c2 => c2);
  Assert.IsTrue(query.Count() == 83);

  var query2 = query.OrderBy(c => c.CompanyName);
  Assert.IsTrue(query.First().CompanyName == "Alfreds Futterkiste");
}

[Description("Selects all regions with a customer, and shows the sum of orders for customers for each region.")]
public void LinqToEntities76() {
  var query = from c in _em1.Customers
              group c by c.Address.Region into regions
              join c2 in _em1.Customers on regions.Key equals c2.Address.Region
              orderby regions.Key descending
              select new { region = regions.Key, total = c2.Orders.Sum(o => o.Freight) };

 // The orderby clause above only works if it *follows*, rather than precedes, the
 // join statement. Somebody explain that to me sometime. - GTD

  Assert.IsTrue(query.First().region == "WY");
  Assert.IsTrue(query.First().total == 558.67M);
}

<Description("Select a sequence of all the orders for a customer using Select.")>
Public Sub LinqToEntities70()
 Dim query = _em1.Customers.Where(Function(cust) cust.CustomerID = "ALFKI").Select(Function(c) c.Orders.OrderBy(Function(o) o.OrderDate).Select(Function(o) o))

  Assert.IsTrue(query.Count() = 1)
 'query.First() is not available because this doesn't return a list
End Sub

<Description("Select all the orders for a customer using SelectMany.")>
Public Sub LinqToEntities71()
 Dim query = _em1.Customers.Where(Function(cust) cust.CustomerID = "ALFKI").SelectMany(Function(c) c.Orders)
  Assert.IsTrue(query.Count() = 6)
  Assert.IsTrue(query.First().OrderDate = New Date(1997, 8, 25))
End Sub

<Description("Select number of orders placed in 1998 for a customer.")>
Public Sub LinqToEntities74()
 Dim query = _em1.Customers.Where(Function(cust) cust.CustomerID = "ALFKI").SelectMany(Function(c) c.Orders).Where(Function(o) o.OrderDate.HasValue = True AndAlso o.OrderDate.Value.Year = 1998)
  Assert.IsTrue(query.Count() = 3)

 Dim query2 = query.OrderBy(Function(o) o.OrderDate)
  Assert.IsTrue(query2.First().OrderDate = New Date(1998, 1, 15))
End Sub

<Description("Select a customer and the sum of the freight of thier orders.")>
Public Sub LinqToEntities73()
 Dim query = _em1.Customers.Where(Function(cust) cust.CustomerID = "ALFKI").Select(Function(c) c.Orders.Sum(Function(o) o.Freight))
  Assert.IsTrue(query.First() = 225.58D)
End Sub

<Description("Select customers with an order where the shipping address is the same as the customers.")>
Public Sub LinqToEntities75()
 Dim query = _em1.Customers.Where(Function(cust) cust.Orders.Any(Function(o) o.ShipAddress = cust.Address.Address)).Select(Function(c2) c2)
  Assert.IsTrue(query.Count() = 83)

 Dim query2 = query.OrderBy(Function(c) c.CompanyName)
  Assert.IsTrue(query.First().CompanyName = "Alfreds Futterkiste")
End Sub

<Description("Selects all regions with a customer, and shows the sum of orders for customers for each region.")>
Public Sub LinqToEntities76()
 Dim query = From c In _em1.Customers
              Group c By c.Address.Region Into regions = Group Join c2 In _em1.Customers On Region Equals c2.Address.Region
              Order By Region Descending
             Select New With {Key .region = Region, Key .total = c2.Orders.Sum(Function(o) o.Freight)}

 ' The orderby clause above only works if it *follows*, rather than precedes, the
 ' join statement. Somebody explain that to me sometime. - GTD

  Assert.IsTrue(query.First().region = "WY")
  Assert.IsTrue(query.First().total = 558.67D)
End Sub