Use Linq to SQL to generate sales report
- by Richard Reddy
I currently have the following code to generate a sales report over the last 30 days. I'd like to know if it would be possible to use linq to generate this report in one step instead of the rather basic loop I have here.
For my requirement, every day needs to return a value to me so if there are no sales for any day then a 0 is returned.
Any of the Sum linq examples out there don't explain how it would be possible to include a where filter so I am confused on how to get the total amount per day, or a 0 if no sales, for the last days I pass through.
Thanks for your help,
Rich
//setup date ranges to use
DateTime startDate = DateTime.Now.AddDays(-29);
DateTime endDate = DateTime.Now.AddDays(1);
TimeSpan startTS = new TimeSpan(0, 0, 0);
TimeSpan endTS = new TimeSpan(23, 59, 59);
using (var dc = new DataContext())
{
//get database sales from 29 days ago at midnight to the end of today
var salesForDay = dc.Orders.Where(b => b.OrderDateTime > Convert.ToDateTime(startDate.Date + startTS) && b.OrderDateTime <= Convert.ToDateTime(endDate.Date + endTS));
//loop through each day and sum up the total orders, if none then set to 0
while (startDate != endDate)
{
decimal totalSales = 0m;
DateTime startDay = startDate.Date + startTS;
DateTime endDay = startDate.Date + endTS;
foreach (var sale in salesForDay.Where(b => b.OrderDateTime > startDay && b.OrderDateTime <= endDay))
{
totalSales += (decimal)sale.OrderPrice;
}
Response.Write("From Date: " + startDay + " - To Date: " + endDay + ". Sales: " + String.Format("{0:0.00}", totalSales) + "<br>");
//move to next day
startDate = startDate.AddDays(1);
}
}
