Sales grouped by month then by product

Question

I want to get the list of sales for each month ordered by product.

Currently I have the following linq query:

        var query = storeDB.OrderDetails
            .Select(od => od)
            .GroupBy(r => r.Product)
            .Select(group => new ProductSalesModel {
                Product = group.Key,
                Sales = group.Sum(s => s.Quantity),
                Amount = group.Sum(s => s.Quantity * s.UnitPrice)
            })
            .OrderByDescending(x => x.Amount);

How should I do to also group the list by month getting the sales date from my Order table ? Order.CreationDate?

Answer 1

I don't know what Linq provider you are trying to use but in plain Linq this should work:

 .GroupBy(r => new {r.Product, r.CreationDate})

and then when you select:

.Select(group => new ProductSalesModel {
                Product = group.Key.Product,
                CreationDate = group.Key.CreationDate,
                Sales = group.Sum(s => s.Quantity),
                Amount = group.Sum(s => s.Quantity * s.UnitPrice)
            })