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 would avoid turning anything into a string at the database. Group by r.SomeDateTime.Year and r.SomeDateTime.Month, ordering by year then month then name, select the year and the month separately (along with the count), and then if you need to do the projection to a string format back in the client code.

By the way, I suspect your second OrderBy should be a ThenBy - currently the Name is actually the most important thing you're ordering by.

Answer 2

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(...).