//+Eager loading - zapytanie o jeden typ tabeli ładuje od razu także powiązaną tabelę jako część zapytania
//+Navigation Property
public static void PrintCategoriesAndProductsEagerLoadingM(ProdContext db)
{
var categories = db.Categories
.Include(c => c.Products) //opcjonalnie po nazwie encji Include("Products")
.ToList();
foreach (var record in categories)
{
Console.WriteLine("Category Name: {0}", record.Name);
foreach (var p in record.Products)
{
Console.WriteLine("Product: {0}", p.Name);
}
}
}
//Eager loading + Navigation Property
public static void PrintOrderWithDetailsEL(ProdContext db)
{
var orders = db.Orders.Include(o => o.Product).Include(o => o.Customer);
foreach (var order in orders)
{
Console.WriteLine("Order nr: {0}, Date: {1}, Quantity {2}, Status {3}",
order.OrderId, order.Date, order.Quantity, order.Status);
Product product = order.Product;
Console.WriteLine("Product details: {0}, {1}", product.ProductId, product.Name);
Customer customer = order.Customer;
Console.WriteLine("Customer details: {0}, {1}\n", customer.CompanyName, customer.Description);
}
}
//METODY DOSTĘPOWE
//M w końcówce oznacza, że jest to Method Syntax, zaś Q - Query Syntax
//Navigation Property - z tabeli Category, po zależnościach dochodzimy do Produktów
public static void PrintCategoriesAndProductsQ(ProdContext db)
{
var query = from b in db.Categories
orderby b.Name descending
select b;
foreach (var categoryName in query)
{
Console.WriteLine("Category name: {0}", categoryName.Name);
foreach (Product product in categoryName.Products)
{
Console.WriteLine("description: {0}", product.Name);
}
}
}
public static void PrintCategoriesAndProductsJoinQ(ProdContext db)
{
var query = from ca in db.Categories
join pr in db.Products
on ca.CategoryId equals pr.CategoryId
//orderby c.Name
select new
{
c = ca,
p = pr
};
foreach (var record in query)
{
Console.WriteLine("Category Name: " + record.c.Name + " Product Name: " + record.p.Name);
}
}
//Join
public static void PrintCategoriesAndProductsJoinM(ProdContext db)
{
var query = db.Categories
.Join(db.Products,
product => product.CategoryId,
category => category.CategoryId,
(category, product) =>
new {
c = category,
p = product
});
foreach (var record in query)
{
Console.WriteLine("Category Name " + record.c, "Product Name " + record.p);
}
}
//Agregacja - Count
public static void CountProductsForCategoryQ(ProdContext db)
{
var query = from c in db.Categories
orderby c.Name descending
select new
{
CategoryID = c.CategoryId,
CategoryName = c.Name,
ProductsQuantity = c.Products.Count()
};
foreach (var c in query)
{
Console.WriteLine("Category Name: {0} \t ProductsQuantity: {1}",
c.CategoryName,
c.ProductsQuantity);
}
}
//dodatkowe metody
//Lazy loading - jest domyślne
//Navigation Property - z tabeli Orders, po zależnościach dochodzimy do Product i Customer, do szczegółów
//Query syntax
public static void PrintOrderWithDetails(ProdContext db)
{
db.Configuration.LazyLoadingEnabled = true;
var query = from o in db.Orders
orderby o.OrderId descending
select o;
foreach (var order in query)
{
Console.WriteLine("Order nr: {0}, Date: {1}, Quantity {2}, Status {3}",
order.OrderId, order.Date, order.Quantity, order.Status);
Product product = order.Product;
Console.WriteLine("Product details: {0}, {1} {2}", product.ProductId,
getCategoryName(db, product.CategoryId), product.Name);
Customer customer = order.Customer;
Console.WriteLine("Customer details: {0}, {1}\n", customer.CompanyName, customer.Description);
}
}
static void Main(string[] args)
{
using (var db = new ProdContext())
{
var wasCreated = db.Database.CreateIfNotExists();
db.Database.Connection.Open();
//Create and save a new Category
//Console.Write("Enter a name for a new Category: ");
//var name = Console.ReadLine();
//zaistancjonuj kategorię o podanej nazwie
//var category = new Category { Name = name };
//dodanie zaistancjonowanego obiektu do kontekstowej kolekcji kategorii
//db.Categories.Add(category);
//var product = new Product { Name = "pomarańczowa", UnitsInStock = 10, CategoryId = 2, UnitPrice = 10 };
//var customer = new Customer { CompanyName = "Lewiatan", Description = "little greengrocer's" };
//var order = new Order { Customer = customer, Product = product, Quantity = 10, Status = 'N' };
//db.Products.Add(product);
//db.Customers.Add(customer);
//db.Orders.Add(order);
//zapisanie zmian w kontekście
//db.SaveChanges();
//List<string> c=db.Categories.Where(a => a.Name.Length >0).Select(a => a.Name).ToList();
//V. method based syntax
var categories = db.Categories
.Select(c => c.Name).ToList();
Console.WriteLine("Categories Names:");
foreach (String c in categories)
{
Console.WriteLine(c);
}
//Display all Categories from the database
var query = from b in db.Categories
orderby b.Name descending
select b;
Console.WriteLine("All categories in the database (method syntax):");
foreach (var item in query)
{
Console.WriteLine(item.Name);
}
Console.WriteLine("Products quantity for each category:");
Methods.CountProductsForCategoryQ(db);
Methods.PrintOrderWithDetails(db);
Methods.PrintOrderWithDetailsEL(db);
MainForm f = new MainForm();
f.ShowDialog();
}
}