using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using LinqToSql;
namespace NorthwindLinqTest {
public partial class NorthwindLinq : Form {
#region Create connection to datasource
//connection to SqlServer Northwind db- Defined in project properties
DbProviderFactory factory = DbProviderFactories.GetFactory(
global::NorthwindLinqTest.Properties.Settings.Default.SQLProvider);
string connectionString = global::NorthwindLinqTest.Properties.Settings.Default.Northwind_SqlConnectionString;
// Connection to Access Northwind db (mdb file is in bin\debug directory)
/*DbProviderFactory factory = DbProviderFactories.GetFactory(
global::NorthwindLinqTest.Properties.Settings.Default.AccessProvider);
string connectionString = global::NorthwindLinqTest.Properties.Settings.Default.Northwind_AccessConnectionString;
*/
DbConnection connection = null;
#endregion
#region Create Query Sources
QueryProvider provider = null;
IQueryable<Customer> customers = null;
IQueryable<Order> orders = null;
IQueryable<OrderDetail> orderDetails = null;
IQueryable<Product> products = null;
IQueryable<Category> categories = null;
#endregion
public NorthwindLinq() {
InitializeComponent();
connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
// Do not open the connection
provider = new SqlQueryProvider(connection);
customers = new Query<Customer>(provider);
orders = new Query<Order>(provider);
orderDetails = new Query<OrderDetail>(provider);
products = new Query<Product>(provider);
categories = new Query<Category>(provider);
}
private void btnTest_Click(object sender, EventArgs e) {
Test1();
//Test2();
//Test3();
//Test4();
//Test5();
//Test6();
//Test7();
//Test8();
//Test9();
//Test10();
//Test11();
//Test12();
//Test13();
//Test14(); not implemented yet
//Test15(); not implemented yet
//Linq2();
//Linq3();
//Linq7();
//Linq11();
//Linq15();
//Linq16();
//Linq18();
//Linq21();
//Linq23();
//Linq30();
//Linq42();
//Linq43();
//Linq47();
//Linq58();
//Linq62();
//Linq69();
//Linq72();
//Linq76();
//Linq77();
//Linq80();
//Linq83();
//Linq84();
//Linq91();
//Linq95();
}
public void Test1() {
var city = "London";
var country = "Brazil";
var x = customers.Select(c => new
{
Name = c.ContactName,
Location = new
{
City = c.City,
Country = c.Country,
Revenues = new
{
TotalRevenue = (from o in orders
where o.CustomerID == c.CustomerID
select o.OrderDetails.Sum(od => od.UnitPrice * od.Quantity)).Sum(),
AverageRevenue = (from o in orders
where o.CustomerID == c.CustomerID
select o.OrderDetails.Average(od => od.UnitPrice * od.Quantity)).Average()
}
}
})
.Where(v => v.Location.City == city || v.Location.Country == country)
.OrderBy(w => w.Name);
ObjectDumper.Write(x, 3);
}
public void Test2() {
var cutoffDate = new DateTime(1995, 1, 1);
var x = from c in customers
join o in orders on c.CustomerID equals o.CustomerID
where c.CustomerID.StartsWith("A") && o.OrderDate > cutoffDate
orderby c.ContactName, o.OrderDate descending
select new { Name = c.ContactName, o.OrderDate };
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
public void Test3() {
int orderCutoff = 20;
var x = from c in customers
where c.Orders.Count() > orderCutoff
orderby c.CustomerID
select new
{
c.CustomerID,
Name = c.ContactName,
// BUG: order of sum and order count been reversed
// OrderCount = c.Orders.Count(),
SumFreight = c.Orders.Sum(o => o.Freight),
};
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
public void Test4() {
var country = "USA";
var region = "WA";
var x = from c in customers
where c.Country == country && c.Region == region
orderby c.CustomerID descending
join o in orders on c.CustomerID equals o.CustomerID
orderby o.OrderDate descending
select new { c.ContactName, o.OrderDate }
;
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
public void Test5() {
var country = "UK";
var x = from c in customers
orderby c.City, c.ContactName
where c.Country == country
select new
{
Name = c.ContactName,
Orders = from o in orders
orderby o.OrderDate descending, o.RequiredDate descending
where (o.CustomerID == c.CustomerID)
select new { o.OrderDate, o.RequiredDate }
};
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
public void Test6() {
var cutoffDate = DateTime.Parse("4/1/1998");
var x = from c in customers
orderby c.ContactName
from o in orders
orderby o.OrderDate
where c.CustomerID == o.CustomerID && o.OrderDate > cutoffDate
select new { Name = c.ContactName, o.OrderDate };
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
public void Test7() {
var x = from product in products
orderby product.ProductName
select new
{
product.ProductID,
product.ProductName,
ProductCategory = (from category in categories
where category.CategoryID == product.CategoryID
select category.CategoryName).First()
};
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
public void Test8() {
var x = from c in customers
orderby c.City
where c.Country == "USA"
select new { c.City, c.ContactName } into customerLite
group customerLite by customerLite.City;
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
public void Test9() {
var orderList = from o in orders
join c in customers on o.CustomerID equals c.CustomerID
orderby c.ContactName
select new
{
Name = c.ContactName,
c.Country,
c.City,
Revenue = o.OrderDetails.Sum(p => p.UnitPrice * p.Quantity)
};
var customerRevenue = from o in orderList
group o by o.Name into g
select new
{
CustomerName = g.Key,
Country = g.Select(a => a.Country).First(),
City = g.Select(b => b.City).First(),
TotalRevenue = g.Sum(o => o.Revenue)
};
Func<decimal, int> getRevenueGroup = (decimal revenue) => (int)revenue / 1000;
var revenueGroups = from c in customerRevenue
group c by getRevenueGroup(c.TotalRevenue) into revenues
select new { revenues.Key, revenues };
ObjectDumper.Write(revenueGroups, 3);
}
public void Test10() {
var x = from c in customers
orderby c.City
where c.City.Contains("ri") || c.Country.EndsWith("o")
select new { c.Country, c.City, c.ContactName };
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
public void Test11() {
var x = from c in customers
orderby c.City
where c.Country.Substring(0, 3) == "Ge"
select new { c.Country, c.City, c.ContactName };
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
public void Test12() {
var x = from c in customers
orderby c.City
select new { Country = c.Country.ToUpper(),
City = c.City.ToLower(),
Name = c.ContactName };
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
public void Test13() {
var x = from p in products
where p.OrderDetails.Count() > 50
orderby p.ProductName
select new
{
Name = p.ProductName,
Revenue = p.OrderDetails.Sum(o => o.Quantity * o.UnitPrice)
};
var y = x.ToList();
ObjectDumper.Write(y, 3);
}
// not implemented
public void Test14() {
var x = from order in orders
where order.OrderDate.Value.Year > DateTime.Parse("1/1/1997").Year
select new { order.OrderID, order.OrderDate };
}
// not implemented
public void Test15() {
var city = "London";
var x = customers.Select(c => new
{
Name = c.ContactName,
Location = new
{
City = c.City,
Country = c.Country,
Revenues = new
{
Revenue =
c.Orders.Sum(o => o.OrderDetails.Sum(p => p.UnitPrice * p.Quantity))
}
}
})
.Where(v => v.Location.City == city || v.Location.Country == "Brazil");
}
public void Linq2() {
var soldOutProducts = from p in products
where p.UnitsInStock == 0
select p;
Debug.WriteLine("Sold out products:");
foreach (var product in soldOutProducts) {
Debug.WriteLine(string.Format("{0} is sold out!", product.ProductName));
}
}
public void Linq3() {
var expensiveInStockProducts = from p in products
where p.UnitsInStock > 0 && p.UnitPrice > 3.00M
select p;
Debug.WriteLine("In-stock products that cost more than 3.00:");
foreach (var product in expensiveInStockProducts) {
Debug.WriteLine(string.Format("{0} is in stock and costs more than 3.00.",
product.ProductName));
}
}
public void Linq7() {
var productNames = from p in products
select p.ProductName;
Debug.WriteLine("Product Names:");
foreach (var productName in productNames) {
Debug.WriteLine(productName);
}
}
public void Linq11() {
var productInfos = from p in products
select new { p.ProductName, p.CategoryID, Price = p.UnitPrice };
Debug.WriteLine("Product Info:");
foreach (var productInfo in productInfos) {
Debug.WriteLine(string.Format("{0} is in the category {1} and costs {2} per unit.",
productInfo.ProductName, productInfo.CategoryID, productInfo.Price));
}
}
public void Linq15() {
var customerList = (from c in customers
select new
{
c.CustomerID,
CustomerOrders = from o in orders
where c.CustomerID == o.CustomerID
select new
{
o.OrderID,
Total = o.OrderDetails.Sum(od => od.UnitPrice * od.Quantity)
}
}).ToList();
var customerOrders = from c in customerList
from o in c.CustomerOrders
where o.Total < 500.00M
select new { c.CustomerID, o.OrderID, o.Total };
ObjectDumper.Write(customerOrders);
}
public void Linq16() {
var orderList =
from c in customers
from o in orders
where c.CustomerID == o.CustomerID && o.OrderDate >= new DateTime(1998, 1, 1)
select new { c.CustomerID, o.OrderID, o.OrderDate };
ObjectDumper.Write(orderList);
}
public void Linq18() {
DateTime cutoffDate = new DateTime(1997, 1, 1);
var orderList =
(from c in customers
where c.Region == "WA"
from o in orders
where c.CustomerID == o.CustomerID && o.OrderDate >= cutoffDate
orderby c.CustomerID
select new { c.CustomerID, o.OrderID }).ToList();
ObjectDumper.Write(orderList);
}
public void Linq21() {
var first3WAOrders = (
from c in customers
from o in orders
where c.Region == "WA" && o.CustomerID == c.CustomerID
select new { c.CustomerID, o.OrderID, o.OrderDate })
.ToList();
Debug.WriteLine("First 7 orders in WA:");
foreach (var order in first3WAOrders.Take(7)) {
ObjectDumper.Write(order);
}
}
public void Linq23() {
var allButFirst2Orders = (
from c in customers
from o in orders
where c.Region == "WA" && o.CustomerID == c.CustomerID
orderby c.CustomerID
select new { c.CustomerID, o.OrderID, o.OrderDate })
.ToList();
Debug.WriteLine("All but first 2 orders in WA:");
foreach (var order in allButFirst2Orders) {
ObjectDumper.Write(order);
}
}
public void Linq30() {
var sortedProducts =
from p in products
orderby p.ProductName
select p;
ObjectDumper.Write(sortedProducts);
}
public void Linq42() {
var productList = (from p in products
select new
{
p.ProductID,
p.ProductName,
ProductCategory = (from category in categories
where p.CategoryID == category.CategoryID
select category.CategoryName).First(),
p.UnitPrice,
p.UnitsInStock
}
).ToList();
var orderGroups =
from p in productList
group p by p.ProductCategory into g
select new { Category = g.Key, Products = g };
ObjectDumper.Write(orderGroups, 1);
}
public void Linq43() {
var customerList = (from c in customers
select new
{
c.CustomerID,
c.CompanyName,
/*BUG: Orders is not returning a projection*/
Orders = from o in orders
where o.CustomerID == c.CustomerID
select new
{
o.OrderDate,
LineItems = o.OrderDetails.Count()
}
}).ToArray();
var customerOrderGroups =
from c in customerList
select
new
{
c.CompanyName,
YearGroups =
from o in orders
where o.CustomerID == c.CustomerID
group o by ((DateTime)o.OrderDate).Year into yg
select
new
{
Year = yg.Key,
MonthGroups =
from o in yg
group o by ((DateTime)o.OrderDate).Month into mg
select new { Month = mg.Key, Orders = mg }
}
}
;
ObjectDumper.Write(customerOrderGroups, 3);
}
public void Linq47() {
var productList = (from p in products
select new
{
p.ProductID,
p.ProductName,
ProductCategory = (from category in categories
where p.CategoryID == category.CategoryID
select category.CategoryName).First(),
p.UnitPrice,
p.UnitsInStock
}
).ToList();
var categoryNames = (from p in productList
select p.ProductCategory).Distinct();
Debug.WriteLine("Category names:");
foreach (var n in categoryNames) {
Debug.WriteLine(n);
}
}
public void Linq58() {
Product product12 = (
from p in products
where p.ProductID == 12
select p)
.First();
ObjectDumper.Write(product12);
}
public void Linq62() {
Product product789 = (from product in products
select product).FirstOrDefault(p => p.ProductID == 789);
Debug.WriteLine(string.Format("Product 789 exists: {0}", product789 != null));
}
public void Linq69() {
var productList = (from p in products
select p).ToList();
var productGroups =
from p in productList
group p by p.CategoryID into g
where g.Any(p => p.UnitsInStock == 0)
select new { Category = g.Key, Products = g };
ObjectDumper.Write(productGroups, 1);
}
public void Linq72() {
var productList = (from p in products
select new
{
p.ProductID,
p.ProductName,
ProductCategory = (from category in categories
where p.CategoryID == category.CategoryID
select category.CategoryName).First(),
p.UnitPrice,
p.UnitsInStock
}
).ToList();
var productGroups =
from p in productList
group p by p.ProductCategory into g
where g.All(p => p.UnitsInStock > 0)
select new { Category = g.Key, Products = g };
ObjectDumper.Write(productGroups, 1);
}
public void Linq76() {
var orderCounts =
from c in customers
select new { c.CustomerID, OrderCount = c.Orders.Count() };
ObjectDumper.Write(orderCounts.ToList());
}
public void Linq77() {
var productList = (from p in products
select p).ToList();
var productGroups =
from p in productList
group p by p.CategoryID into g
select new { Category = g.Key, ProductCount = g.Count() };
ObjectDumper.Write(productGroups, 1);
}
public void Linq80() {
var productList = from p in products
select new
{
ProductCategory = (from c in categories
where p.CategoryID == c.CategoryID
select c.CategoryName).First(),
p.UnitsInStock
};
var categoryList =
from p in productList
group p by p.ProductCategory into g
select new
{
Category = g.Key,
TotalUnitsInStock = g.Sum(p => p.UnitsInStock)
};
ObjectDumper.Write(categoryList);
}
public void Linq83() {
var productList = from p in products
select new
{
ProductCategory = (from c in categories
where p.CategoryID == c.CategoryID
select c.CategoryName).First(),
p.UnitPrice
};
var categoryList =
from p in productList
group p by p.ProductCategory into g
select new
{
Category = g.Key,
TotalUnitsInStock = g.Min(p => p.UnitPrice)
};
ObjectDumper.Write(categoryList);
}
public void Linq84() {
var productList = from p in products
select new
{
p.ProductID,
p.ProductName,
ProductCategory = (from c in categories
where p.CategoryID == c.CategoryID
select c.CategoryName).First(),
p.UnitPrice,
p.UnitsInStock
};
var categoryList =
from p in productList
group p by p.ProductCategory into g
select new
{
Category = g.Key,
CheapestProducts = g.Where(a => a.UnitPrice == g.Min(p => p.UnitPrice))
};
ObjectDumper.Write(categoryList, 1);
}
public void Linq91() {
var productList = from p in products
select new
{
ProductCategory = (from c in categories
where p.CategoryID == c.CategoryID
select c.CategoryName).First(),
p.UnitPrice
};
var categoryList =
from p in productList
group p by p.ProductCategory into g
select new
{
ProductCategory = g.Key,
AveragePrice = g.Average(p => p.UnitPrice)
};
ObjectDumper.Write(categoryList);
}
public void Linq95() {
var customerList = (from c in customers
select c).ToList();
var productList = (from p in products
select p).ToList();
var customerNames =
from c in customerList
select c.CompanyName;
var productNames =
from p in productList
select p.ProductName;
var allNames = customerNames.Concat(productNames);
Debug.WriteLine("Customer and product names:");
foreach (var n in allNames) {
Debug.WriteLine(n);
}
}
}
}
Submit an article or tip