public static IEnumerable<Customer> GetAllCustomersWithOrdersIn1997ToCanadaWithQuery(NorthwindEntities db)
{
// Does not show distinct rows!
string query = "select * from Customers c, Orders o " +
"where o.CustomerID = c.CustomerID and datediff(year, o.OrderDate, '1997-01-01') = 0 and o.ShipCountry = 'Canada' ";
var customers = db.Database.SqlQuery<Customer>(query);
return customers.ToList();
}
public static IEnumerable<Customer> GetAllCustomersWithOrdersIn1997ToCanada(NorthwindEntities db)
{
// Shows only distinct rows!
var customers = from c in db.Customers
where (from o in c.Orders
where o.ShipCountry == "Canada"
select o).Count() != 0
select c;
return customers.ToList();
}
public static void ModifyCustomer(NorthwindEntities db, string customerId, Action<Customer> operation)
{
Console.WriteLine("MODIFYING CUSTOMER...");
Customer customer = db.Customers.FirstOrDefault(x => x.CustomerID.ToLower() == customerId.ToLower());
if (customer != null)
{
operation.Invoke(customer);
db.SaveChanges();
}
Console.WriteLine("DONE");
}
public static void Main()
{
using (var db = new NorthwindEntities())
{
// MODIFY CUSTOMER
//NorthwindDao.ModifyCustomer(db: db, customerId: "alfki", operation: (customer => customer.City = "Sofia"));
// INSERT CUSTOMER
//NorthwindDao.InsertCustomer(new Customer
//{
// CustomerID = "PESHO",
// CompanyName = "Pesho Inc.",
// ContactName = "Goshko",
// ContactTitle = "CEO",
// Address = "Studentski grad",
// City = "Sofia",
// PostalCode = "1000",
// Country = "Bulgaria"
//});
// DELETE CUSTOMER
//NorthwindDao.DeleteCustomer(db: db, customerId: "alfki");
// GET ALL CUSTOMERS WITH ORDERS IN 1997 TO CANADA
//var customers = NorthwindDao.GetAllCustomersWithOrdersIn1997ToCanada(db);
// GET ALL CUSTOMERS WITH ORDERS IN 1997 TO CANADA USING NATIVE QUERY
//var customers = NorthwindDao.GetAllCustomersWithOrdersIn1997ToCanadaWithQuery(db);
//foreach (var item in customers)
//{
// Console.WriteLine("{0} - {1}", item.CustomerID, item.ContactName);
//}
//db.SaveChanges();
// GET ALL ORDERS FROM A REGION AND IN A TIME SPAN
var orders = NorthwindDao.GetAllSalesBySpecifiedRegionAndPeriod(db, "RJ", new DateTime(1995, 01, 01), new DateTime(2000, 01, 01));
foreach (var item in orders)
{
Console.WriteLine("{0} - {1}", item.OrderID, item.CustomerID);
}
// USE THE NEW EMPLOYEE PROPERTY
//var territories = db.Employees.Where(e => e.EmployeeID == 1).FirstOrDefault().TerritoriesEntities.ToList();
//foreach (var item in territories)
//{
// Console.WriteLine(item.TerritoryDescription);
//}
}
}
public void UpdateCustomerShouldChangeDbRecord()
{
var northwindDbMock = new NorthwindEntities();
Mock.Arrange(() => northwindDbMock.Customers).ReturnsCollection(this.db);
var expected = "Ivan Borimechkata";
NorthwindDao.ModifyCustomer(northwindDbMock, "pen",
c => c.ContactName = expected);
var actual = this.db.Where(x => x.CustomerID == "PEN")
.FirstOrDefault()
.ContactName;
Assert.AreEqual(expected, actual);
}
public static void InsertCustomer(NorthwindEntities db, Customer customer)
{
Console.WriteLine("INSERTING CUSTOMER...");
var allCustomers = db.Customers;
foreach (var item in allCustomers)
{
if (item.CustomerID.ToLower() == customer.CustomerID.ToLower())
{
return;
}
}
db.Customers.Add(customer);
db.SaveChanges();
Console.WriteLine("DONE");
}
public void AddCustomerShouldAddDbRecord()
{
var customer = new Customer()
{
CustomerID = "ZXC",
ContactName = "Asd Qwerty"
};
var northwindDbMock = new NorthwindEntities();
Mock.Arrange(() => northwindDbMock.Customers).ReturnsCollection(this.db);
Mock.Arrange(() =>
northwindDbMock.Customers
.Add(customer))
.DoInstead((Customer c) => { this.db.Add(c); });
NorthwindDao.InsertCustomer(northwindDbMock, customer);
Assert.AreEqual(expected: 3, actual: this.db.Count);
}
public static void DeleteCustomer(NorthwindEntities db, string customerId)
{
Console.WriteLine("DELETING CUSTOMER...");
var customer = db.Customers.FirstOrDefault(x => x.CustomerID.ToLower() == customerId.ToLower());
if (customer != null)
{
var orders = db.Orders.Where(x => x.CustomerID.ToLower() == customerId.ToLower()).ToList();
foreach (var order in orders)
{
order.CustomerID = null;
}
db.SaveChanges();
db.Customers.Remove(customer);
db.SaveChanges();
}
Console.WriteLine("DONE");
}
public static IEnumerable<Order> GetAllSalesBySpecifiedRegionAndPeriod(NorthwindEntities db, string region, DateTime start, DateTime end)
{
var result = db.Orders.Where(o => o.OrderDate <= end && o.OrderDate >= start && o.ShipRegion == region).ToList();
return result;
}
