static void Main(string[] args)
{
#region Explaination
// Read Northwind using Entity core 2.1.1
// Basic LINQ
// More advanced LINQ with lambda
// Nuget : entityframeworkcore 2.1.1/ entityframeworkcore.sqlserver 2.1.1/ entityframeworkcore.design 2.1.1
// Command - install-package microsoft.entityframeworkcore - v 2.1.1
#endregion
List <Customer> customers = new List <Customer>();
List <Customer> selectedDatabaseCustomers;
List <Product> productsList = new List <Product>();
List <Category> categoriesList = new List <Category>();
using (var db = new Northwind())
{
customers = db.Customers.ToList();
// Simple Linq from local collection
// Whole dataset is returned (more data)
// IENUMERABLE ARRAY
var selectedLocalCustomers =
from customer in customers
select customer;
// Simple query over database directly
// Only return actual data we need
// Lazy loading - query not actually executed
// Data not actually arrived yet
// Force data by pushing ToList
selectedDatabaseCustomers =
(from customer in db.Customers
where customer.City == "London" || customer.City == "Berlin"
orderby customer.CompanyName
select customer).ToList();
printCustomers(selectedDatabaseCustomers);
// Create custom object output
var selectedCustomCustomers =
(from customer in db.Customers
select new {
Name = customer.ContactName,
Location = customer.City + " " + customer.Country
}).Take(10).ToList();
foreach (var c in selectedCustomCustomers)
{
Console.WriteLine($"{c.Name,-20}{c.Location}");
}
// Grouping
// Group and list all customers by city
// City... count(Customer)
var selectedGroupCustomers =
(from customer in db.Customers
group customer by customer.City into Cities
orderby Cities.Key
select new
{
City = Cities.Key,
Count = Cities.Count()
});
foreach (var c in selectedGroupCustomers.ToList())
{
Console.WriteLine($"{c.City,-15}{c.Count}");
}
// Join
// Product with categoryID => category
var products =
(from product in db.Products
join category in db.Categories
on product.CategoryID equals category.CategoryID
select new
{
ID = product.ProductID,
Name = product.ProductName,
Category = category.CategoryName
}).ToList();
products.ForEach(p => Console.WriteLine($"{p.ID,-10}" + $"{p.Name,-30}" + $"{p.Category}"));
// Smarter way to join
Console.WriteLine("\n\nDisplay List using smarter \n" + "'dot' Notation to join tables\n");
// Read in products and categories
productsList = db.Products.ToList();
categoriesList = db.Categories.ToList();
productsList.ForEach(p => Console.WriteLine($"{p.ProductID,-15}{p.ProductName,-30}{p.Category.CategoryName}"));
// List categories with count of products and sub-list of product names
Console.WriteLine("\n\nList categories with count of products and sub-list of product names\n");
categoriesList.ForEach(c =>
{
Console.WriteLine($"{c.CategoryID,-5}{c.CategoryName,-15} has {c.Products.Count} products");
// Loop within loop
foreach (var p in c.Products)
{
Console.WriteLine($"\t\t\t\t{p.ProductID,-5}{p.ProductName}");
}
});
// Linq lambda notation
Console.WriteLine("\n\nLINQ Lambda notation");
customers = db.Customers.ToList();
Console.WriteLine($"Local count is {customers.Count}");
Console.WriteLine($"Database count is {db.Customers.Count()}");
// Select....Distinct
Console.WriteLine("\n\nList of Cities : Select ... Distinct ... OrderBy\n");
Console.WriteLine("Using SELECT to select one column\n");
var cityList = db.Customers.Select(c => c.City).Distinct().OrderBy(c => c).ToList();
cityList.ForEach(city => Console.WriteLine(city));
// Contaims i.e. same like SQL Like
Console.WriteLine("\n\nContaims i.e. same like SQL Like\n");
var cityListFiltered =
db.Customers
.Select(c => c.City)
.Where(city => city.Contains("o"))
.Distinct()
.OrderBy(c => c)
.ToList();
cityListFiltered.ForEach(city => Console.WriteLine(city));
}