public static void DeleteCustomerRecored(string customerId)
{
using (var northwindEntities = new NorthwindEntities())
{
var customer = CustomersRepository.GetCustomerById(northwindEntities, customerId);
northwindEntities.Customers.Remove(customer);
northwindEntities.SaveChanges();
}
}
// 8.
// By inheriting the Employee entity class create a class which allows employees to access their corresponding
// territories as property of type EntitySet<T>.
public static void ShowEmployeeTerritories()
{
using (var db = new NorthwindEntities())
{
var employee = db.Employees.FirstOrDefault();
foreach (var correspondingTerritory in employee.CorrespondingTerritories)
{
Console.WriteLine(correspondingTerritory.TerritoryDescription);
}
}
}
// 5. Write a method that finds all the sales by specified region and period (start / end dates).
public static IEnumerable<Order> FindAllSales(string region, DateTime startDate, DateTime endDate)
{
using (var northwindEntities = new NorthwindEntities())
{
//return (from order in northwindEntities.Orders
// where order.ShipRegion == region && startDate <= order.ShippedDate && order.ShippedDate <= endDate
// select order).ToList();
return
northwindEntities.Orders.Where(
o => o.ShipRegion == region && startDate <= o.ShippedDate && o.ShippedDate <= endDate).ToList();
}
}
public static void AddCustomerRecord(string customerId, string companyName)
{
using (var northwindEntities = new NorthwindEntities())
{
northwindEntities.Customers.Add(new Customer
{
CustomerID = customerId,
CompanyName = companyName
});
northwindEntities.SaveChanges();
}
}
// 7.
// Try to open two different data contexts and perform concurrent changes on the same records.
// What will happen at SaveChanges()?
// How to deal with it?
public static void DifferentContexts()
{
using (NorthwindEntities db1 = new NorthwindEntities(), db2 = new NorthwindEntities())
{
var customer1 = db1.Customers.FirstOrDefault();
Console.WriteLine(customer1.ContactName);
customer1.ContactName = "Ivan";
var customer2 = db2.Customers.FirstOrDefault();
Console.WriteLine(customer2.ContactName);
customer2.ContactName = "Pesho";
db1.SaveChanges();
db2.SaveChanges();
}
}
//2. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
// To the examiner: Check the commented code below! I solved this problem in 3 different ways.
public static IEnumerable<Customer> GetCustomersWithOrdersIn1997ToCanada()
{
using (var northwindEntities = new NorthwindEntities())
{
//Console.WriteLine(from c in northwindEntities.Customers
// join o in northwindEntities.Orders on c.CustomerID equals o.CustomerID
// where o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997
// orderby c.CompanyName
// select c);
//return (from c in northwindEntities.Customers
// join o in northwindEntities.Orders on c.CustomerID equals o.CustomerID
// where o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997
// orderby c.CompanyName
// select c).ToList();
//Console.WriteLine(
// northwindEntities.Orders
// .Where(o => o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997)
// .Join(
// northwindEntities.Customers,
// o => o.CustomerID,
// c => c.CustomerID,
// (o, c) => c)
// .OrderBy(c => c.CompanyName));
//return
// northwindEntities.Orders
// .Where(o => o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997)
// .Join(
// northwindEntities.Customers,
// o => o.CustomerID,
// c => c.CustomerID,
// (o, c) => c)
// .OrderBy(c => c.CompanyName)
// .ToList();
//Console.WriteLine(
// northwindEntities.Customers
// .Select(c => c)
// .Where(c => c.Orders.Any(o => o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997)));
//return
// northwindEntities.Customers
// .Select(c => c)
// .Where(c => c.Orders.Any(o => o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997))
// .ToList();
// 4. Implement previous by using native SQL query and executing it through the DbContext.
string query =
@"SELECT c.CustomerID,
c.CompanyName,
c.ContactName,
c.ContactTitle,
c.Address,
c.City,
c.Region,
c.PostalCode,
c.Country,
c.Phone,
c.Fax
FROM Customers c
WHERE EXISTS (SELECT * FROM Orders o
WHERE c.CustomerID = o.CustomerID AND o.ShipCountry = 'Canada' AND YEAR(o.OrderDate) = 1997)";
return northwindEntities.Database.SqlQuery<Customer>(query).ToList();
}
}
private static Customer GetCustomerById(NorthwindEntities northwindEntities, string customerId)
{
return northwindEntities.Customers.FirstOrDefault(c => c.CustomerID == customerId);
}
public static void UpdateCustomerRecord(string customerId, string contactName)
{
using (var northwindEntities = new NorthwindEntities())
{
var customer = CustomersRepository.GetCustomerById(northwindEntities, customerId);
customer.ContactName = contactName;
northwindEntities.SaveChanges();
}
}