public static List<Customer> FindCustomersWithOrdersInYearAndCountryWithDbCommand(int year, string countryName)
{
var dbContext = new NorthwindEntities();
string selectQuery =
"SELECT DISTINCT " +
"c.CustomerID AS[CustomerID]," +
"c.CompanyName AS[CompanyName]," +
"c.ContactName AS[ContactName]," +
"c.ContactTitle AS[ContactTitle]," +
"c.Address AS[Address]," +
"c.City AS[City]," +
"c.Region AS[Region]," +
"c.PostalCode AS[PostalCode]," +
"c.Country AS[Country]," +
"c.Phone AS[Phone]," +
"c.Fax AS[Fax]" +
"FROM Customers AS c " +
"JOIN Orders AS o " +
" ON c.CustomerID = o.CustomerID " +
"WHERE c.Country = 'Canada' AND o.OrderDate BETWEEN '1997-01-01' AND '1997-12-31'";
var customers = dbContext.Database.SqlQuery<Customer>(selectQuery).ToList();
return customers;
}
public static List<Customer> FindCustomersWithOrdersInYearAndCountry(int year, string countryName)
{
var dbContex = new NorthwindEntities();
var customers = dbContex.Customers
.Where(c => c.Orders.Where(o => o.OrderDate.Value.Year == year && o.ShipCountry == countryName)
.Any(o => o.CustomerID == c.CustomerID))
.Select(c => c)
.ToList();
return customers;
}
public static void DeleteCustomer(string custumerId)
{
var dbContex = new NorthwindEntities();
using (dbContex)
{
var customer = dbContex.Customers
.Where(c => c.CustomerID == custumerId)
.FirstOrDefault();
if (customer == null)
{
return;
}
dbContex.Customers.Remove(customer);
dbContex.SaveChanges();
}
}
public static void ChangeCustomerContactName(string customerId, string newName)
{
var dbContex = new NorthwindEntities();
using (dbContex)
{
var customer = dbContex.Customers
.FirstOrDefault(c => c.CustomerID == customerId);
if (customer == null)
{
return;
}
customer.ContactName = newName;
dbContex.SaveChanges();
}
}
public static List<Order> FindSales(string region, DateTime startDate, DateTime endDate)
{
var dbContex = new NorthwindEntities();
var orders = dbContex.Orders
.Where(o => o.ShipRegion.ToLower() == region.ToLower()
&& startDate <= o.OrderDate && o.OrderDate <= endDate)
.ToList();
return orders;
}
public static void InsertCustomer(Customer custumer)
{
var dbContex = new NorthwindEntities();
using (dbContex)
{
dbContex.Customers.Add(custumer);
dbContex.SaveChanges();
}
}