创建文章存档列表

一般的来说博客都要有一个文章存档列表以方便浏览者访问过去的文章，像博客园、WordPress都了提供这种服务。

于是，我也在自己的NBlog中加入了这个功能。下面是代码：

public static IQueryable<Models.Archive> GetArchive()
{
    NBlogDataContext db = new NBlogDataContext();

    return from post in db.Posts
           group post by new { Year = post.CreateDate.Year, Month = post.CreateDate.Month }
                into YM
                orderby YM.Key.Year descending
                orderby YM.Key.Month descending
                select new Archive
                {
                    Title = YM.Key.Year + "年" + YM.Key.Month + "月",
                    Count = YM.Count(),
                    URL = string.Format("/{0}/{1}", YM.Key.Year, YM.Key.Month)
                };
}

这段代码就是将Posts表中的文章按创建 年月 分组、倒序，然后生成Archive对象。恩，蛮简单的。

查看了一下生成的SQL语句如下：

SELECT (( (CONVERT(NVarChar,[t2].[value2])) + @p0) 
		+ (CONVERT(NVarChar,[t2].[value22]))) + @p1 AS [Title]
		, [t2].[value] AS [Count]
		, [t2].[value2] AS [arg0], [t2].[value22] AS [arg1]
FROM (
    SELECT COUNT(*) AS [value], [t1].[value] AS [value2], [t1].[value2] AS [value22]
    FROM (
        SELECT DATEPART(Year,  [t0].[CreateDate]) AS [value]
		     , DATEPART(Month, [t0].[CreateDate]) AS [value2]
        FROM [dbo].[Posts] AS [t0]
        ) AS [t1]
    GROUP BY [t1].[value], [t1].[value2]
    ) AS [t2]
ORDER BY [t2].[value22] DESC, [t2].[value2] DESC
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [年]
-- @p1: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [月]

在页面端的调用

<h4>
    <span>穿越到过去，看以前的 <strong>文章</strong></span>
</h4>
<ul>
    @foreach (var archive in NBlog.BLL.SystemService.GetArchive())
    { 
        <li><a href="@archive.URL">@archive.Title (@archive.Count)</a></li>
    }
</ul>

看看效果吧。

基本完成我的要求了，不过有一个问题，就是当月的文章到底要不要算进去呢？？？

标签: LINQ，NBlog，SQL