How add missing sequence in a list based on month and year?

Let's suppose we have those values from a database :

Month: 5, Total: 100, Year: 2019, Id: 1
Month: 7, Total: 45, Year: 2019, Id: 1
Month: 5, Total: 55, Year: 2019, Id: 2
Month: 9, Total: 110, Year: 2019, Id: 2

Then I have to generate rows for Months 6 and 8 that are missing from the list with Total : 0 for Id: 1 and Id: 2

The final result should be:

Month: 5, Total: 100, Year: 2019, Id: 1
Month: 6, Total: 0, Year: 2019, Id: 1
Month: 7, Total: 45, Year: 2019, Id: 1
Month: 5, Total: 55, Year: 2019, Id: 2
Month: 6, Total: 0, Year: 2019, Id: 2
Month: 7, Total: 0, Year: 2019, Id: 2
Month: 8, Total: 0, Year: 2019, Id: 2
Month: 9, Total: 110, Year: 2019, Id: 2

How can I do that with LINQ and C#? It's even harder if I think about the end of the year, e. g: Month 12, Year: 2019 and Month 2, Year: 2020.

Thank you for any help!

Solution

You want to group the records by (id and year) and find the boundary (first month and last month) in each group to fill the blanks:

    db.NAME_OF_THE_TABLE
        .ToArray() // materialize the query, since this group by is unlikely to be supported by linq2...
        .GroupBy(x => new { x.Id, x.Year }, (k, g) =>
        {
            var months = new { First = g.Min(x => x.Month), Last = g.Max(x => x.Month) };

            // left join to fill the blank between the records
            return (
                from month in Enumerable.Range(months.First, months.Last - months.First + 1)
                join row in g on month equals row.Month into match
                from x in match.DefaultIfEmpty(new TYPE_OF_RECORD { Id = k.Id, Year = k.Year, Month = month, Total = 0 })
                select x
            );
        })
        .SelectMany(g => g) // flatten the grouping