CODEWITHSUNDEEP
c#linqentity-framework-6
Amelia
Amelia

Reputation: 23

Entity Framework groupby day to day in month

I'm trying to do a groupby from the 16th of each months to the 15th of the next months.

Is it possible with Entity Framework or linq?

var result = await db.Orders.Where(o => o.OrderDate > date)
                            .GroupBy(...)
                            .ToListAsync();

Upvotes: 2

Views: 543

Answers (1)

NetMage
NetMage

Reputation: 26917

To make this work with EF and SQL, you can just do some date math to compute a "period" number for grouping. Basically, you convert each date to the month, offset by the 16th, plus add an offset for the year:

var result = await db.Orders.Where(o => o.OrderDate > date)
                            .GroupBy(o => o.OrderDate.Month + 12*(o.OrderDate.Year - date.Year) - (o.OrderDate.Day >= 16 ? 0 : 1))
                            .ToListAsync();

Per @stuartd, here is how to get back the period for each group:

var resultp = result.Select(r => new {
                  PeriodBegin = new DateTime(date.Year + r.Key / 12 - (r.Key % 12 == 0 ? 1 : 0), r.Key % 12 == 0 ? 12 : r.key % 12, 16),
                  PeriodEnd = new DateTime(date.Year + r.Key / 12, r.Key % 12 + 1, 15),
                  Orders = r.ToList()
              }).ToList();

Upvotes: 1

Related Questions