[Description("This sample uses FIRST and WHERE to get the first (database order) order that is shipped to Seattle. The WHERE predicate is evaluated on the server.")]
public void LinqToEntities42() {
  var query = from o in _em1.Orders
              where o.ShipCity == "Seattle"
              orderby o.OrderID
              select o;

  var result = query.First();
  Assert.IsTrue(result.OrderID == 10269);
}

[Description("This sample uses FIRST to get the first (database order) order that is shipped to Seattle. The predicate is evaluated on the client.")]
public void LinqToEntities43() {
  var query = from o in _em1.Orders
              orderby o.OrderID
              select o;
  var result = query
      .First(x => x.ShipCity == "Bern");
  Assert.IsTrue(result.OrderID == 10254);
}

[Description("This sample uses FIRST, WHERE and ORDER BY to get the first order that is shipped to Seattle, ordered by date. The predicate is evaluated on the server.")]
public void LinqToEntities44() {
  var query = from o in _em1.Orders
              where o.ShipCity == "Seattle"
              orderby o.OrderDate
              select o;

  var result = query.First();
  Assert.IsTrue(result.OrderID == 10269);
}

[Description("This sample uses DISTINCT to get all the categories of products.")]
public void LinqToEntities45() {
  var query = _em1.Products.Select(o => o.Category).Distinct().OrderByDescending(c => c.CategoryName);
  Assert.IsTrue(query.Count() == 8);
  Assert.IsTrue(query.First().CategoryName == "Seafood");
}

[Description("This sample uses UNION to get all the orders where the shipping country was Mexico or Canada.")]
public void LinqToEntities46() {
  var mexico = _em1.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
  var canada = _em1.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);
  var query = mexico.Union(canada).OrderBy(o => o.OrderID);

  Assert.IsTrue(query.Count() == 58);
  Assert.IsTrue(query.First().OrderID == 10259);
}

[Description("This sample uses UNION and DISTINCT to get all the Customers from orders where the shipping country was Mexico or Canada.")]
public void LinqToEntities47() {
  var mexico = _em1.Orders.Where(o => o.ShipCountry == "Mexico").Select(o => o);
  var canada = _em1.Orders.Where(o => o.ShipCountry == "Canada").Select(o => o);
  var union = mexico.Union(canada).Select(o => o.Customer);

  var query = union.Distinct().OrderByDescending(c => c.CompanyName);

  Assert.IsTrue(query.Count() == 8);
  Assert.IsTrue(query.First().CompanyName == "Tortuga Restaurante");
}

[Description("This sample uses CONCAT to get all orders where the shipping country was Mexico or Canada.")]
public void LinqToEntities48() {
  var mexico = _em1.Orders.Where(o => o.ShipCountry == "Mexico").OrderBy(o => o.OrderID).Select(o => o);
  var canada = _em1.Orders.Where(o => o.ShipCountry == "Canada").OrderBy(o => o.OrderID).Select(o => o);

  var query = mexico.Concat(canada);

  Assert.IsTrue(query.Count() == 58);
  var r = query.ToList();
  Assert.IsTrue(r.First().OrderID == 10259);
}

[Description("This sample uses INTERSECT to get common products where an order was shipped to Mexico or Canada.")]
public void LinqToEntities49() {
  var mexico = _em1.OrderDetails.Where(od => od.Order.ShipCountry == "Mexico").OrderBy(o => o.ProductID).Select(od => od.Product);
  var canada = _em1.OrderDetails.Where(od => od.Order.ShipCountry == "Canada").OrderBy(o => o.ProductID).Select(od => od.Product);

  var query = mexico.Intersect(canada);

  Assert.IsTrue(query.Count() == 24);
  var productIds = query.Select(p => p.ProductID).ToList();
  Assert.IsTrue(productIds.Contains(21));
}

[Description("This sample uses INTERSECT to get common products where an order was shipped to Mexico " +
    "or USA in one consolidated query.")]
