Question
I'm getting data from db like this:
Later I group them so it looks like
Month 6
Week 2
Amount 228
And so on..
Here is the code:
var yas = await _context.product
.AsNoTracking()
.Where(x => (x.PaymentDate != null && x.PaymentDate > DateTime.UtcNow.AddMonths(-4))).ToListAsync();
var grouped = yas.GroupBy(x => CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(x.PaymentDate ?? DateTime.UtcNow, CalendarWeekRule.FirstDay, DayOfWeek.Monday))
.Select(product => new productsDemoObject
{
Week = GetWeekNumberOfMonth(product.FirstOrDefault().PaymentDate.Value),
Amount = product.Sum(x => x.Amount),
Month = product.FirstOrDefault().PaymentDate.Value.Month
});
As you can see for Month 6 there is only data for week 2. And it's group and work as expected, but now I'm wondering how could I add empty object with amount of 0 for missing weeks.
For example if there is only week 2, lets add data with amount 0 for week 1,3 and 4.
In example of Month 8, because there are weeks 2 and 3 I should add week 1 and 4 with amount of 0.
How could I achieve this?
Thanks guys
Cheers
Accepted Answer
If you want an entry per week of the year, you need to create these somehow. You could take a look at this answer and modify it a bit according to your requirements.
Something like this:
DateTime jan1 = new DateTime(DateTime.Today.Year, 1, 1);
//beware different cultures, see other answers
DateTime startOfFirstWeek = jan1.AddDays(1 - (int)(jan1.DayOfWeek));
Dictionary<int, productsDemoObject> allWeeks =
Enumerable
.Range(0, 54)
.Select(i => new {
weekStart = startOfFirstWeek.AddDays(i * 7)
})
.TakeWhile(x => x.weekStart.Year <= jan1.Year)
.Select(x => new {
x.weekStart,
weekFinish = x.weekStart.AddDays(4)
})
.SkipWhile(x => x.weekFinish < jan1.AddDays(1))
.Select((x, i) => new productsDemoObject
{
Week = i + 1,
Month = x.weekStart.Month,
Amount = 0
})
.ToDictionary(x => x.Week, x => x);
foreach(var week in yas
.GroupBy(x => CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(x.PaymentDate ?? DateTime.UtcNow, CalendarWeekRule.FirstDay, DayOfWeek.Monday)))
{
allWeeks[week.Key].Amount = week.Sum(x => x.Amount);
}
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow