How to ensure that there are 4 values added for each id

Question

I'm playing with "weeks". I'm retrieving data from a DB in a way like this:

My object in c# looks like this:

public class DemoObj
{
 public decimal Amount { get; set; }
 public int Month { get; set; }
 public int Week { get; set; }
}

I'm grouping data as I wrote on the right of the image, and it works fine and on the end it looks like this:

Month 6
Week 2

Month 8
Week 2

Month 8
Week 3

But I would like to achieve next:

Check if there are no all 4 weeks in one month for example for month with value 8, lets add missing weeks even if that will be empty object, filling week value would be enought so at the end value would look like this:

 Month 8
 Week 1

 Month 8
 Week 2

 Month 8
 Week 3

 Month 8
 Week 4

So, check if there are not all 4 weeks for value 8, if not, than lets add missing ones..

Here is my current code:

var query = await _context.product
                     .AsNoTracking()
                     .Where(x => (x.PaymentDate != null && x.PaymentDate > DateTime.UtcNow.AddMonths(-4))).ToListAsync();


var groupedData = query.GroupBy(x => CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(x.PaymentDate ?? DateTime.UtcNow, CalendarWeekRule.FirstDay, DayOfWeek.Monday))
    .Select(product => new productsChartDTO
    {
        Week = GetWeekNumberOfMonth(product.FirstOrDefault().PaymentDate.Value),
        Amount = product.Sum(x => x.Amount),
        Month = product.FirstOrDefault().PaymentDate.Value.Month
    });

So only for returned months, if there are not all 4 weeks (Values with 1,2,3,4) lets find which one are missing and lets add them something like that.

Any kind of help would be awesome

Thanks

What's the different between this question and your [previous](https://stackoverflow.com/questions/58013517/how-can-i-add-to-my-list-empty-objects-based-on-week-number) one? — mm8, Sep 20 '19 at 09:22
@mm8 previous was about full year and you provided answer, here I want something similar but only for months that are returned in data. If that are 3 months than lets fill missing weeks for those 3 months.. and thank you mm8 for all effort in answerin question in a such a deatiled way .. you are great :) — Roxy'Pro, Sep 20 '19 at 12:13

score 1 · Accepted Answer · answered Sep 20 '19 at 12:59

You could group by month and then create a productsChartDTO[4] with an entry for each week:

var groupedData = query.GroupBy(x => (x.PaymentDate ?? DateTime.UtcNow).Month)
    .Select(month =>
    {
        var weeks = Enumerable.Range(1, 4).Select(x => new productsChartDTO() { Month = month.Key, Week = x, Amount = 0 }).ToArray();
        foreach (var date in month)
        {
            int week = GetWeekNumberOfMonth(date.PaymentDate ?? DateTime.UtcNow);
            weeks[week - 1].Amount += date.Amount;
        }
        return weeks;
    })
    .SelectMany(x => x);

check for this question mate, and thanks for anaswer! https://stackoverflow.com/questions/58024981/c-sharp-linq-group-allready-grouped-data/58025038#58025038 — Roxy'Pro, Sep 21 '19 at 09:23

score 0 · Answer 2 · answered Sep 19 '19 at 20:35

There might be some cleaner way to do it but this works.

    static void Main(string[] args)
    {
        var groupedData = new List<DemoObj>
            {
                new DemoObj { Amount = 11, Month = 1, Week = 1 },
                new DemoObj { Amount = 133, Month = 1, Week = 2 },
                new DemoObj { Amount = 323, Month = 1, Week = 3 },
                // Needs to add week 4
                new DemoObj { Amount = 2342, Month = 2, Week = 1 },
                // Needst to add week 2
                new DemoObj { Amount = 23433, Month = 2, Week = 3 }
                // Needs to add etc.. 
            };

        var fullData = AddMissingValues(groupedData);
    }

    private static IEnumerable<DemoObj> AddMissingValues(IEnumerable<DemoObj> valuesFromDb)
    {
        var results = valuesFromDb.ToList();
        var possibleMonths = new[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 };
        var possibleWeeks = new[] { 1, 2, 3, 4 };

        foreach (var possibleMonth in possibleMonths)
        {
            foreach (var possibleWeek in possibleWeeks)
            {
                if (results.Any(x => x.Month == possibleMonth && x.Week == possibleWeek) == false)
                {
                    results.Add(new DemoObj { Month = possibleMonth, Week = possibleWeek });
                }
            }
        }

        return results.OrderBy(x => x.Month).ThenBy(x => x.Week);
    }

    public class DemoObj
    {
        public decimal Amount { get; set; }
        public int Month { get; set; }
        public int Week { get; set; }
    }

How to ensure that there are 4 values added for each id

2 Answers2