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