public void LinqToEntities50() {
  var query = _em1.OrderDetails.Where(od => od.Order.ShipCountry == "Mexico")
      .Select(od => od.Product).Intersect(_em1.OrderDetails
      .Where(od => od.Order.ShipCountry == "USA").Select(o => o.Product));
  Assert.IsTrue(query.Count() == 44);
}

[Description("This sample uses EXCEPT to get customers who shipped orders to Mexico but not Canada.")]
public void LinqToEntities51() {
  var query = _em1.Orders.Where(o => o.ShipCountry == "Mexico")
      .Select(o => o.Customer).Except(_em1.Orders
      .Where(o => o.ShipCountry == "Canada").Select(o => o.Customer));
  Assert.IsTrue(query.Count() == 5);
}

[Description("Variation on LinqToEntities51.")]
public void LinqToEntities51b() {
  var mexico = _em1.Orders.Where(o => o.ShipCountry == "Mexico")
      .Select(o => o.Customer);
  var canada = _em1.Orders.Where(o => o.ShipCountry == "Canada")
      .Select(o => o.Customer);
 // diff => customers with orders shipping to Mexico but none shipping to Canada
 var diff = mexico.Except(canada);

  Assert.IsTrue(mexico.Count() == 28);
  Assert.IsTrue(canada.Count() == 30);
  Assert.IsTrue(diff.Count() == 5);
}

[Description("This sample uses EXCEPT to get customers with no orders sent to Mexico.")]
public void LinqToEntities52() {
  var query = _em1.Customers.Select(e => e)
      .Except(_em1.Orders.Where(o => o.ShipCountry == "Mexico")
      .Select(o => o.Customer));
  Assert.IsTrue(query.Count() == 86);
}

<Description("This sample uses FIRST and WHERE to get the first (database order) order that is shipped to Seattle. The WHERE predicate is evaluated on the server.")>
Public Sub LinqToEntities42()
 Dim query = From o In _em1.Orders
              Where o.ShipCity = "Seattle"
              Order By o.OrderID
             Select o

 Dim result = query.First()
  Assert.IsTrue(result.OrderID = 10269)
End Sub

<Description("This sample uses FIRST to get the first (database order) order that is shipped to Seattle. The predicate is evaluated on the client.")>
Public Sub LinqToEntities43()
 Dim query = From o In _em1.Orders
              Order By o.OrderID
             Select o
 Dim result = query.First(Function(x) x.ShipCity = "Bern")
  Assert.IsTrue(result.OrderID = 10254)
End Sub

<Description("This sample uses FIRST, WHERE and ORDER BY to get the first order that is shipped to Seattle, ordered by date. The predicate is evaluated on the server.")>
Public Sub LinqToEntities44()
 Dim query = From o In _em1.Orders
              Where o.ShipCity = "Seattle"
              Order By o.OrderDate
             Select o

 Dim result = query.First()
  Assert.IsTrue(result.OrderID = 10269)
End Sub

<Description("This sample uses DISTINCT to get all the categories of products.")>
Public Sub LinqToEntities45()
 Dim query = _em1.Products.Select(Function(o) o.Category).Distinct().OrderByDescending(Function(c) c.CategoryName)
  Assert.IsTrue(query.Count() = 8)
  Assert.IsTrue(query.First().CategoryName = "Seafood")
End Sub

<Description("This sample uses UNION to get all the orders where the shipping country was Mexico or Canada.")>
Public Sub LinqToEntities46()
 Dim mexico = _em1.Orders.Where(Function(o) o.ShipCountry = "Mexico").Select(Function(o) o)
 Dim canada = _em1.Orders.Where(Function(o) o.ShipCountry = "Canada").Select(Function(o) o)
 Dim query = mexico.Union(canada).OrderBy(Function(o) o.OrderID)

  Assert.IsTrue(query.Count() = 58)
  Assert.IsTrue(query.First().OrderID = 10259)
End Sub

