LINQ Examples 4

Restriction operators- LINQ Where, Any and All operators 

[Description("This sample uses WHERE to find all customers whose contact title is Sales Representative.")]
public void LinqToEntities01() {

  var query = from cust in _em1.Customers
              where cust.ContactTitle == "Sales Representative"
              orderby cust.CompanyName
              select cust;
  var r = query.ToList();
  Assert.IsTrue(r.First().CompanyName == "Alfreds Futterkiste");
}

[Description("This sample uses WHERE to find all orders placed before 1997.")]
public void LinqToEntities02() {
  var metadata = EntityMetadataStore.Instance.GetEntityMetadata(typeof(InternationalOrder));
  DateTime dt = new DateTime(1997, 1, 1);
  var query = from order in _em1.Orders
              where order.OrderDate < dt
              orderby order.OrderDate
              select order;
  Assert.IsTrue(query.First().Freight == 32.38M);
}

[Description("This sample uses WHERE to filter for Products that have stock below their reorder level and have a units on]
public void LinqToEntities03() {
  var query = from p in _em1.Products
              where p.UnitsInStock < p.ReorderLevel && p.UnitsOnOrder == 0
              orderby p.UnitsInStock
              select p;
  Assert.IsTrue(query.First().ProductName == "Nord-Ost Matjeshering");
}

[Description("This sample uses WHERE to filter out Products that have a UnitPrice less than 10.")]
public void LinqToEntities04() {
  var query = from p in _em1.Products
              where p.UnitPrice < 10
              orderby p.ProductName
              select p;
  Assert.IsTrue(query.First().ProductName == "Filo Mix");
}

[Description("This sample uses WHERE to find Employees in London.")]
public void LinqToEntities04a() {
  var query = from e in _em1.Employees
              where e.Address.City == "London"
              orderby e.EmployeeID
              select e;
  Assert.IsTrue(query.First().LastName == "Buchanan");
}

[Description("This sample uses WHERE to get previous employees.")]
public void LinqToEntities05() {
  var query = from e in _em1.Employees
              where e is PreviousEmployee
              orderby e.EmployeeID
              select e;
  var r = query.ToList();
  Assert.IsTrue(r.First().LastName == "King");
}

[Description("This sample uses WHERE to get employees who handle the Boston territory.")]
public void LinqToEntities06() {

  var query = from e in _em1.Employees.OfType<CurrentEmployee>()
              where e.Territories.Any(t => t.TerritoryDescription == "Boston")
              orderby e.EmployeeID
              select e;
  Assert.IsTrue(query.First().LastName == "Fuller");
}

[Description("This sample uses any Customers who placed an order in 1997.")]
public void LinqToEntities07() {
  var query = from c in _em1.Customers
              where c.Orders.Any(o => o.OrderDate.HasValue == true &&
                  o.OrderDate.Value.Year == 1997)
              select c;
  Assert.IsTrue(query.Count() == 85);
}

[Description("This sample uses ANY to check for any out-of-stock products.")]
public void LinqToEntities08() {
  var query = _em1
      .Suppliers
      .Where(s => s.Products
                 .Any(p => p.UnitsInStock == 0))
      .Select(s => s);
  Assert.IsTrue(query.Count() == 5);
}

[Description("This sample uses WHERE and ANY to get orders containing a product with a unit on order.")]
public void LinqToEntities09() {
  var query = from o in _em1.Orders
              where o.OrderDetails.Any(od => od.Product.UnitsOnOrder > 0)
              select o;
  Assert.IsTrue(query.ToList().Count == 366);
  Assert.IsTrue(query.Count() == 366);
}

[Description("This sample uses COUNT to get Products sold to Customers in the same Country " +
    "as the Products' Suppliers, and where all the Products in the order were from the same Country.")]
public void LinqToEntities10() {
  var query = from p in _em1.Products
              where p.OrderDetails.Count(od => od.Order.Customer.Address.Country ==
                  p.Supplier.Address.Country) > 2
              select p;
  Assert.IsTrue(query.Count() == 20);
}


Paging operators- LINQ Take and Skip operators

[Description("This sample uses WHERE to find all customers whose contact title is Sales Representative.")]
public void LinqToEntities96a() {

 // Not a variation of LinqToEntities96, but no numbers were available here.
  var customersQuery = _em1.Customers.OrderBy(c => c.CompanyName);
  customersQuery.QueryStrategy = QueryStrategy.DataSourceOnly;
  ICollection<Customer> customers = customersQuery.Skip(5).Take(5).ToList();
  Assert.IsTrue(customers.Count() == 5);
  customersQuery.QueryStrategy = QueryStrategy.CacheOnly;
  Assert.IsTrue(customersQuery.Count() == 5);
}

[Description("Skip the most recent 2 orders from customers in London")]
public void LinqToEntities96() {
  var query = _em1.Orders
      .Where(o => o.Customer.Address.City == "London")
      .OrderBy(o => o.OrderDate)
      .Skip(2).Select(o => o);

  Assert.IsTrue(query.First().OrderID == 10359);
}

[Description("Take the 2 most recent Orders ")]
public void LinqToEntities97() {
  var query = _em1.Orders
      .OrderBy(o => o.OrderDate)
      .Take(2).Select(o => o);

  Assert.IsTrue(query.Count() == 2);
  Assert.IsTrue(query.First().OrderID == 10248);
}

[Description("Take the 10th to the 20th Orders, ordered by date ")]
public void LinqToEntities98() {
  var query = _em1.Orders
      .OrderBy(o => o.OrderDate)
      .Skip(10).Take(10).Select(o => o);

  query.QueryStrategy = QueryStrategy.DataSourceOnly; // because of skip operator
  var r = query.ToList();
  Assert.IsTrue(r.Count() == 10);
  Assert.IsTrue(r.First().OrderID == 10258);
}

[Description("Use a page number variable to get the xth page")]
public void LinqToEntities99() {
 int pageSize = 10;
 int pageNumber = 4;

  var query = _em1.Orders
      .OrderBy(o => o.OrderDate)
      .Skip(pageSize * pageNumber).Take(pageSize).Select(o => o);
  query.QueryStrategy = QueryStrategy.DataSourceOnly;
  var r = query.ToList();
  Assert.IsTrue(r.Count() == pageSize);
  Assert.IsTrue(r.First().OrderID == 10288);
}


Linq Query to Select Top 5

var list = (from t in ctn.Items           
                 where t.DeliverySelection == true && t.Delivery.SentForDelivery == null           
                 orderby t.Delivery.SubmissionDate select t).Take(5);