group by Year(CreatedDate), Month(CreatedDate) 用sql, entity sql （linq表达式好像挺复杂）

博客左边的存档相信大家都很常见到吧。
我写了句sql语句，希望取出年份，月份，记录数。

select 

    Year(CreatedDate) as Year,

    Month(CreatedDate) as Month,

    Count(PostId) as Count

from

    Post

group by

    Year(CreatedDate),

    Month(CreatedDate)

order by

    Year desc, Month desc

然后我希望在Entity Framework里实现，我用Entity SQL实现了下。

select 

    Year,

    Month,

    Count(it.PostId) as Count

from

    Entities.Post as it

group by

    Year(it.CreatedDate) as Year,

    Month(it.CreatedDate) as Month

order by

    Year desc, Month desc

生成SQL语句

SELECT 

[Project1].[C4] AS [C1], 

[Project1].[C1] AS [C2], 

[Project1].[C2] AS [C3], 

[Project1].[C3] AS [C4]

FROM ( SELECT 

    [GroupBy1].[K1] AS [C1], 

    [GroupBy1].[K2] AS [C2], 

    [GroupBy1].[A1] AS [C3], 

    1 AS [C4]

    FROM ( SELECT 

        [Extent1].[K1] AS [K1], 

        [Extent1].[K2] AS [K2], 

        COUNT([Extent1].[A1]) AS [A1]

        FROM ( SELECT 

            DATEPART (year, [Extent1].[CreatedDate]) AS [K1], 

            DATEPART (month, [Extent1].[CreatedDate]) AS [K2], 

            [Extent1].[PostId] AS [A1]

            FROM [dbo].[aurora_Blogs_Post] AS [Extent1]

        )  AS [Extent1]

        GROUP BY [K1], [K2]

    )  AS [GroupBy1]

)  AS [Project1]

ORDER BY [Project1].[C1] DESC, [Project1].[C2] DESC

如果按上面的思路用linq表达式发现要group by两次，貌似很复杂。
换个算法。

from p in posts

let m = new ArchiveDateTime { Year = p.CreatedDate.Year, Month = p.CreatedDate.Month }

group p by m into months

orderby months.Key descending

select new ArchiveData { Year = months.Key.Year, Month = months.Key.Month, PostCount = months.Count() };

Entity Framework不支持带参构造函数。所以你不能使用用let m = new DateTime(p.CreatedDate.Year, p.CreatedDate.Month, 1)

所以只能自己新建一个ArchiveDateTime。