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

Question

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

c# entity-framework-core group-by linq

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

Roxy'Pro

View more on Stack Overflow

mm8 · Accepted Answer · 9/19/2019 3:15:36 PM

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

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related