Linq to Sql - DateTime Format - YYYY-MMM (2009-Mar)

Question

I want to write a Linq to Sql query that does a count and groups by a Username and DateTime. I want the DateTime to be formatted like following "YYYY-MMM" (i.e. 2009-Mar).

I thought this would work but Linq to Sql can't parse the ToString "format" argument.

            dc.MyTable
              .GroupBy(r => new
              {
                  Name = r.UserName,
                  YearMonth = r.SomeDateTime.ToString("yyyy-MMM")
              })
              .Select(r => new
              {
                  Name = r.UserName,
                  YearMonth = r.YearMonth,
                  Count = r.Count()
              })
              .OrderBy(r => r.YearMonth)
              .ThenBy(r => r.Name);

Does anyone have any thoughts/suggestions?

Thanks.

Answer 1

I wonder if you shouldn't do that the "long" way...

          dc.MyTable
          .GroupBy(r => new
          {
              Name = r.UserName,
              Year = r.SomeDateTime.Year,
              Month = r.SomeDateTime.Month
          })
          .Select(r => new
          {
              Name = r.UserName,
              Year = r.Year,
              Month = r.Month,
              Count = r.Count()
          })
          .OrderBy(r => r.Year)
          .ThenBy(r => r.Month)
          .ThenBy(r => r.Name);

If you need the format as a string, do that later at the UI. Either by reconstructing a DateTime etc, or using CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(...).