product sale qty sum and name in linq group query-CodePudding

I have a product sales data and want to show the summary of sale grouped by product id. Summary result should show product name and total sales. How can I select a field along with groupby result and that field is not the key field.

public partial class SaleOrderDetail
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
    public decimal LineTotal { get; set; }
}

var query = from saleorder in _dbContext.SaleOrderDetail
                      group saleorder by saleorder.ProductId into salesummary
                      select new
                      {
                          productid = salesummary.Key,
                          prdouctname = salesummary.First().ProductName,
                          totalqty = salesummary.Sum(s => s.Quantity)
                      };

I got the error invalidoperationException because of First() for product name.

CodePudding user response：

You have to include ProductName in grouping Key.

var query = 
    from saleorder in _dbContext.SaleOrderDetail
    group saleorder by new {saleorder.ProductId, saleorder.ProductName} into salesummary
    select new
    {
        productid = salesummary.Key.ProductId,
        prdouctname = salesummary.Key.ProductName,
        totalqty = salesummary.Sum(s => s.Quantity)
    };

CodePudding user response：

Making SaleOrderDetail as AsEnumerable() did the trick. For SQL Expression it will work if make it as AsEnumerable() or .ToList<> etc.

var query = from saleorder in _dbContext.SaleOrderDetail.AsEnumerable()
                      group saleorder by saleorder.ProductId into salesummary
                      select new
                      {
                          productid = salesummary.Key,
                          prdouctname = salesummary.First().ProductName,
                          totalqty = salesummary.Sum(s => s.Quantity)
                      };