LINQ To实体INNER与COUNT联接

Question

我正试图使用理解方法在LINQ中复制以下查询。我的SQL如下：

SELECT COUNT(Post.Id), User.Id, User.Name
FROM Post  
INNER JOIN User ON Post.ModeratorId = User.Id
WHERE Post.Status IN ("Live", "Pending", "Expired")
GROUP BY User.Id, User.Name

我的LINQ查询如下，但当没有为帖子分配主持人时，它仍然返回0计数。请注意Post.ModeratorId是一个可以为null的值。

我只想要一份主持人名单和他们正在或已经主持的帖子数。如何在LINQ中复制上述SQL？

    public IEnumerable<ModeratorPostViewModel> GetModeratorPostCount()
    {
        var posts = _context.Post
                       .Include(p => p.Moderator)
                       .Include(p => p.Status)
                       .Where(p => p.ModeratorId != null && p.Status.Name IN ("Live", "Pending", "Expired"))
                       .OrderBy(p => p.Moderator.Name)
                       .Select(p => new ModeratorPostViewModel 
                       {
                           Id = p.ModeratorId,
                           Name = p.Moderator.Name,
                           CountOfPosts = p.Moderator.ModeratorPosts.Count()
                       })
                       .ToList();
        // Return list
        return posts 
    }

我的模型定义如下：

public class Post
{
   int Id { get; set; }
   int StatusId { get; set; }
   string ModeratorId { get; set; }
   // Navigation properties
   public Status Status { get; set; }
   public ApplicationUser Moderator { get; set; }
   // some other other properties
}
public class ApplicationUser : IdentityUser
{
    public string Name { get; set; }
    // Navigation property
    public ICollection<Post> ModeratorPosts { get; set; }
}

Answer 1

我只想要一个主持人列表和他们是或已经主持的帖子数

然后根据Moderator（或任何它的名称）实体进行查询：

var statuses = new [] { "Live", "Pending", "Expired" };
var posts = _context.Moderator
    .OrderBy(m => m.Name)
    .Select(m => new ModeratorPostViewModel 
    {
        Id = m.Id,
        Name = m.Name,
        CountOfPosts = m.ModeratorPosts.Count(p => statuses.Contains(p.Status.Name))
    })
    .Where(m => m.CountOfPosts != 0)
    .ToList();

UPDATE：我不得不承认，上面的LINQ查询并不能生成很好的SQL，尤其是在最后一个Where条件下。因此，您可能会使用与SQL查询完全等效的LINQ（您错过了GROUP BY部分）：

var statuses = new [] { "Live", "Pending", "Expired" };
var posts = _context.Post
    .Where(p => p.ModeratorId != null && statuses.Contains(p.Status.Name))
    .GroupBy(p => new { Id = p.ModeratorId, Name = p.Moderator.Name })
    .Select(g => new ModeratorPostViewModel 
    {
        Id = g.Key.Id,
        Name = g.Key.Name,
        CountOfPosts = g.Count()
    })
    .OrderBy(m => m.Name)
    .ToList();

Answer 2

根据您正在查找的状态过滤帖子后，您可以进行分组

var s = new List<string>() {"Live", "Pending", "Expired"};
var grouped = db.Post.Where(x => s.Contains(x.Status)) //Fitler for the statuses
                 .GroupBy(f => f.ModeratorId, po => po,
                              (k, items) => new ModeratorPostViewModel
                                            {
                                               Name = items.FirstOrDefault().User.Name,
                                               Id=k, 
                                               CountOfPosts = items.Count()
                                            }).ToList();