CODEWITHSUNDEEP
c#sql-serverlinq
Chad
Chad

Reputation: 396

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

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.

Upvotes: 4

Views: 3891

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1064204

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

Upvotes: 5

Jon Skeet
Jon Skeet

Reputation: 1503859

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.

Upvotes: 3

Related Questions