ASP.NET MVC中通过Json向分部视图返回列表并展示分类文章计数的实现

需求为获取分类（Categories）列表，同时展示每个分类下匹配的文章记录数量。已在控制器中准备了Json数据，此前仅通过foreach返回分类列表，现在需要同时展示文章计数，已创建SQL View关联分类与文章（posts）表。

初始问题代码 #

初始ViewModel #

public class PostsInCategories
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string UrlSlug { get; set; }
}

初始待优化控制器代码 #

需要补充UrlSlug字段到返回结果：

public ActionResult Category()
{
    //List<CategoryViewModel> Category = new List<CategoryViewModel>();
    //Category = db.Categories.Where(k => k.IsDelete != true).OrderByDescending(a => a.CreatedDate).ToList().Select(a => a.ToCategoryModel()).ToList();
    var model = (from c in db.PostsInCategories
                 group c by c.Name into g
                 orderby g.Count() descending
                 select new
                 {
                     Cat = g.Key,
                     CountPosts = g.Count()
                 }).ToList();

    //return PartialView(@"~/Views/Blog/Partials/Category.cshtml", Category);
    return Json(new { result = model }, JsonRequestBehavior.AllowGet);
}

初始视图代码 #

需要保留原有样式，同时改造为支持接收包含计数和UrlSlug的数据集：

@model  IEnumerable<MyProject.Models.CategoryViewModel>
<ul class="list list-border angle-double-right">
@foreach (var item in Model)
{
    <li>
        <a href="/Blog/Category/@item.UrlSlug/@item.Id">
            @Html.DisplayFor(modelItem => item.Name)
            <span>(19)</span>
        </a>
    </li>
}
</ul>

初始SQL View脚本 #

SELECT        dbo.Category.Name, dbo.Post.Id, dbo.Post.UrlSlug
FROM          dbo.Category LEFT OUTER JOIN
              dbo.Post ON dbo.Category.Id = dbo.Post.CategoryId

最终实现代码 #

1. 新建ViewModel #

public class CategoryListViewModel
{
    public int Id { get; set; }
    public string Category { get; set; }
    public string UrlSlug { get; set; }
    public int CountPosts { get; set; }
    public int PostId { get; set; }
}

2. 优化后控制器代码 #

新增PostId字段，即使分类下没有关联文章也会返回该分类：

public ActionResult Category()
    {
        var model = (from c in db.PostsInCategories
                     group c by c.Name into g
                     orderby g.Count() descending
                     select new CategoryListViewModel
                     {
                         Category = g.Key,
                         Id = g.FirstOrDefault().Id,
                         UrlSlug = g.FirstOrDefault().UrlSlug,
                         PostId = g.FirstOrDefault().PostId,
                         CountPosts = g.Count()

                     }).ToList();

        return View(model);
    }

3. 优化后分部视图代码 #

保留原有样式，新增文章计数展示逻辑，无文章的分类计数显示为0：

<ul class="list list-border angle-double-right">
@foreach (var item in Model)
{
    <li>
        <a title="@item.Category Posts" href="/Blog/Category/@item.UrlSlug/@item.Id">
            @Html.DisplayFor(modelItem => item.Category)
        </a>
            <span title="Number of Posts">( @if (item.PostId != 0)
            {  @item.CountPosts }
            else
            { <text>0</text>} )</span>
    </li>
}
</ul>

4. 修正后SQL View代码 #

用ISNULL处理无文章分类的PostId字段，空值默认返回0：

SELECT  dbo.Category.Id, dbo.Category.Name, dbo.Category.UrlSlug, ISNULL(dbo.Post.Id, 0) AS PostId
FROM    dbo.Category LEFT OUTER JOIN
        dbo.Post ON dbo.Category.Id = dbo.Post.CategoryId

内容的提问来源于stack exchange，提问作者S.Purtan