I have a table with a datetime field. I want to retrieve a result set grouped by the month/year combination and the number of records that appear within that month/year. How can this be done in LINQ?
The closest I've been able to figure out is in TSQL:
select substring(mo,charindex(mo,'/'),50) from (
select mo=convert(varchar(2),month(created)) + '/' + convert(varchar(4), year(created))
,qty=count(convert(varchar(2),month(created)) + '/' + convert(varchar(4), year(created)))
from posts
group by convert(varchar(2),month(created)) + '/' + convert(varchar(4), year(created))
) a
order by substring(mo,charindex(mo,'/')+1,50)
But I wouldn't say that works...