How can I add to my list empty objects based on week number

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

The old telephone trunk problem. Bell Telephone Scientist worked on determine max number of phone calls a company used to determine number of trunk lines needed. So the solution was to create a table of 30 seconds intervals and then fill in table. You needed to to this to make sure you have a table entry for each period. So you need to create a table that consists of a row for each week of the 6 month period. Then fill in table with data. The rows with no data added are the weeks with no entries. — jdweng, Sep 19 '19 at 14:47
this should help to create the list of distinct dates...for those...youll need to group by your week value...and union it to your data set to get your zeroes...https://stackoverflow.com/questions/3738748/create-an-array-or-list-of-all-dates-between-two-dates — Ctznkane525, Sep 19 '19 at 14:47
So you always want `yas` to contain 52 or 53 entries, one for each week of the year? — mm8, Sep 19 '19 at 14:56
@mm8 Yes, I allways want 4 entries for 1 month, even if database value exist only for 1 and 3 week for example I want empty value for 2. and 4. week :) — Roxy'Pro, Sep 19 '19 at 14:58
This gets every Monday : DateTime startDate = DateTime.Parse("1/7/19"); //first Monday of the year DateTime endDate = DateTime.Parse("6/7/19"); DateTime[] mondays = Enumerable.Range(0, ((endDate - startDate).Days / 7) + 1).Select((x, i) => startDate.Date.AddDays(7 * i)).ToArray(); — jdweng, Sep 19 '19 at 15:04

score 1 · Accepted Answer · answered Sep 19 '19 at 15:15

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

How can I add to my list empty objects based on week number

1 Answers1

Linked