Format date with leading zeros in EF Core Linq GroupBy statement

Question

I've a LINQ statement (EF Core 3.1) where I want to group by year and month of a timestamp column, e.g. "2020-03".

var result = _context.Messages
            .Where(x => x.timestamp != null)
            .GroupBy(x => x.timestamp.Value.Year.ToString()+"-" + x.timestamp.Value.Month.ToString())
            .Select(x => new { date = x.Key, count = x.Count() })

The problem is that the result format of date is "2020-3" which causes in later sorting problems.

How can I format the month string to always have 2 digits with a leading zero?

I read a lot about SqlFunctions - but these are not available in EF Core. Are there any other ways?

Answer 1

You could group by the actual year/month and then project out those values. This way the grouping gets done entirely in SQL. Once you have the in-memory collection, you could project again creating your sort key along with the D2 format specifer

var result = _context.Messages
            .Where(x => x.timestamp != null)
            .GroupBy(x => new { 
                x.timestamp.Value.Year,
                x.timestamp.Value.Month
             })
            .Select(x => new { 
                Year = x.Key.Year, 
                Month = x.Key.Month, 
                Count = x.Count() 
             })
            .AsEnumerable()
            .Select(x => new {
                Date = $"{x.Year:D2}-{x.Month:D2}",
                Count = x.Count
             }) 
            .ToList();