Geek Answers Handbook

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

I want to write a Linq to Sql query that counts both groups by username and DateTime. I want the DateTime format to be formatted as follows: "YYYY-MMM" (i.e. 2009-Mar).

I thought this would work, but Linq to Sql will not be able to parse the argument to the "ToString" format.

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.

+4
source share
2 answers

I wonder if you should do this "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 a string format, do it later in the user interface. Either by restoring DateTime , etc., Or using CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(...) .

+5
source

I would not turn anything into a string in the database. r.SomeDateTime.Year by r.SomeDateTime.Year and r.SomeDateTime.Month , sorted by year, then month, then specify the year, month, and month (along with the count), and then, if you need to project in string format, back to the client code.

By the way, I suspect your second OrderBy should be ThenBy - currently, Name is actually the most important thing you order.

+3
source

More articles:


All Articles