public IQueryable <Category> GetCategories()
{
Debug.WriteLine("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");
var _db = new Models.ProductContext();
IQueryable <Category> query = _db.Categories;
return(query);
}
public IQueryable <Product> GetProducts([QueryString("id")] int?categoryId)
{
var _db = new Models.ProductContext();
IQueryable <Product> query = _db.Products;
if (categoryId.HasValue && categoryId > 0)
{
query = query.Where(p => p.CategoryID == categoryId); //skims off the query of products that to not have the matching Category ID
}
return(query);
}
public IQueryable <Product> GetProduct([QueryString("ProductID")] int?productId, [RouteData] string productName)
{
var _db = new Models.ProductContext(); //skims off the query of all products except the matching ID one.
IQueryable <Product> query = _db.Products;
if (productId.HasValue && productId > 0)
{
query = query.Where(p => p.ProductID == productId);
}
else if (!String.IsNullOrEmpty(productName))
{
query = query.Where(p => String.Compare(p.ProductName, productName) == 0);
//Compares two specified String objects and returns an
//integer that indicates their relative position in the sort order.
//if string.compare()== 0, strA occurs in the same position as strB in the sort order.
}
else
{
query = null;
}
return(query);
}
/// <summary>
/// Kata : Performance problems with databases and queries are legendary - much like this one, overlooked details can make your site crawl
///1. Find the issue with leaking connections
///2. Find the error that this page is generating
///3. Come up with a better way to get and display the information below
///Hint: Use New Relic to look at the query plan of all of the queries on this page
///to see if any suggestions about what to do with the query or schema can be made.
/// </summary>
/// <returns></returns>
public ActionResult TheQuery()
{
List<ProductView> products = new List<ProductView>();
using (var db = new ProductContext())
{
var query = (from p in db.Products
join info in db.ProductInformation on p.ProductInformation.Id equals info.Id
join dc in db.DistributionCenter on p.Id equals dc.Id into dcp
from x in dcp.DefaultIfEmpty()
orderby info.Price, p.Name, x.Quantity
select new ProductView
{
ProductId = p.Id,
Name = p.Name,
Desctiption = p.Desctiption,
CreateDate = p.CreateDate,
Color = info.Color,
Price = info.Price,
Quantity = x.Quantity,
});
//products = query.ToList<ProductView>();
foreach (Product p in db.Products.Include("ProductInformation")) {
ProductView pv = query.Where(x => x.ProductId == p.Id).FirstOrDefault() as ProductView;
if(!string.IsNullOrEmpty(p.ProductInformation.Color)){
switch (p.ProductInformation.Color) {
case "Red":
pv.ColorHex = "#FF0000";
break;
case "Black":
pv.ColorHex = "#000000";
break;
default:
pv.ColorHex = "#FFFFFF";
break;
}
}
products.Add(pv);
}
}
return View(products);
}