/// <summary>
/// The main.
/// </summary>
/// <param name="args">
/// The args.
/// </param>
private static void Main(string[] args)
{
// 3. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
Console.WriteLine("Customers who have orders made in 1997 and shipped to Canada.");
Console.WriteLine("--------------------------------------------------------------");
Console.WriteLine("Entity request:");
Console.WriteLine("--------------------------------------------------------------");
using (var db = new NorthwindEntities())
{
//var customers = (from c in db.Customers
// join o in db.Orders on c.CustomerID equals o.CustomerID
// where o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada"
// select c).ToList();
var customers = db.Customers.Where(c => c.Orders.Any(x => x.OrderDate != null && (x.OrderDate.Value.Year == 1997 && x.ShipCountry == "Canada"))).ToList();
foreach (var customer in customers)
{
Console.WriteLine(customer.ContactName);
}
}
// 4. Implement previous by using native SQL query and executing it through the DbContext.
Console.WriteLine("--------------------------------------------------------------");
Console.WriteLine("Sql request:");
Console.WriteLine("--------------------------------------------------------------");
using (var db = new NorthwindEntities())
{
var customers = db.Database.SqlQuery<string>(SqlQuery).ToList();
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
// 5. Write a method that finds all the sales by specified region and period (start / end dates).
Console.WriteLine("--------------------------------------------------------------");
Console.WriteLine("Sales by specified region and period (start / end dates).");
Console.WriteLine("--------------------------------------------------------------");
Console.WriteLine("Enter region:");
var region = Console.ReadLine();
Console.WriteLine("Enter start date:");
var startDate = DateTime.Parse(Console.ReadLine());
Console.WriteLine("Enter end date:");
var endDate = DateTime.Parse(Console.ReadLine());
using (var db = new NorthwindEntities())
{
var orders =
db.Orders.Where(o => startDate >= o.OrderDate && o.OrderDate <= endDate && o.ShipRegion == region)
.Select(x => new { Name = x.ShipName })
.ToList();
foreach (var order in orders)
{
Console.WriteLine(order);
}
}
}
/// <summary>
/// The main.
/// </summary>
/// <param name="args">
/// The args.
/// </param>
private static void Main(string[] args)
{
// 3. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
Console.WriteLine("Customers who have orders made in 1997 and shipped to Canada.");
Console.WriteLine("--------------------------------------------------------------");
Console.WriteLine("Entity request:");
Console.WriteLine("--------------------------------------------------------------");
using (var db = new NorthwindEntities())
{
//var customers = (from c in db.Customers
// join o in db.Orders on c.CustomerID equals o.CustomerID
// where o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada"
// select c).ToList();
var customers = db.Customers.Where(c => c.Orders.Any(x => x.OrderDate != null && (x.OrderDate.Value.Year == 1997 && x.ShipCountry == "Canada"))).ToList();
foreach (var customer in customers)
{
Console.WriteLine(customer.ContactName);
}
}
// 4. Implement previous by using native SQL query and executing it through the DbContext.
Console.WriteLine("--------------------------------------------------------------");
Console.WriteLine("Sql request:");
Console.WriteLine("--------------------------------------------------------------");
using (var db = new NorthwindEntities())
{
var customers = db.Database.SqlQuery <string>(SqlQuery).ToList();
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
// 5. Write a method that finds all the sales by specified region and period (start / end dates).
Console.WriteLine("--------------------------------------------------------------");
Console.WriteLine("Sales by specified region and period (start / end dates).");
Console.WriteLine("--------------------------------------------------------------");
Console.WriteLine("Enter region:");
var region = Console.ReadLine();
Console.WriteLine("Enter start date:");
var startDate = DateTime.Parse(Console.ReadLine());
Console.WriteLine("Enter end date:");
var endDate = DateTime.Parse(Console.ReadLine());
using (var db = new NorthwindEntities())
{
var orders =
db.Orders.Where(o => startDate >= o.OrderDate && o.OrderDate <= endDate && o.ShipRegion == region)
.Select(x => new { Name = x.ShipName })
.ToList();
foreach (var order in orders)
{
Console.WriteLine(order);
}
}
}
