public static IEnumerable<Customer> GetCustomersByShippmentDateAndCountryNative(
string country,
DateTime datetime)
{
using (var dbContext = new NorthwindEntities())
{
string quotedCountry = "'" + country + "'";
string query =
"SELECT c.CustomerID " +
"FROM Customers c " +
"JOIN Orders o " +
"ON c.CustomerID = o.CustomerID " +
"WHERE o.ShipCountry = {0} " +
"AND " +
"o.OrderDate BETWEEN '01/01/{1}' AND '12/31/{1}'";
string formatedQuery = string.Format(
query,
quotedCountry,
datetime.Year);
// TODO: There must be a way smarter way for doing this!
var resultCustomersIds = dbContext.Database.SqlQuery<string>(formatedQuery);
foreach (var customerId in resultCustomersIds)
{
var currentCustomer = dbContext.Customers.Find(customerId);
yield return currentCustomer;
}
}
}
// Added a connection string in App.config
private static void CreateNorthwindTwin()
{
using (var dbContext = new NorthwindEntities())
{
dbContext.Database.CreateIfNotExists();
}
}
public static void AddCustomer(Customer customer)
{
using (var dbContext = new NorthwindEntities())
{
dbContext.Customers.Add(customer);
dbContext.SaveChanges();
}
}
public static void DeleteCustomer(string customerId)
{
using (var dbContext = new NorthwindEntities())
{
var customerToRemove = dbContext.Customers.FirstOrDefault(c => c.CustomerID == customerId);
dbContext.Customers.Remove(customerToRemove);
dbContext.SaveChanges();
}
}
public static IEnumerable<object> FindAllWithOrdersByShippedDateAndShipCountry(DateTime shippedDate, string shipCountry)
{
string shipCountryLowered = shipCountry.ToLower();
using (var dbContext = new NorthwindEntities())
{
return dbContext.Customers
.Where(c => c.Orders.Any(o => (o.ShippedDate.Value.Year == shippedDate.Year)
&& (o.ShipCountry.ToLower() == shipCountryLowered)))
.Select(c => new { c.CompanyName, c.ContactName })
.ToList();
}
}
public static void EditCustomerCompanyName(
string customerId,
string newCompany)
{
using (var dbContext = new NorthwindEntities())
{
var customerToEdit = dbContext.Customers.FirstOrDefault(c => c.CustomerID == customerId);
customerToEdit.CompanyName = newCompany;
dbContext.SaveChanges();
}
}
private static IEnumerable<Order> GetSalesByRegionAndDateRange(
DateTime startDate,
DateTime endtDate,
string region)
{
using (var dbContext = new NorthwindEntities())
{
var result =
dbContext.Orders
.Where(o => o.OrderDate >= startDate && o.OrderDate <= endtDate)
.Where(o => o.ShipRegion == region)
.ToList();
return result;
}
}
public static IEnumerable<Customer> GetCustomersByShippmentDateAndCountry(string country, DateTime datetime)
{
using (var dbContext = new NorthwindEntities())
{
var result = dbContext.Customers
.Join(
dbContext.Orders
.Where(o => o.OrderDate.Value.Year == datetime.Year)
.Where(o => o.ShipCountry == country),
c => c.CustomerID,
o => o.CustomerID,
(c, o) => c);
return result.ToList();
}
}
public static void Insert(Customer customer)
{
if (customer == null)
{
throw new ArgumentNullException("Customer cannot be null.");
}
if (string.IsNullOrWhiteSpace(customer.CustomerID))
{
throw new ArgumentException("CustomerID is mandatory.");
}
using (var dbContext = new NorthwindEntities())
{
dbContext.Customers.Add(customer);
dbContext.SaveChanges();
}
}
private static void FindAllOrdersByRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
string regionLowered = region.ToLower();
using (var dbContext = new NorthwindEntities())
{
var foundOrders = dbContext.Orders
.Where(o => (o.ShipRegion.ToLower() == regionLowered)
&& (o.OrderDate > startDate && o.OrderDate < endDate))
.Select(o => new { o.OrderDate, o.ShipRegion })
.ToList();
foreach (var order in foundOrders)
{
Console.WriteLine(order);
}
}
}
private static void FindAllCustomersWithOrdersIn1997ToCanadaUsingSql()
{
string query = @"SELECT DISTINCT(c.CompanyName)
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE o.ShipCountry = 'Canada'
AND DATEPART(yyyy, o.ShippedDate) = '1997'";
using (var dbContext = new NorthwindEntities())
{
var foundCustomers = dbContext.Database.SqlQuery<string>(query).ToList();
foreach (var c in foundCustomers)
{
Console.WriteLine(c);
}
Console.WriteLine();
}
}
public static void Modify(string id, string newContactName)
{
if (string.IsNullOrWhiteSpace(id))
{
throw new ArgumentException("Id is null, empty or contains only whitespaces.");
}
string trimmedId = id.Trim();
using (var dbContext = new NorthwindEntities())
{
var customer = dbContext.Customers
.Where(c => c.CustomerID.Equals(trimmedId))
.FirstOrDefault();
if (customer == null)
{
throw new ArgumentException("Id not found.");
}
customer.ContactName = newContactName;
dbContext.SaveChanges();
}
}
// Separate contexts at the same time are appropriate if they are for completely unrelated modules.
// In the example below the changes made by the second db context override the changes from the first one.
private static void TestMultipleDbContexts()
{
var firstDbContext = new NorthwindEntities();
var secondDbContext = new NorthwindEntities();
var firstCategoryFirstContext = firstDbContext.Categories.FirstOrDefault();
var firstCategorySecondContext = secondDbContext.Categories.FirstOrDefault();
firstCategoryFirstContext.CategoryName = "Drinks";
firstCategorySecondContext.CategoryName = "Override drinks";
firstDbContext.SaveChanges();
secondDbContext.SaveChanges();
}