locked
.net core 2.2 efcore sum column in list RRS feed

  • Question

  • User1980526530 posted

    simple question/answer hopefully since I cannot wrap my head around the syntax and how to do this. 

    I am populating a grid with the code below: 

    public IActionResult OrderBestSellersByQuantity()
            {
                var ordersList = _orderService.SearchOrders(customerId: _workContext.CurrentCustomer.Id);
                
                
                var orders = ordersList.ToList().Select(x => new BestsellerModel
                {
                    ProductName = x.OrderItems.First().Product.Name,
                    TotalQuantityInt = x.OrderItems.First().Quantity,
                    TotalAmount = _priceFormatter.FormatPrice(x.OrderItems.First().UnitPriceInclTax, true, false) 
                }).OrderByDescending(o => o.TotalQuantityInt);
                return Json(orders);
            }

     I would like to sum Quantity and sum the price. 

    Tuesday, October 13, 2020 11:10 PM

Answers

  • User711641945 posted

    Hi BreauP,

    Could you please share your models?

    If the name of OrderItems in each Order are the same, refer to the following code.

    Here is the simulated data.

    var ordersList = new List<Order>
                {
                    new Order
                    {
                        customerId=10,
                        OrderItems=new List<OrderItem>
                        {
                            new OrderItem
                            {
                                Product=new Product{ Name="product1"},
                                Quantity=6,
                                UnitPriceInclTax=18
                            },
                            new OrderItem
                            {
                                Product=new Product{ Name="product1"},
                                Quantity=2,
                                UnitPriceInclTax=12
                            }
                        }
                    },
                    new Order
                    {
                        customerId=10,
                        OrderItems=new List<OrderItem>
                        {
                            new OrderItem
                            {
                                Product=new Product{ Name="product2"},
                                Quantity=3,
                                UnitPriceInclTax=17
                            }
                        }
                    }
    };

    After obtaining a product’s name, calculate its attributes through the Sum function.

    var orders = ordersList.ToList().Select(x => new List<BestsellerModel>
                {
                    new BestsellerModel
                    {
                        ProductName=x.OrderItems.First().Product.Name,
                        TotalQuantityInt = x.OrderItems.Sum(x=>x.Quantity),               
                        TotalAmount=x.OrderItems.Sum(x=>x.UnitPriceInclTax*x.Quantity)
                    }
                });

    Here is the model.

    public class Order
    {
            public int customerId { get; set; }
            public List<OrderItem> OrderItems { get; set; }
    }
    public class OrderItem
    {
            public Product Product { get; set; }
            public int Quantity { get; set; }
            public int UnitPriceInclTax { get; set; }
    }
    public class Product
    {
            public string Name { get; set; }
    }
    public class BestsellerModel
    {
            public string ProductName { get; set; }
            public int TotalQuantityInt { get; set; }
            public int TotalAmount { get; set; }
    }

    Result:

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 14, 2020 2:52 AM
  • User585649674 posted

    Try

    Sum (y=>y.quantity)

    x is declared in the outer section

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 15, 2020 9:52 AM
  • User-2054057000 posted

     I would like to sum Quantity and sum the price. 

    You can use a bit of jQuery to achieve it. See How to show sum of columns in the footer. 

    Now in your last line of code which is:

    return Json(orders);

    The orders will now not return the "TotalQuantityInt" and "TotalAmount" instead your jQuery code should do it for you automatically. I hope I am clear in my explanation.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 17, 2020 12:35 PM

