public void LinqJoin06()
{
string[] names = { "C#", "VB", "F#" };
var lengths = from name in names select name.Length;
List <Customer> customers = LinqHellper.GetCustomers();
List <Supplier> suppliers = LinqHellper.GetSupplier();
var supplierCusts =
from sup in suppliers
join cust in customers on new { sup.City, sup.Country } equals new { cust.City, cust.Country } into cs
from c in cs.DefaultIfEmpty() //Remove DefaultIfEmpty method call to make this an inner join
orderby sup.SupplierName
select new
{
Country = sup.Country,
City = sup.City,
SupplierName = sup.SupplierName,
CompanyName = c == null ? "(No customers)" : c.CompanyName
};
foreach (var item in supplierCusts)
{
Debug.WriteLine("{0} ({1}, {2}): {3}", item.SupplierName, item.City, item.Country, item.CompanyName);
}
}
//This sample uses Max to get the most expensive price among each category's products.
public void GroupAndMax01()
{
var producs = LinqHellper.GetProducts();
var categories =
from prod in producs
group prod by prod.Category into prodGroup
select new { Category = prodGroup.Key, MostExpensivePrice = prodGroup.Max(p => p.UnitPrice) };
}
public void ElementOperators04()
{
List <Product> products = LinqHellper.GetProducts();
Product product789 = products.FirstOrDefault(p => p.ProductID == 789);
Debug.WriteLine("Product 789 exists: {0}", product789 != null);
}
public void Linq30()
{
List <Product> products = LinqHellper.GetProducts();
var sortedProducts =
from prod in products
orderby prod.ProductName
select prod;
}
public void LinqOrderByDescending05()
{
List <Product> products = LinqHellper.GetProducts();
var sortedProducts =
from prod in products
orderby prod.UnitsInStock descending
select prod;
}
public void LinqOderBy08()
{
List <Product> products = LinqHellper.GetProducts();
var sortedProducts =
from prod in products
orderby prod.Category, prod.UnitPrice descending
select prod;
}
public void ElementOperators01()
{
List <Product> products = LinqHellper.GetProducts();
Product product12 = (
from prod in products
where prod.ProductID == 12
select prod)
.First();
}
public void All02()
{
List <Product> products = LinqHellper.GetProducts();
var productGroups =
from prod in products
group prod by prod.Category into prodGroup
where prodGroup.All(p => p.UnitsInStock > 0)
select new { Category = prodGroup.Key, Products = prodGroup };
}
//This sample uses Count to return a list of customers and how many orders each has.
public void Count03()
{
List <Customer> customers = LinqHellper.GetCustomers();
var orderCounts =
from cust in customers
select new { cust.CustomerID, OrderCount = cust.Orders.Count() };
foreach (var item in orderCounts)
{
Debug.WriteLine("Custormet Id:" + item.CustomerID + " Order Count:" + item.OrderCount);
}
}
//This sample uses Sum to get the total units in stock for each product category.
public void Sum01()
{
List <Product> products = LinqHellper.GetProducts();
var categories =
from prod in products
group prod by prod.Category into prodGroup
select new { Category = prodGroup.Key, TotalUnitsInStock = prodGroup.Sum(p => p.UnitsInStock) };
foreach (var item in categories)
{
Debug.WriteLine("Category: " + item.Category + " TotalUnitsInStock: " + item.TotalUnitsInStock);
}
}
//This sample uses Count to return a list of categories and how many products each has.
public void Count04()
{
List <Product> products = LinqHellper.GetProducts();
var categoryCounts =
from prod in products
group prod by prod.Category into prodGroup
select new { Category = prodGroup.Key, ProductCount = prodGroup.Count() };
foreach (var item in categoryCounts)
{
Debug.WriteLine("Category: " + item.Category + " Product Count: " + item.ProductCount);
}
}
//This sample uses Max to get the products with the most expensive price in each category.
public void Let02()
{
var producs = LinqHellper.GetProducts();
var categories =
from prod in producs
group prod by prod.Category into prodGroup
let maxPrice = prodGroup.Max(p => p.UnitPrice)
select new { Category = prodGroup.Key, MostExpensiveProducts = prodGroup.Where(p => p.UnitPrice == maxPrice) };
foreach (var item in categories)
{
Debug.WriteLine("Category: " + item.Category + " MostExpensiveProduct: " + item.MostExpensiveProducts);
}
}
//This sample uses Min to get the cheapest price among each category's products.
public void Min03()
{
List <Product> products = LinqHellper.GetProducts();
var categories =
from prod in products
group prod by prod.Category into prodGroup
select new { Category = prodGroup.Key, CheapestPrice = prodGroup.Min(p => p.UnitPrice) };
foreach (var item in categories)
{
Debug.WriteLine("Category: " + item.Category + " CheapestPrice: " + item.CheapestPrice);
}
}
//This sample uses Average to get the average price of each category's products.
public void GroupAndAverage()
{
List <Product> products = LinqHellper.GetProducts();
var categories =
from prod in products
group prod by prod.Category into prodGroup
select new { Category = prodGroup.Key, AveragePrice = prodGroup.Average(p => p.UnitPrice) };
foreach (var item in categories)
{
Debug.WriteLine("Category: " + item.Category + " AvaragePrice: " + item.AveragePrice);
}
}
public void Restrict03()
{
List <Product> products = LinqHellper.GetProducts();
var expensiveInStockProducts =
from prod in products
where prod.UnitsInStock > 0 && prod.UnitPrice > 3.00M
select prod;
Debug.WriteLine("In-stock products that cost more than 3.00:");
foreach (var product in expensiveInStockProducts)
{
Debug.WriteLine("{0} is in stock and costs more than 3.00.", product.ProductName);
}
}
public void Restrict02()
{
List <Product> products = LinqHellper.GetProducts();
var soldOutProducts =
from prod in products
where prod.UnitsInStock == 0
select prod;
Debug.WriteLine("Sold out products:");
foreach (var product in soldOutProducts)
{
Debug.WriteLine("{0} is sold out!", product.ProductName);
}
}
public void LinqDistinct02()
{
List <Product> products = LinqHellper.GetProducts();
var categoryNames = (
from prod in products
select prod.Category)
.Distinct();
Debug.WriteLine("Category names:");
foreach (var n in categoryNames)
{
Debug.WriteLine(n);
}
}
public void LinqJoin01()
{
List <Customer> customers = LinqHellper.GetCustomers();
List <Supplier> suppliers = LinqHellper.GetSupplier();
var custSupJoin =
from sup in suppliers
join cust in customers on sup.Country equals cust.Country
select new { Country = sup.Country, SupplierName = sup.SupplierName, CustomerName = cust.CompanyName };
foreach (var item in custSupJoin)
{
Debug.WriteLine("Country = {0}, Supplier = {1}, Customer = {2}", item.Country, item.SupplierName, item.CustomerName);
}
}
public void LinqTake02()
{
List <Customer> customers = LinqHellper.GetCustomers();
var first3WAOrders = (
from cust in customers
from order in cust.Orders
where cust.Region == "WA"
select new { cust.CustomerID, order.OrderID, order.OrderDate })
.Take(3);
Debug.WriteLine("First 3 orders in WA:");
foreach (var order in first3WAOrders)
{
Debug.WriteLine($"Customer ID: {order.CustomerID} Order ID: {order.OrderID} Order Date: {order.OrderDate}");
}
}
public void LinqSkip02()
{
List <Customer> customers = LinqHellper.GetCustomers();
var waOrders =
from cust in customers
from order in cust.Orders
where cust.Region == "WA"
select new { cust.CustomerID, order.OrderID, order.OrderDate };
var allButFirst2Orders = waOrders.Skip(2);
Debug.WriteLine("All but first 2 orders in WA:");
foreach (var order in allButFirst2Orders)
{
Debug.WriteLine($"Customer ID: {order.CustomerID} Order ID: {order.OrderID} Order Date: {order.OrderDate}");
}
}
public void Restrict04()
{
List <Customer> customers = LinqHellper.GetCustomers();
var waCustomers =
from cust in customers
where cust.Region == "WA"
select cust;
Debug.WriteLine("Customers from Washington and their orders:");
foreach (var customer in waCustomers)
{
Debug.WriteLine("Customer {0}: {1}", customer.CustomerID, customer.CompanyName);
foreach (var order in customer.Orders)
{
Debug.WriteLine(" Order {0}: {1}", order.OrderID, order.OrderDate);
}
}
}
/// <summary>
/// This sample uses Concat to create one sequence that contains the names of all customers and products, including any duplicates.
/// </summary>
public void LinqConcat02()
{
List <Customer> customers = LinqHellper.GetCustomers();
List <Product> products = LinqHellper.GetProducts();
var customerNames =
from cust in customers
select cust.CompanyName;
var productNames =
from prod in products
select prod.ProductName;
var allNames = customerNames.Concat(productNames);
Debug.WriteLine("Customer and product names:");
foreach (var n in allNames)
{
Debug.WriteLine(n);
}
}
/// <summary>
/// The Except operator produces the set difference between two sequences.
/// The Except operator allocates and returns an enumerable object that captures the arguments passed to the operator. An ArgumentNullException is thrown if any argument is null.
/// </summary>
public void LinqExcept02()
{
List <Product> products = LinqHellper.GetProducts();
List <Customer> customers = LinqHellper.GetCustomers();
var productFirstChars =
from prod in products
select prod.ProductName[0];
var customerFirstChars =
from cust in customers
select cust.CompanyName[0];
var productOnlyFirstChars = productFirstChars.Except(customerFirstChars);
Debug.WriteLine("First letters from Product names, but not from Customer names:");
foreach (var ch in productOnlyFirstChars)
{
Debug.WriteLine(ch);
}
}
public void LinqUnion02()
{
List <Product> products = LinqHellper.GetProducts();
List <Customer> customers = LinqHellper.GetCustomers();
var productFirstChars =
from prod in products
select prod.ProductName[0];
var customerFirstChars =
from cust in customers
select cust.CompanyName[0];
var uniqueFirstChars = productFirstChars.Union(customerFirstChars);
Debug.WriteLine("Unique first letters from Product names and Customer names:");
foreach (var ch in uniqueFirstChars)
{
Debug.WriteLine(ch);
}
}
public void LinqJoin02()
{
List <Customer> customers = LinqHellper.GetCustomers();
List <Supplier> suppliers = LinqHellper.GetSupplier();
var custSupQuery =
from sup in suppliers
join cust in customers on sup.Country equals cust.Country into cs
select new { Key = sup.Country, Items = cs };
foreach (var item in custSupQuery)
{
Debug.WriteLine(item.Key + ":");
foreach (var element in item.Items)
{
Debug.WriteLine(" " + element.CompanyName);
}
}
}
public void LinqJoin05()
{
List <Customer> customers = LinqHellper.GetCustomers();
List <Supplier> suppliers = LinqHellper.GetSupplier();
var custSuppliers =
from cust in customers
join sup in suppliers on cust.Country equals sup.Country into ss
from s in ss.DefaultIfEmpty()
orderby cust.CompanyName
select new
{
Country = cust.Country,
CompanyName = cust.CompanyName,
SupplierName = s == null ? "(No suppliers)" : s.SupplierName
};
foreach (var item in custSuppliers)
{
Debug.WriteLine("{0} ({1}): {2}", item.CompanyName, item.Country, item.SupplierName);
}
}
public void LinqJoin04()
{
List <Customer> customers = LinqHellper.GetCustomers();
List <Supplier> suppliers = LinqHellper.GetSupplier();
var supplierCusts =
from sup in suppliers
join cust in customers on sup.Country equals cust.Country into cs
from c in cs.DefaultIfEmpty() // DefaultIfEmpty preserves left-hand elements that have no matches on the right side
orderby sup.SupplierName
select new
{
Country = sup.Country,
CompanyName = c == null ? "(No customers)" : c.CompanyName,
SupplierName = sup.SupplierName
};
foreach (var item in supplierCusts)
{
Debug.WriteLine("{0} ({1}): {2}", item.SupplierName, item.Country, item.CompanyName);
}
}
public void LinqJoin03()
{
string[] categories = new string[] {
"Beverages",
"Condiments",
"Vegetables",
"Dairy Products",
"Seafood"
};
List <Product> products = LinqHellper.GetProducts();
var prodByCategory =
from cat in categories
join prod in products on cat equals prod.Category into ps
from p in ps
select new { Category = cat, p.ProductName };
foreach (var item in prodByCategory)
{
Debug.WriteLine(item.ProductName + ": " + item.Category);
}
}
