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

LINQ Examples 6

Projection operators

[Description("This samples uses SELECT to get all Customers as Entity Objects.")]
public void LinqToEntities11() {
  var query = from c in _em1.Customers
              select c;
  Assert.IsTrue(query.Count() == 91);
}

[Description("This samples uses SELECT to get all Customer Contact Names as Strings.")]
public void LinqToEntities12() {
  var query = from c in _em1.Customers
              orderby c.ContactName
              select c.ContactName;
  var r = query.ToList();
  Assert.IsTrue(r.First() == "Alejandra Camino");
}

[Description("This samples uses SELECT to get all Customer Contact Names as an anonoymous type.")]
public void LinqToEntities13() {
  var query = from c in _em1.Customers
              orderby c.CompanyName
              select new { c.ContactName };

  var r = query.ToList();
  Assert.IsTrue(r.First().ContactName == "Maria Anders");
}

[Description("This sample uses SELECT to get Orders as anonymous type")]
public void LinqToEntities14() {
  var query = from o in _em1.Orders
              where o.Customer.Address.City == "London"
              orderby o.OrderDate
              select new { o };

  var r = query.ToList();
  Assert.IsTrue(r.Count() == 46);
  Assert.IsTrue(r.First().o.OrderDate == new DateTime(1996, 8, 26));
}

[Description("This sample uses SELECT to get all Orders and associated Customers as anonymous type")]
public void LinqToEntities15() {
  _em1.DefaultQueryStrategy = QueryStrategy.DataSourceOnly;
  var query = from o in _em1.Orders
              where o.Customer.Address.City == "London"
              orderby o.Customer.CompanyName descending
              select new { o, o.Customer };

  var r = query.ToList();
  Assert.IsTrue(r.Count() == 46);
  Assert.IsTrue(r.First().Customer.CompanyName == "Seven Seas Imports");
}

[Description("This sample uses SELECTMANY to get all Orders for a Customer as a flat result")]
public void LinqToEntities16() {
  _em1.DefaultQueryStrategy = QueryStrategy.DataSourceOnly;
  var query = from c in _em1.Customers
              where c.CustomerID == "ALFKI"
              from o in c.Orders
              orderby o.OrderDate
              select o;

  var r = query.ToList();
  Assert.IsTrue(r.Count() == 6);
  Assert.IsTrue(r.First().OrderDate == new DateTime(1997, 8, 25));
}

[Description("This sample uses SELECTMANY to get all Orders for a Customer as a flat result as a method query")]
public void LinqToEntities17() {
  _em1.DefaultQueryStrategy = QueryStrategy.DataSourceOnly;
  var query = _em1.Customers.Where(cust => cust.CustomerID == "ALFKI")
      .SelectMany(cust => cust.Orders
          .OrderBy(o => o.OrderDate));

  var r = query.ToList();
  Assert.IsTrue(r.Count() == 6);
  Assert.IsTrue(r.First().OrderDate == new DateTime(1997, 8, 25));
}

[Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result")]
public void LinqToEntities18() {
  _em1.DefaultQueryStrategy = QueryStrategy.DataSourceOnly;
  var query = from c in _em1.Customers
              where c.Address.Country == "Denmark"
              from o in c.Orders
              orderby o.OrderDate
              select o;

  var r = query.ToList();
  Assert.IsTrue(r.Count() == 18);
  Assert.IsTrue(r.First().OrderDate == new DateTime(1996, 10, 29));
}

[Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result as a method query")]
public void LinqToEntities19() {
  _em1.DefaultQueryStrategy = QueryStrategy.DataSourceOnly;
  var query = _em1.Customers.Where(cust => cust.Address.Country == "Denmark")
      .SelectMany(cust => cust.Orders);

  var r = query.ToList();
  Assert.IsTrue(r.Count() == 18);
}


[Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result")]
public void LinqToEntities20x() {
  _em1.DefaultQueryStrategy = QueryStrategy.DataSourceOnly;
  var query = from c in _em1.Customers
              where c.Address.Country == "Denmark"
              from o in c.Orders
              where o.Freight > 5
              orderby o.OrderDate
              select o;

  Assert.IsTrue(query.Count() == 17);
  Assert.IsTrue(query.First().OrderDate == new DateTime(1996, 10, 29));
}

[Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as an anonymous type containing the Orders and Customer flat result")]
public void LinqToEntities21x() {
  _em1.DefaultQueryStrategy = QueryStrategy.Normal.With(QueryInversionMode.Manual);
  var query = from c in _em1.Customers
              where c.Address.Country == "Denmark"
              orderby c.CompanyName
              from o in c.Orders
              where o.Freight > 5
              orderby o.OrderDate
              select new { c, o};

  Assert.IsTrue(query.Count() == 17);
  var x = query.First();
  Assert.IsTrue(query.First().o.OrderDate == new DateTime(1996, 10, 29));
}

[Description("This sample uses SELECTMANY to get all Orders for Customers in Denmark as a flat result using LINQ opeartors")]
public void LinqToEntities22() {
  _em1.DefaultQueryStrategy = QueryStrategy.DataSourceOnly;
  var query = _em1
      .Customers
      .Where(cust => cust.Address.Country == "Denmark")
      .SelectMany(cust => cust.Orders
          .Where(o => o.Freight > 5)
          .OrderBy(o => o.OrderDate));
  var r = query.ToList();
  Assert.IsTrue(r.Count() == 17);
  Assert.IsTrue(r.First().OrderDate == new DateTime(1996, 10, 29));
}

LINQ Examples 5

Include operators-

[Description("Load OrderDetails with Orders ")]
public void LinqToEntities94() {
  var query0 = _em1.Orders.Include("OrderDetails")
      .Where(c => c.Customer.Address.City == "London").Select(o => o);

  var query1 = query0.OrderBy(o => o.OrderID);
  var r1 = query1.ToList();

  var o1 = query1.First();
  var count1 = o1.OrderDetails.Count();
  Assert.IsTrue(count1 == 2);
}

[Description("Load OrderDetails and Products with Orders ")]
public void LinqToEntities95() {
  var query = _em1.Orders
      .Include("OrderDetails")
      .Include("OrderDetails.Product")
      .Take(3).Select(o => o);

  var query2 = query.OrderByDescending(o => o.OrderID);
  var r2 = query2.ToList();
  var p = r2.First().OrderDetails.First().Product;
  Assert.IsNotNull(p);
  Assert.IsFalse(p.EntityAspect.IsNullOrPendingEntity);
}


class MyClass {
 public static decimal Val = 50;

 public decimal GetVal() {
   return MyClass.Val;
  }
}



Closures



[Description("Uses a local variable as a query parameter.")]
public void LinqToEntities91() {
  MyClass c = new MyClass();

  var query = _em1.Orders
      .Where(o => o.Freight > MyClass.Val).Select(o => o);
  Assert.IsTrue(query.Count() == 360);
}

[Description("Uses a the value of the local variable at query execution time.")]
public void LinqToEntities92() {
 decimal x = 50;
  var query = _em1.Orders.Where(o => o.Freight > x).Select(o => new { o.Freight, o });
  Assert.IsTrue(query.Count() == 360);
  x = 100;
  Assert.IsTrue(query.Count() == 187);
}

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