如何使用实体框架对数据库中的嵌套集合进行计数

本文关键字：集合 嵌套 实体 何使用 框架 数据库 | 更新日期: 2023-09-27 18:32:41

我正在制作我的第一个 Asp.net Mvc 应用程序 - 论坛系统。我正在尝试显示子类别中有多少帖子和线程。

这是我的表格：

public class Category
{
   private ICollection<SubCategory> subCategories;
   public Category()
   {
       this.subCategories = new HashSet<SubCategory>();
   }
   public int Id { get; set; }
   public string Name { get; set; }
   public virtual ICollection<SubCategory> SubCategories
   {
       get { return this.subCategories; }
       set { this.subCategories = value; }
   }                          
}

public class SubCategory
{
    private ICollection<Thread> threads;
    public SubCategory()
    {
        this.threads = new HashSet<Thread>();
    }
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public int CategoryId { get; set; }
    public virtual Category Category { get; set; }
    public virtual ICollection<Thread> Threads
    {
        get { return this.threads; }
        set { this.threads = value; }
    }
}

public class Thread
{
    private ICollection<Post> posts;
    public Thread()
    {
        this.posts = new HashSet<Post>();
    }
    public int Id { get; set; }
    public string Title { get; set; }
    public virtual SubCategory SubCategory { get; set; }
    public int SubCategoryId { get; set; }
    public virtual ICollection<Post> Posts
    {
        get { return this.posts; }
        set { this.posts = value; }
    }
    public string AuthorId { get; set; }
    public virtual ApplicationUser Author { get; set; } 
}

public class Post
{
    public int Id { get; set; }
    public string Content { get; set; }
    public int ThreadId { get; set; }
    public virtual Thread Thread { get; set; }
    public string AuthorId { get; set; }
    public virtual ApplicationUser Author { get; set; }
}

这是我的观点：

    @model IEnumerable<ForumSystem.Web.ViewModels.Home.IndexCategoryViewModel>
@{
    ViewBag.Title = "Home Page";
}
<div class="container">
    @foreach (var category in Model)
    {
        <div class="row">
            <h5>@category.Name</h5>
            @foreach (var subCat in category.SubCategories)
            {
                <div class="col-md-10">
                    <div class="row">
                        <h7>
                            <a href="/SubCategory/@subCat.Title">@subCat.Title</a>
                        </h7>
                    </div>
                    <div class="row">
                        <p>@subCat.Description</p>
                    </div>
                </div>
                <div class="col-md-2">
                        <p>@category.ThreadsCount threads</p>
                        <p>@category.PostsCount posts</p>
                    <div class="row">
                    </div>
                </div>
            }
        </div>
    }
</div>

@category。ThreadsCount 和 PostsCount 不起作用。我可以使用 @subCategory.Threads.Count 在视图中轻松获得线程计数，但我无法获取帖子计数。在控制器中，我尝试了很多东西。此刻的代码是：

public ActionResult Index()
    {
        var threadScount = this.Data
                           .SubCategories
                           .All()
                           .SelectMany(x => x.Threads)
                           .Count();
        var postsCount = this.Data
                        .Threads
                        .All()
                        .SelectMany(x => x.Posts)
                        .Count();
        var model = this.Data
                    .Categories
                    .All()
                    .Select(c => new IndexCategoryViewModel
                    {
                        Name = x.Name,
                        SubCategories = c.SubCategories,
                        ThreadsCount = threadScount,
                        PostsCount = postsCount,
                    })
                    .ToList();

        return this.View(model);
    }

但这给了我所有线程和帖子计数，而不是特定于每个子类别。提前谢谢。

如何使用实体框架对数据库中的嵌套集合进行计数

创建表示要显示的内容的视图模型

public class SubCategoryVM
{
  public string Title { get; set; }
  public string Description { get; set; }
  public int ThreadsCount { get; set; }
  public int PostCount { get; set; }
}
public class CategoryVM
{
  public string Name { get; set; }
  public List<SubCategoryVM> SubCategories { get; set; }
}

控制器

public ActionResult Index()
{
  var model = this.Data.Categories.Select(c => new CategoryVM
  {
    Name = c.Name,
    SubCategories = c.SubCategories.Select(s => new SubCategoryVM
    {
      Title = s.Title,
      Description = s.Description,
      ThreadsCount = s.Threads.Count,
      PostsCount = s.Threads.SelectMany(t => t.Posts).Count;
    })
  });
  return View(model);
}

视图

@model IEnumerable<CategoryVM>
@{
  ViewBag.Title = "Home Page";
}
<div class="container">
  @foreach (var category in Model)
  {
    <div class="row">
      <h5>@category.Name</h5>
      @foreach (var subCategory in category.SubCategories)
      {
        <div class="col-md-10">
          <div class="row">
            <h7>
              // The following line in your view makes no sense
              // <a href="/SubCategory/@subCat.Title">@subCat.Title</a>
              // Assuming you have method: public ActionResult Details(string title) in SubCategoryController, then
              @Html.ActionLink(subCategory.Title, "Details", "SubCategory", new { title = subCategory.Title }, null)
            </h7>
          </div>
          <div class="row">
            <p>@subCategory.Description</p>
          </div>
        </div>
        <div class="col-md-2">
          <p><span>@subCategory.ThreadCount</span><span>threads</span></p>
          <p><span>@subCategory.PostCount</span><span>posts</span></p>
        </div>
      }
    </div>
  }
</div>
我相信

你要找的是这样的：

var model = this.Data
    .Categories
    .Select(c => new IndexCategoryViewModel
    {
        Name = c.Name,
        SubCategories = c.SubCategories,
        ThreadsCount = c.Threads.Count(),
        PostsCount = c.Threads.Sum(t => t.Posts.Count()),
    })
    .ToList();

在这里，您可以创建嵌套查询来计算每个类别的话题和帖子。您不需要threadsCount和postsCount，因为它们存储的是总体计数，而不是每个类别的计数。

注意

我假设Threads属性存在于类别类上，Posts属性存在于线程类上。 否则，您需要有谓词来关联帖子与话题以及话题与类别相关联。最常见的是 id，在这种情况下，代码将如下所示：

var model = this.Data
    .Categories
    .Select(c => new IndexCategoryViewModel
    {
        Name = c.Name,
        SubCategories = c.SubCategories,
        ThreadsCount = this.Data
            .Threads
            .Count(t => t.CategoryId == c.Id),
        PostsCount = this.Data
            .Posts
            .Count(p => this.Data
                .Threads
                .Any(t => p.ThreadId == t.Id && t.CategoryId == c.Id)
            ),
    })
    .ToList();

请注意，我还跳过了所有.All()调用，因为它们似乎是多余的，尽管我不确定它们的作用，因此您可以在必要时将其放回该位置。

试试这个，你还需要更改视图模型来反映需求

public ActionResult Index()
{
    var model = from subCategory in this.Data.SubCategories
                select new SubCategoryViewModel
                {
                    Name = subCategory.Category.Name,
                    SubCategory = subCategory,
                    ThreadsCount = subCategory.Threads.Count(),
                    PostsCount = subCategory.Threads.SelectMany(c => c.Posts).Count(),
                })
                .ToList();

    return this.View(model);
}
相关文章：