This code is performing well as expected.
var soldQtyForEachItem = await _context.InvoiceProduct
.Where(x => x.ProductId != 0)
.GroupBy(x => x.ProductId)
.Select(grp => new CompanyProducts
{
CompanyProductId = grp.Key,
CompanyProductSoldQuantity = grp.Sum(item => item.QuantitySold)
}).ToListAsync();
Question :
I now need to join another table called Products and filter by Id against InvoiceProduct table and retrieve ProductsItemName which is a row from Products table; then all need to go to a custom type "CompanyProducts" below.
Please how do I achieve it?
public class CompanyProducts
{
public int CompanyProductId { get; set; }
public int CompanyProductName { get; set; }
public int CompanyProductSoldQuantity { get; set; }
}
InvoiceProduct table is a many to many, meaning that one InvoiceId may have multiple ProductIds.
Products table has property like ProductId, ProductsItemName.
Simplest solutions is to include ProductItemName into grouping:
var soldQtyForEachItem = await _context.InvoiceProduct
.Where(x => x.ProductId != 0)
.GroupBy(x => new { x.ProductId, x.Product.ProductsItemName } )
.Select(grp => new CompanyProducts
{
CompanyProductId = grp.Key.ProductId,
CompanyProductName = grp.Key.ProductsItemName,
CompanyProductSoldQuantity = grp.Sum(item => item.QuantitySold)
})
.ToListAsync();