public List<CustomerOrderSummary> GetCustomerOrderSummaries()
{
var dbContext = new NWContext();
var data = (from purchase in dbContext.Orders
where purchase.OrderDate.HasValue
select new CustomerOrderSummary()
{
OrderDate = purchase.OrderDate.Value,
// see http://blog.dreamlabsolutions.com/post/2008/11/17/LINQ-Method-cannot-be-translated-into-a-store-expression.aspx
Freight = purchase.Freight ?? 0m, // purchase.Freight.GetValueOrDefault(),
Subtotal = purchase.Order_Details
.Sum(x =>
(decimal?)(x.UnitPrice * x.Quantity)
) ?? 0, // NOTE: See Footnote 1
Discount = purchase.Order_Details
.Sum(x =>
x.UnitPrice * x.Quantity *
(((decimal)((int)(x.Discount * 100))) / 100) // NOTE: See Footnote 2
),
Total = purchase.Order_Details.Sum(x => (x.UnitPrice * x.Quantity) -
(x.UnitPrice * x.Quantity *
(((decimal)((int)(x.Discount * 100))) / 100) // NOTE: See Footnote 2
)
),
ItemCount = purchase.Order_Details.Count(),
ItemQuantity = purchase.Order_Details.Sum(x => (short?)x.Quantity) ?? 0,
AverageItemUnitPrice = purchase.Order_Details.Average(x => (decimal?)x.UnitPrice) ?? 0,
CompanyName = purchase.Customer.CompanyName,
ContactName = purchase.Customer.ContactName,
ContactTitle = purchase.Customer.ContactTitle,
CustomerId = purchase.CustomerID
}).ToList();
return data;
}
public int Add(Region region)
{
// TODO: Add Unit Test
if (region == null)
throw new ArgumentNullException("region", "region is null.");
using (var dbContext = new NWContext())
{
/* NOTE:
* The TerritoryID column in Territories is a string - nvarchar(20) - rather than an integer.
* The existing data in Northwind Traders uses the zip code of the city/town as the TerritoryID.
* This sample just "simplifies" and assigns the territory description as the ID, since we're
* in Canada and we aren't using a single zip or postal code.
*/
foreach (var territory in region.Territories)
if (string.IsNullOrEmpty(territory.TerritoryID))
territory.TerritoryID = territory.TerritoryDescription;
/* NOTE:
* The RegionID column in Regions is an integer, but it is not an IDENTITY column.
* As such, we're simply going to get the next highest ID available.
*/
if (region.RegionID <= 0)
region.RegionID = dbContext.Regions.Max(item => item.RegionID) + 1;
dbContext.Regions.Add(region);
dbContext.SaveChanges();
return region.RegionID;
}
}
// TODO: Create a method called GetOrders() that will return a list of Order objects from the database.
public List<Order> GetOrders()
{
using (var context = new NWContext())
{
var result = context.Orders;
return result.ToList();
}
}
public List<Employee> GetEmployees()
{
using (var context = new NWContext())
{
var result = context.Employees;
return result.ToList();
}
}
public int AddShipper(Shipper info)
{
using (var context = new NWContext())
{
context.Shippers.Add(info);
context.SaveChanges();
return info.ShipperID;
}
}
public int AddProduct(Product info)
{
using (var context = new NWContext())
{
context.Products.Add(info);
context.SaveChanges();
return info.ProductID;
}
}
public void DeleteProduct(Product product)
{
using (var context = new NWContext())
{
var found = context.Products.Find(product.ProductID);
if (found != null)
{
context.Products.Remove(found);
context.SaveChanges();
}
}
}
public void DeleteShipper(Shipper shipper)
{
using (var context = new NWContext())
{
//BEST practice. find shipper first, then delete
var found = context.Shippers.Find(shipper.ShipperID);
if (found != null)
{
context.Shippers.Remove(found);
context.SaveChanges();
}
}
}
public List<ProductSaleSummary> GetProductSaleSummaries()
{
// NOTE: See Footnote 1
// NOTE: See Footnote 2
var dbContext = new NWContext();
var data =
(from item in dbContext.Products
where !item.Discontinued
select new ProductSaleSummary()
{
TotalSales = item.Order_Details.Sum(x => (decimal?)(x.UnitPrice * x.Quantity)) ?? 0, // NOTE: See Footnote 1
TotalDiscount = item.Order_Details
.Sum(x => (decimal?)
(x.UnitPrice * x.Quantity *
(((decimal)((int)(x.Discount * 100))) / 100) // NOTE: See Footnote 2
)) ?? 0,
SaleCount = item.Order_Details.Count(),
SaleQuantity = item.Order_Details.Sum(x => (short?)x.Quantity) ?? 0,
AverageUnitPrice = item.Order_Details.Average(x => (decimal?)x.UnitPrice) ?? 0,
ProductName = item.ProductName,
QuantityPerUnit = item.QuantityPerUnit,
UnitsInStock = item.UnitsInStock.HasValue ?
item.UnitsInStock.Value : (short)0,
UnitsOnOrder = item.UnitsOnOrder.HasValue ?
item.UnitsOnOrder.Value : (short)0,
ReorderLevel = item.ReorderLevel.HasValue ?
item.ReorderLevel.Value : (short)0,
Discontinued = item.Discontinued,
CurrentUnitPrice = item.UnitPrice.HasValue ?
item.UnitPrice.Value : 0,
CategoryId = item.CategoryID.HasValue ?
item.CategoryID.Value : 0,
ProductId = item.ProductID
}).ToList();
var dbInventoryContext = new NWContext();
foreach (var item in data)
if (item.CategoryId > 0)
item.CategoryName = dbInventoryContext.Categories.Find(item.CategoryId).CategoryName;
return data;
}
public void Update(Region region, List<Territory> territories)
{
// TODO: Add Unit Test
if (region == null)
throw new ArgumentNullException("region", "region is null.");
if (territories == null)
throw new ArgumentNullException("territories", "territories is null.");
using (var dbContext = new NWContext())
{
foreach (var item in territories)
{
var found = dbContext.Territories.Find(item.TerritoryID);
if (found != null)
{
/* NOTE:
* Pre-process the Territory IDs to see if they should be "synced" with the name/description.
* This will be the case if, in the original, the ID was the same as the description
*/
string foundTerritoryID = found.TerritoryID;
string foundTerritoryDescription = found.TerritoryDescription.Trim(); // HACK: Turns out, the column is nchar(50), not an nvarchar....
string itemTerritoryID = item.TerritoryID;
string itemTerritoryDescription = item.TerritoryDescription.Trim();
if (foundTerritoryID.Equals(foundTerritoryDescription) &&
!itemTerritoryID.Equals(itemTerritoryDescription))
{
item.TerritoryID = itemTerritoryDescription;
dbContext.Territories.Remove(found); // Because the PK has changed...
dbContext.Territories.Add(item); // Because the PK has changed...
}
}
}
dbContext.Entry(region).State = EntityState.Modified;
dbContext.SaveChanges();
}
}
public Shipper GetShipper(int shipperId)
{
using (var context = new NWContext())
{
return context.Shippers.Find(shipperId);
}
}
public List<Region> GetRegions()
{
using (var context = new NWContext())
{
var result =
context.Regions
.Include(item => item.Territories)
.OrderBy(item => item.RegionDescription);
return result.ToList();
}
}
public Product GetProduct(int productID)
{
using (var context = new NWContext())
{
return context.Products.Find(productID);
}
}
public IList<Product> ListProducts()
{
using (var context = new NWContext())
{
return context.Products.ToList();
}
}
public IList<Shipper> ListShippers()
{
using (var context = new NWContext())
{
return context.Shippers.ToList();
}
}
public void UpdateShipper(Shipper info)
{
//Note: see question and commentary on
//http://stackoverflow.com/questions/15336248/entity-framework-5-updating-a-reocrd
using (var context = new NWContext())
{
context.Shippers.Attach(info);
context.Entry(info).State = EntityState.Modified;
context.SaveChanges();
}
}
public void UpdateProduct(Product info)
{
using (var context = new NWContext())
{
context.Products.Attach(info);
context.Entry(info).State = EntityState.Modified;
context.SaveChanges();
}
}