//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());
}
}