All replies

  • User711641945 posted

    Hi BreauP,

    Could you please share your models?

    If the name of OrderItems in each Order are the same, refer to the following code.

    Here is the simulated data.

    var ordersList = new List<Order>
                {
                    new Order
                    {
                        customerId=10,
                        OrderItems=new List<OrderItem>
                        {
                            new OrderItem
                            {
                                Product=new Product{ Name="product1"},
                                Quantity=6,
                                UnitPriceInclTax=18
                            },
                            new OrderItem
                            {
                                Product=new Product{ Name="product1"},
                                Quantity=2,
                                UnitPriceInclTax=12
                            }
                        }
                    },
                    new Order
                    {
                        customerId=10,
                        OrderItems=new List<OrderItem>
                        {
                            new OrderItem
                            {
                                Product=new Product{ Name="product2"},
                                Quantity=3,
                                UnitPriceInclTax=17
                            }
                        }
                    }
    };

    After obtaining a product’s name, calculate its attributes through the Sum function.

    var orders = ordersList.ToList().Select(x => new List<BestsellerModel>
                {
                    new BestsellerModel
                    {
                        ProductName=x.OrderItems.First().Product.Name,
                        TotalQuantityInt = x.OrderItems.Sum(x=>x.Quantity),               
                        TotalAmount=x.OrderItems.Sum(x=>x.UnitPriceInclTax*x.Quantity)
                    }
                });

    Here is the model.

    public class Order
    {
            public int customerId { get; set; }
            public List<OrderItem> OrderItems { get; set; }
    }
    public class OrderItem
    {
            public Product Product { get; set; }
            public int Quantity { get; set; }
            public int UnitPriceInclTax { get; set; }
    }
    public class Product
    {
            public string Name { get; set; }
    }
    public class BestsellerModel
    {
            public string ProductName { get; set; }
            public int TotalQuantityInt { get; set; }
            public int TotalAmount { get; set; }
    }

    Result:

    Best Regards,

    Rena

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 14, 2020 2:52 AM
  • User1980526530 posted

    Hi Rena,

    thanks for your reply much appreciated, when implementing the code I get this:

    https://prnt.sc/uz1818

    I tried changing it to another letter like q for example it did remove the error but off course it calculates everything for that order instead of grouping by product id. 

    BestsellerModel

    public int ProductId { get; set; }
    public string ProductName { get; set; }
    public string TotalAmount { get; set; }
    public decimal TotalQuantity { get; set; }
    public int TotalQuantityInt { get; set; }

    OrderItemsModel

       public int ProductId { get; set; }
    
            public string ProductName { get; set; }
    
            public string VendorName { get; set; }
    
            public string Sku { get; set; }
    
            public string PictureThumbnailUrl { get; set; }
    
            public string UnitPriceInclTax { get; set; }
    
            public string UnitPriceExclTax { get; set; }
    
            public decimal UnitPriceInclTaxValue { get; set; }
    
            public decimal UnitPriceExclTaxValue { get; set; }
    
            public int Quantity { get; set; }
    
            public string DiscountInclTax { get; set; }
    
            public string DiscountExclTax { get; set; }
    
            public decimal DiscountInclTaxValue { get; set; }
    
            public decimal DiscountExclTaxValue { get; set; }
    
            public string SubTotalInclTax { get; set; }
    
            public string SubTotalExclTax { get; set; }
    
            public decimal SubTotalInclTaxValue { get; set; }
    
            public decimal SubTotalExclTaxValue { get; set; }
    
            public string AttributeInfo { get; set; }
    
            public string RecurringInfo { get; set; }
    
            public string RentalInfo { get; set; }
    
            public IList<ReturnRequestBriefModel> ReturnRequests { get; set; }
    
            public IList<int> PurchasedGiftCardIds { get; set; }
    
            public bool IsDownload { get; set; }
    
            public int DownloadCount { get; set; }
    
            public DownloadActivationType DownloadActivationType { get; set; }
    
            public bool IsDownloadActivated { get; set; }
    
            public Guid LicenseDownloadGuid { get; set; }

    Wednesday, October 14, 2020 9:37 AM
  • User585649674 posted

    Try

    Sum (y=>y.quantity)

    x is declared in the outer section

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 15, 2020 9:52 AM
  • User1980526530 posted

    Thanks for the reply but that still not doing the trick. 

    I think the problem here is my approach as i'm returning records one by one I think I need another way to bring them all and then group by to sum then return into json .... 

    Any ideas ? 

    Thursday, October 15, 2020 7:56 PM
  • User-2054057000 posted

     I would like to sum Quantity and sum the price. 

    You can use a bit of jQuery to achieve it. See How to show sum of columns in the footer. 

    Now in your last line of code which is:

    return Json(orders);

    The orders will now not return the "TotalQuantityInt" and "TotalAmount" instead your jQuery code should do it for you automatically. I hope I am clear in my explanation.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 17, 2020 12:35 PM
  • User1980526530 posted

    I had a hard time wrapping my head around the platform I was using, so I worked around it.

    Simply created a new table and populate the table based of the order items linking the customer id and order id. Then I was able to show the current user order items group by then sum. 

    Thanks all for the help 

    Tuesday, October 20, 2020 6:05 PM