public static void Create(Customer customer)
{
using (var db = new NorthwindEntities())
{
db.Customers.Add(customer);
db.SaveChanges();
}
}
// Work only with original object. Don't use select with update
public static void Update(string customerId, string newCompanyName)
{
using (var db = new NorthwindEntities())
{
var customerToUpdate = db.Customers.FirstOrDefault(c => c.CustomerID == customerId);
customerToUpdate.CompanyName = newCompanyName;
db.SaveChanges();
}
}
public static void Delete(string deletingCriteria)
{
using (var db = new NorthwindEntities())
{
var customerToDelete = db.Customers.FirstOrDefault(c => c.CompanyName.Contains(deletingCriteria));
db.Customers.Remove(customerToDelete);
db.SaveChanges();
}
}
public void OrdersByYearAndCountryWithNativeQuery(string country, int year)
{
using (var db = new NorthwindEntities())
{
string nativeSqlQuery = "SELECT DISTINCT c.ContactName" +
" FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID" +
" WHERE o.ShipCountry = '" + country + "' AND DATEPART(YEAR, o.OrderDate) = " + year;
var queryResult = db.Database.SqlQuery<string>(nativeSqlQuery).ToList();
foreach (var customer in queryResult)
{
Console.WriteLine(customer);
}
}
}
public void OrdersByYearAndCountry(string country, int year)
{
using (var db = new NorthwindEntities())
{
var customers = db
.Orders
.Where(o => o.OrderDate.Value.Year == year && o.ShipCountry == country)
.Select(c => c.Customer.ContactName)
.Distinct()
.ToList();
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
}
/// <summary>
/// Problem solved by only using only one connection or introducing transactions isolation levels
/// </summary>
public static void Main()
{
var firstConection = new NorthwindEntities();
var secondConection = new NorthwindEntities();
var customerFromFirstCon = firstConection.Customers.First();
var customerFromSecondCon = secondConection.Customers.First();
Console.WriteLine("Initial Name FisrtCon: {0} - SecondCon {1}", customerFromFirstCon.CompanyName, customerFromSecondCon.CompanyName);
customerFromFirstCon.CompanyName = "Mercedes";
// Second name will win.
customerFromSecondCon.CompanyName = "Jaguar";
firstConection.SaveChanges();
secondConection.SaveChanges();
var result = new NorthwindEntities().Customers.First();
Console.WriteLine("Name After Change {0}", result.CompanyName);
}
public void AllOrdersInRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
using (var db = new NorthwindEntities())
{
var orders = db
.Orders
.Where(o => o.ShipRegion == region && (startDate < o.ShippedDate && o.ShippedDate < endDate))
.Select(order => new
{
ShipRegion = order.ShipRegion,
ShipName = order.ShipName,
ShipDate = order.ShippedDate
})
.ToList();
foreach (var order in orders)
{
Console.WriteLine(order);
}
}
}
