1

Firstly, I've found a solution to my problem. The question is - is there a better way?

I needed to retrieve a list of Order counts for a period (7 days), such as using a group by. On some days there are no orders, which would mean those days would be eliminated from a group by.

This is what I needed to achieve (using a Kendo UI bar chart):

A graph of orders for the last 7 days, some days with 0 orders

I've done this in 2 stages. The first to create an Enumeration of the days, then secondly to retrieve the count of Orders from the database.

Notes: db is an Entity Framework data context. DbFunctions.TruncateTime() is used to compare only the date, ignoring the time part).

var from = DateTime.Now.AddDays(-6);
var to = DateTime.Now;

var days = Enumerable.Range(0, 1 + to.Subtract(from).Days)
            .Select(offset => from.AddDays(offset))
            .ToArray();

var data = days
           .Select(i => new
           {
                Day = i.ToShortDateString(),
                Orders = db.Orders.Count(d => DbFunctions.TruncateTime(o.OrderDate) == i.Date)
            });

Can it be done better? To retrieve a count/sum of items over the last 7 days?

pfeds
  • 2,183
  • 4
  • 32
  • 48

2 Answers2

0

I think this should work. The idea is firstly filter for all Orders having OrderDate in range, then to fill up the missing OrderDate we can use Concat to append some default Dates (in range). Next we use GroupBy and OrderBy to prepare for projection. Finally we project it normally but note that g.Count() should be decreased by 1 because we appended all default Dates (making orders of each Date be increased by 1). 

var data = db.Orders.Select(o => DbFunctions.TruncateTime(o.OrderDate))
                    .Where(d => DbFunctions.DiffDays(DbFunctions.TruncateTime(DateTime.Now), d) < 7)
                    .Concat(Enumerable.Range(0,7).Select(i => DateTime.Now.Date.AddDays(-i) as DateTime?))       
                    .GroupBy(d => d)
                    .OrderBy(g => g.Key)
                    .Select(g => new {
                                 Day = g.Key.ToShortDateString(),
                                 Orders = g.Count() - 1
                             });
Hopeless
  • 4,397
  • 5
  • 37
  • 64
  • 1
    This will not work since GroupBy with eleminate dates with no orders. see 21/08/2015 & 25/08/2015 in graph. – Nikita Shrivastava Aug 27 '15 at 09:36
  • @Nikita yes I misread the question. After that I've tried finding a work-around and ended up with an updated answer. – Hopeless Aug 27 '15 at 09:56
  • Enumerable has time part ,so currently I might get two records ,one for 25/08/2015 & another for 25/08/2015 15:50:00.Also, why we need to "- 1" from the count? – Nikita Shrivastava Aug 27 '15 at 10:13
  • 1
    @Nikita in Enumerable, I used `DateTime.Now.Date` not just `DateTime.Now` so the time part should be removed. As I explained, the `Concat` makes orders count of each Date be increased by 1. So at the end we have to subtract 1 (then the missing Dates will have orders count = 0). – Hopeless Aug 27 '15 at 10:15
0

This solution has a major problem that you are querying db.Orders each time, that can be expensive if Order has thousands of records. Yes, there is a better way:

var orderDictionary= (from ord in db.Orders
                     let dateOfOrder=DbFunctions.TruncateTime(ord.OrderDate)
                     where dateOfOrder>=fromDate && dateOfOrder<=toDate 
                     group by dateOfOrder into ordGrp
                  select new {Key=ordGrp.Key,Count=ordGrp.Count()}).ToDictionary(o=>o.Key,o=>o.Count);

var data=days.Select(i=>new
                     {   Day=i.ToShortDateString(), 
                         NoOfOrders=orderDictionary.ContainsKey(i.Date)?orderDictionary[i.Date]:null
                     });
Nikita Shrivastava
  • 2,978
  • 10
  • 20