//static string CustomersPath = "customers.csv"; static void Main(string[] args) { using (var db = new NorthwindDbContext()) { customers = db.Customers.ToList(); } //PrintCustomers(customers); // Process.Start("EXCEL", CustomersPath); // LINQ BASIC SYNTAX using (var db = new NorthwindDbContext()) { // LINQ BASIC (QUERY) SYNTAX // NOTE : WOULD HAVE TO CAST THESE OBJECTS TO LIST OR ARRAY TO USE THEM var customers01 = from customer in db.Customers select customer; foreach (var customer in customers01.ToList()) { // ... iterate } var customers02 = from c in db.Customers where c.City == "LONDON" || c.City == "BERLIN" orderby c.ContactName descending select c; // print //PrintCustomers(customers02.ToList()); // creating custom output object - LITERAL var customList = from c in db.Customers select new { Name = c.ContactName, City = c.City, Country = c.Country }; // custom print // Console.WriteLine($"Customer Detail\n"); // Console.WriteLine($"{"Name",-25}{"City",-20}{"Country"}"); // customList.ToList().ForEach(item => Console.WriteLine($"{item.Name,-25}" + // $"{item.City,-20}{item.Country}")); var customList2 = from c in db.Customers select new customObject() { Name = c.ContactName, City = c.City, Country = c.Country }; //Console.WriteLine($"\nCustomer Detail\n"); //Console.WriteLine($"{"Name",-25}{"City",-20}{"Country"}"); //customList2.ToList().ForEach(item => Console.WriteLine($"{item.Name,-25}" + // $"{item.City,-20}{item.Country}")); // SQL has AGGREGATION ie SUM, COUNT, AVERAGE, MAX AND MIN // QUERY BY CITY ==> COUNT PER CITY var customerCountByCity = from cust in db.Customers group cust by cust.City into Cities where Cities.Count() > 1 orderby Cities.Count() descending select new { City = Cities.Key, Count = Cities.Count() }; //Console.WriteLine($"\n\nLIST OF CUSTOMER COUNT BY CITY\n"); foreach (var item in customerCountByCity.ToList()) { //Console.WriteLine($"{item.City,-20}{item.Count}"); } var customerByCity = db.Customers.ToList().OrderBy(c => c.Country).ThenBy(c => c.City); var customersGroupedByCity = db.Customers.ToList() .GroupBy(c => c.City) .Where(item => item.Count() > 0) .OrderByDescending(item => item.Count()) .ThenByDescending(item => item.Key); //Console.WriteLine($"\n\nLIST OF CUSTOMER GROUPED BY CITY\n"); foreach (var item in customersGroupedByCity.ToList()) { //Console.WriteLine($"{item.Key,-20}{item.Count()}"); } // JOINING TABLES // PRODUCTS WILL HAVE A CATEGORY AND LINK VIA CATEGORY ID products = (from p in db.Products select p).ToList(); // In order to add CATEGORY NAME to output, first have to pull // in Categories into local database store (cache) // NOTE : In LINQ by default we have 'LAZY LOADING' which means QUERY NOT ACTUALLY RUN // AND BROUGHT INTO MEMORY UNTIL WE ACTUALLY NEED THE DATA. // var categories = db.Categories; ==> LAZY LOADING (NOT RUN!) // .ToList(); ==> FORCED LOADING (QU categories = (from c in db.Categories select c).ToList(); // print PrintProducts(products.ToList()); } }
static void Main(string[] args) { using (var db = new NorthwindDbContext()) { customers = db.Customers.ToList(); PrintCustomers(customers); // Process.Start("EXCEL", CustomersPath); //opens excel } //Linq basic syntax using (var db = new NorthwindDbContext()) { //linq basic query syntax //would have to cast these objects to list or array to use them var customers01 = from customer in db.Customers select customer; var customers02 = from customer in db.Customers where customer.City == "London" || customer.City == "Berlin" orderby customer.ContactName descending select customer; //print PrintCustomers(customers02.ToList()); Console.WriteLine("Customer details\n"); Console.WriteLine($"{"Name",-30} {"City",-30} {"Country"}\n"); //create custom output object var customList = from customer in db.Customers select new { Name = customer.ContactName, City = customer.City, Country = customer.Country }; //custom print customList.ToList().ForEach(item => Console.WriteLine($"{item.Name,-30} {item.City,-30} {item.Country}")); Console.WriteLine("Customer details\n"); Console.WriteLine($"{"Name",-30} {"City",-30} {"Country"}\n"); //output from constructor var customList2 = from customer in db.Customers select new CustomObject() { Name = customer.ContactName, City = customer.CompanyName, Country = customer.Country }; customList2.ToList().ForEach(item => Console.WriteLine($"{item.Name,-30} {item.City,-30} {item.Country}")); //grouping items //SQL has aggregation (Sum, Count, Max, Min etc) //Query via city ==> count by city var customerCountByCity = from customer in db.Customers group customer by customer.City into cities //groups cities where cities.Count() > 1 //cities with more than 1 orderby cities.Count() descending //cities put in descending order (for amount of city) select new { City = cities.Key, Count = cities.Count() }; Console.WriteLine($"\n\nList of customer count by city\n"); foreach (var item in customerCountByCity.ToList()) { Console.WriteLine($"{item.City,-20}{item.Count}"); } var customerByCity = db.Customers.ToList().OrderBy(c => c.Country).ThenBy(c => c.City); var customersGroupedByCity = db.Customers.ToList() .GroupBy(c => c.City) .Where(item => item.Count() > 1) .OrderByDescending(item => item.Count()) .ThenByDescending(item => item.Key); Console.WriteLine($"\n\nList of customer count by city\n"); foreach (var item in customerCountByCity.ToList()) { Console.WriteLine($"{item.City,-20}{item.Count}"); } //Joining Tables //products will have a category and link via category ID products = (from p in db.Products select p).ToList(); //In order to add category name to output, first have to pull //in categories into local database store (cache) //in LINQ we have lazy loading - query not actually run //and bought into memory until we use/need the data //var categories = db.Categories => lazy loading //.ToList() forced loading categories = (from c in db.Categories select c).ToList(); //print PrintProducts(products.ToList()); } }