<Description("This sample uses UNION and DISTINCT to get all the Customers from orders where the shipping country was Mexico or Canada.")>
Public Sub LinqToEntities47()
 Dim mexico = _em1.Orders.Where(Function(o) o.ShipCountry = "Mexico").Select(Function(o) o)
 Dim canada = _em1.Orders.Where(Function(o) o.ShipCountry = "Canada").Select(Function(o) o)
 Dim union = mexico.Union(canada).Select(Function(o) o.Customer)

 Dim query = union.Distinct().OrderByDescending(Function(c) c.CompanyName)

  Assert.IsTrue(query.Count() = 8)
  Assert.IsTrue(query.First().CompanyName = "Tortuga Restaurante")
End Sub

<Description("This sample uses CONCAT to get all orders where the shipping country was Mexico or Canada.")>
Public Sub LinqToEntities48()
 Dim mexico = _em1.Orders.Where(Function(o) o.ShipCountry = "Mexico").OrderBy(Function(o) o.OrderID).Select(Function(o) o)
 Dim canada = _em1.Orders.Where(Function(o) o.ShipCountry = "Canada").OrderBy(Function(o) o.OrderID).Select(Function(o) o)

 Dim query = mexico.Concat(canada)

  Assert.IsTrue(query.Count() = 58)
 Dim r = query.ToList()
  Assert.IsTrue(r.First().OrderID = 10259)
End Sub

<Description("This sample uses INTERSECT to get common products where an order was shipped to Mexico or Canada.")>
Public Sub LinqToEntities49()
 Dim mexico = _em1.OrderDetails.Where(Function(od) od.Order.ShipCountry = "Mexico").OrderBy(Function(o) o.ProductID).Select(Function(od) od.Product)
 Dim canada = _em1.OrderDetails.Where(Function(od) od.Order.ShipCountry = "Canada").OrderBy(Function(o) o.ProductID).Select(Function(od) od.Product)

 Dim query = mexico.Intersect(canada)

  Assert.IsTrue(query.Count() = 24)
 Dim productIds = query.Select(Function(p) p.ProductID).ToList()
  Assert.IsTrue(productIds.Contains(21))
End Sub

<Description("This sample uses INTERSECT to get common products where an order was shipped to Mexico " & "or USA in one consolidated query.")>
Public Sub LinqToEntities50()
 Dim query = _em1.OrderDetails.Where(Function(od) od.Order.ShipCountry = "Mexico").Select(Function(od) od.Product).Intersect(_em1.OrderDetails.Where(Function(od) od.Order.ShipCountry = "USA").Select(Function(o) o.Product))
  Assert.IsTrue(query.Count() = 44)
End Sub

<Description("This sample uses EXCEPT to get customers who shipped orders to Mexico but not Canada.")>
Public Sub LinqToEntities51()
 Dim query = _em1.Orders.Where(Function(o) o.ShipCountry = "Mexico").Select(Function(o) o.Customer).Except(_em1.Orders.Where(Function(o) o.ShipCountry = "Canada").Select(Function(o) o.Customer))
  Assert.IsTrue(query.Count() = 5)
End Sub

<Description("Variation on LinqToEntities51.")>
Public Sub LinqToEntities51b()
 Dim mexico = _em1.Orders.Where(Function(o) o.ShipCountry = "Mexico").Select(Function(o) o.Customer)
 Dim canada = _em1.Orders.Where(Function(o) o.ShipCountry = "Canada").Select(Function(o) o.Customer)
 ' diff => customers with orders shipping to Mexico but none shipping to Canada
 Dim diff = mexico.Except(canada)

  Assert.IsTrue(mexico.Count() = 28)
  Assert.IsTrue(canada.Count() = 30)
  Assert.IsTrue(diff.Count() = 5)
End Sub

<Description("This sample uses EXCEPT to get customers with no orders sent to Mexico.")>
Public Sub LinqToEntities52()
 Dim query = _em1.Customers.Select(Function(e) e).Except(_em1.Orders.Where(Function(o) o.ShipCountry = "Mexico").Select(Function(o) o.Customer))
  Assert.IsTrue(query.Count() = 86)
End Sub