博客左边的存档相信大家都很常见到吧。
我写了句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。