Home > Enterprise >  Get the best selling product by using C# LINQ
Get the best selling product by using C# LINQ

Time:09-05

I have a list of Receipts

IEnumerable<Receipt> Receipts =
            new List<Receipt>()
            {
                new Receipt
                {
                    Id = 1, CustomerId = 1, IsCheckedOut = false, OperationDate = new DateTime(2021, 1, 2),
                    ReceiptDetails = new List<ReceiptDetail>()
                    {
                        new ReceiptDetail { Id = 1, ProductId = 1, UnitPrice = 10, Product = ProductEntities.ElementAt(0), DiscountUnitPrice = 9, Quantity = 2, ReceiptId = 1 },
                        new ReceiptDetail { Id = 2, ProductId = 2, UnitPrice = 20, Product = ProductEntities.ElementAt(1), DiscountUnitPrice = 19, Quantity = 8, ReceiptId = 1},
                        new ReceiptDetail { Id = 3, ProductId = 3, UnitPrice = 25, Product = ProductEntities.ElementAt(2), DiscountUnitPrice = 24, Quantity = 1, ReceiptId = 1 },
                    }
                },
                new Receipt
                {
                    Id = 2, CustomerId = 2, IsCheckedOut = false, OperationDate = new DateTime(2021, 1, 15),
                    ReceiptDetails = new List<ReceiptDetail>()
                    {
                        new ReceiptDetail { Id = 4, ProductId = 1, UnitPrice = 10, Product = ProductEntities.ElementAt(0), DiscountUnitPrice = 9, Quantity = 10, ReceiptId = 2 },
                        new ReceiptDetail { Id = 5, ProductId = 3, UnitPrice = 25, Product = ProductEntities.ElementAt(2), DiscountUnitPrice = 24, Quantity = 1, ReceiptId = 2 }
                    }
                },
                new Receipt
                {
                    Id = 3, CustomerId = 1, IsCheckedOut = false, OperationDate = new DateTime(2021, 2, 15),
                    ReceiptDetails = new List<ReceiptDetail>()
                    {
                        new ReceiptDetail { Id = 6, ProductId = 1, UnitPrice = 10, Product = ProductEntities.ElementAt(0), DiscountUnitPrice = 9, Quantity = 10, ReceiptId = 3 },
                        new ReceiptDetail { Id = 7, ProductId = 2, UnitPrice = 25, Product = ProductEntities.ElementAt(1), DiscountUnitPrice = 24, Quantity = 1, ReceiptId = 3 }
                    }
                }
            };

I need to get the best selling(by quantity) products based on CustomerId

Here is my function

public IEnumerable<Product> GetMostSoldProductByCustomer(int customerId, int productCount)
    {
       var a = ReceiptEntities.Where(rd => rd.CustomerId == customerId)..SelectMany(x => x.ReceiptDetails);
       var b = a.GroupBy(rd => rd.ProductId);
       var c = b.Select(p => p.Select(y => y.Quantity).Sum());
    }

I'm stuck on this, I have no idea how to connect b and c. For better understanding, if customerId = 1 and productCount = 3. The function should return 3 products with Id = 1, 2, 3 accordingly in descending order by quantities

One note! The customer whose id = 1 has two receipts, that's why I'm calculating the sum of Quantity as there are the same products in different receipts

CodePudding user response:

Try the following query:

public IEnumerable<int> GetMostSoldProductByCustomer(int customerId, int productCount)
{
    var query = 
        from re in ReceiptEntities
        where re.CustomerId == customerId
        from rd in re.ReceiptDetails
        group rd by rd.ProductId into g
        select new 
        {
            ProductId = g.Key,
            TotalQuantity = g.Sum(x => x.Quantity)
        } into s
        orderby descending s.TotalQuantity
        select s.ProductId;

    return query;
}
  • Related