I have 2 objects (Order and Product) and a third (OrderDetail) that will be used as "navigation" between Products and Orders.

I'm trying to build a view that will show the less-sold products. For that I am "querying" the object OrderDetail and saving the result in a view model to later on be used in the view.

Model:

public class Product
{
public int ProductID { get; set; }
public string CodProduct { get; set; }
public string Nome { get; set; }
(...)
public ICollection<OrderDetail> OrderDetails { get; set; }
}

public class Order
{
public int OrderID { get; set; }
(...)
[BindNever]
public ICollection<OrderDetail> OrderDetails { get; set; }
}

public class OrderDetail
{
public int OrderDetailId { get; set; }
public int OrderId { get; set; }
public int ProductID { get; set; }
public int Quantity { get; set; }
public decimal UnitPrice { get; set; }
public virtual Product Product { get; set; }
public virtual Order Order { get; set; }
}


ViewModel:

public class ProductSoldViewModel
{
//Data from the Product
public string ProductCode { get; set; }
public string ProductName { get; set; }
//Data from the OrderDetail
public int Qty { get; set; }
}


Controller:

public IActionResult LSProducts()
{
List<ProductSoldViewModel> model = new List<ProductSoldViewModel>();

model = _context.OrderDetail
.GroupBy(od => od.ProductID)
.Select(o => new ProductSoldViewModel
{
ProductCode = o.Select(s => s.Product.CodProduct).FirstOrDefault(),
ProductName = o.Select(s => s.Product.Nome).FirstOrDefault(),
Qty = o.Sum(s => s.Quantity)
})
.OrderBy(od => od.Qty)
.ToList();

return View(model);
}


With this code, I get only the less-sold products that are present in the orders. But I need to get all the products, even those that have never been sold.

Can you give me some advice about how can I do that?

1 Answer
1

You should query the product table if you need to get all products:

public IActionResult LSProducts()
{
List<ProductSoldViewModel> model = new List<ProductSoldViewModel>();

model = _context.Product
.Include(a => a.OrderDetails)
.Select(o => new ProductSoldViewModel
{
ProductCode = o.CodProduct,
ProductName = o.Nome,
Qty = o.OrderDetails.Sum(s => s.Qty)
})
.OrderBy(od => od.Qty)
.ToList();

return View(model);
}


To avoid a null exception you may want to add the following constructor to your model:

public class Product
{
public Product()
{
OrderDetails = new HashSet<OrderDetail>();
}

(...)

public ICollection<OrderDetail> OrderDetails { get; set; }
}


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.