private static void NaiveReportingBetter()
{
using (var context = new WideWorldImportersEntities())
{
List <string> results = new List <string>();
var invoiceLines = context.InvoiceLines
.Include(il => il.Invoice)
.Include(il => il.Invoice.Customer.City)
.Include(il => il.Invoice.Customer.City.StateProvince)
.Include(il => il.Invoice.Customer.City.StateProvince.Country)
.Include(il => il.PackageType)
.Take(50);
foreach (var line in invoiceLines)
{
var countryName = line.Invoice.Customer.City.StateProvince.Country.CountryName;
var stateProvinceName = line.Invoice.Customer.City.StateProvince.StateProvinceName;
var cityName = line.Invoice.Customer.City.CityName;
var packageTypeName = line.PackageType.PackageTypeName;
var quantity = line.Quantity;
var deliveryInstructions = line.Invoice.DeliveryInstructions;
results.Add($"{countryName}-{stateProvinceName}-{cityName}, {packageTypeName}: {quantity}, {deliveryInstructions}");
}
foreach (var result in results)
{
Console.WriteLine(result);
}
}
}
private static void ComplexQuery()
{
using (var context = new WideWorldImportersEntities())
{
var result =
context.OrderLines
.GroupBy(line => line.Order.Customer.City.StateProvince.StateProvinceName)
.Select(group => new { SpName = group.Key, Total = group.Sum(line => line.Quantity) })
.Join(context.OrderLines.GroupBy(line => new
{
Color = line.StockItem.Color,
SpName = line.Order.Customer.City.StateProvince.StateProvinceName
}), outer => outer.SpName, inner => inner.Key.SpName,
(outer, inner) => new
{
SpName = outer.SpName,
TotalInSp = outer.Total,
Quantity = inner.Sum(line => line.Quantity),
Color = inner.Key.Color,
Percentage = (inner.Sum(line => line.Quantity) + 0.0) / outer.Total
})
.OrderBy(val => val.SpName)
.ThenByDescending(val => val.Quantity);
foreach (var item in result.ToList())
{
Console.WriteLine($"{item.SpName}, {item.Color?.ColorName ?? "No Color"}: {item.Quantity} ({item.Percentage * 100:#,0.00}%)");
}
}
}