Use Linq to SQL to Generate Sales Report
I currently have the following code to generate a sales report for the last 30 days. I would like to know if linq can be used to generate this report in one step, instead of the rather basic loop I have.
For my requirement, I need to return a value every day, so if there are no sales for the day, 0 is returned.
Any of the linux Lin examples does not explain how the filter can be enabled somewhere, so I am confused as to how to get the total per day, or 0 if there is no sales in the last days I go 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);
}
}
EDIT: Johannes's answer is a great way to handle my request. Below is a code adjustment to make it work for this example, in case anyone else has this problem. This will cast an outer join from the allDays table and return 0 if there are no sales on that day.
var query = from d in allDays
join s in salesByDay on d equals s.Day into j
from s in j.DefaultIfEmpty()
select new { Day = d, totalSales = (s != null) ? s.totalSales : 0m };
a source to share
You can group all the data by day and perform the sum on these groups. To meet the requirement of having an amount for each day, even without an order, you can either join the list of all dates or simply use a loop to make sure all dates are included. Little hint: you don't need to specify the time explicitly if you are comparing properties DateTime.Date
.
Here's a solution using a generator function (taken from the MoreLinq project):
public static partial class MoreEnumerable
{
public static IEnumerable<TResult> GenerateByIndex<TResult>(Func<int, TResult> generator)
{
// Looping over 0...int.MaxValue inclusive is a pain. Simplest is to go exclusive,
// then go again for int.MaxValue.
for (int i = 0; i < int.MaxValue; i++)
{
yield return generator(i);
}
yield return generator(int.MaxValue);
}
}
public class MyClass
{
private void test()
{
DateTime startDate = DateTime.Now.AddDays(-29);
DateTime endDate = DateTime.Now.AddDays(1);
using (var dc = new DataContext())
{
//get database sales from 29 days ago at midnight to the end of today
var salesForPeriod = dc.Orders.Where(b => b.OrderDateTime > startDate.Date && b.OrderDateTime <= endDate.Date);
var allDays = MoreEnumerable.GenerateByIndex(i => startDate.AddDays(i)).Take(30);
var salesByDay = from s in salesForPeriod
group s by s.OrderDateTime.Date into g
select new {Day = g.Key, totalSales = g.Sum(x=>(decimal)x.OrderPrice};
var query = from d in allDays
join s in salesByDay on s.Day equals d
select new {Day = s.Day , totalSales = (s != null) ? s.totalSales : 0m;
foreach (var item in query)
{
Response.Write("Date: " +item.Day.ToString() " Sales: " + String.Format("{0:0.00}", item.totalSales) + "<br>");
}
}
}
}
a source to share
I think that if your enum does not contain data for a day, you cannot return values for that day. The best I can do is create a list of Order objects with a null value for each day and create a union with the result of your query. Here's what I came up with. However, I think that looping through each group is checked if any day is "skipped", and returning zero for each day that is "skipped" is more straight forward than creating your own in-memory enum (unless you want to enumerate "missing gaps" "is filled in.) Note that I am basically assuming that for each group, you want to sum all the values in one day.
List<Order> zeroList = new List<Order>();
while (startDate <= endDate)
{
zeroList.Add(new Order { OrderDateTime = startDate, OrderPrice = 0 });
startDate = startDate.AddDays(1)
}
var comboList = zeroList.Union(dc.Orders.Where(b => b.OrderDateTime > Convert.ToDateTime(startDate.Date + startTS) && b.OrderDateTime <= Convert.ToDateTime(endDate.Date + endTS))
var groupedTotalSales = comboList.GroupBy(b => b.OrderDateTime.Date)
.Select(b => new { StartDate = Convert.ToDateTime(b.Key + startTS), EndDate = Convert.ToDateTime(b.Key + endTS), Sum = b.Sum(x => x.OrderPrice });
foreach (totalSale in groupedTotalSales)
Response.Write("From Date: " + totalSale.StartDate + " - To Date: " + totalSale.EndDate + ". Sales: " + String.Format("{0:0.00}", (decimal)totalSale.Sum) + "<br/>");
a source to share