Count posts by tags even for tags that have not posts

Question

In Entity Framework Core I have 3 entities:

public class Post { 
  public Int32 PostId { get; set; }
  public virtual ICollection<PostTag> PostTags { get; set; } 
}

public class PostTag { 
  public Int32 PostId { get; set; } 
  public Int32 TagId { get; set; } 
  public virtual Post { get; set; }
  public virtual Tag { get; set; }
}

public class Tag { 
  public Int32 TagId { get; set; }
  public virtual ICollection<PostTag> PostTags { get; set; } 
}

I need to get the Post Count per Tag so I tried:

IQueryable<PostTag> postTags = context.PostTags.AsNoTracking();

var stats = await postTags
  .GroupBy(x => x.Tag)
  .Select(x => new {
    TagId = x.Key.Id,
    TagName = x.Key.Name
    PostCount = x.Count()
  }).ToListAsync();

The problem is that Tags with not posts to not appear ...

And I would like them to appear with PostCount = 0.

How can I do this?

Answer 1

The problem as I see it is that you are starting from PostTags, and presumably there aren't any records in PostTags in this case; it seems like you want to start from Tags, then left-outer through to Posts. Actually: assuming you have referential integrity, you shouldn't even need to get to Posts - PostTags should be enough. So... perhaps just:

await context.Tags.AsNoTracking().Select(x => {
   x.TagId, PostCount = x.PostTags.Count() }).ToListAsync();

(sorry if the LINQ is rusty - - personally I'd just write it as SQL, so I know exactly what SQL is being executed)