Use Linq to SQL to generate sales report

Question

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);
        }
    }

EDIT: Johannes answer was a great way to handle my query. Below is an adjustment to the code to get it working for this example in case anyone else has this issue. This will perform an outer join from the allDays table and return 0 values when there is no sales for 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 };

Answer 1

I think that if your enumeration does not contain data for a day, you cannot return values for that day. The best I can think of is to create a list of Order objects with a zero value for each day and create a union with the result of your query. Here is what I came up with. However, I think looping through each group, checking if any day is "skipped", and returning zero for each day that is "skipped" is more straightforward than creating your own enumeration in memory (unless you want an enumeration with the "missing gaps" filled in). Please note that I'm basically assuming that for each group, you want to sum all the values for a single 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/>");

Answer 2

You can group all your data by day and run sum over those groups. To fulfill the requirement of having a sum for each day, even those without order, you can either join a list of all the dates or simply use a loop to make sure all dates are included. Small hint: You don't need to set up the times explicitly if you compare by the DateTime.Date properties.

Here's the 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>");
            }


        }
    }
}