Querying many to many relationships

Question

I saw the post on relationships on the official asp.net core documentation: asp.net core model relationships

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public List<PostTag> PostTags { get; set; }
}

public class Tag
{
    public string TagId { get; set; }

    public List<PostTag> PostTags { get; set; }
}

public class PostTag
{
    public int PostId { get; set; }
    public Post Post { get; set; }

    public string TagId { get; set; }
    public Tag Tag { get; set; }
}

If I wanted to return a list of all the Tags, I would do this:

public async Task<IEnumerable<Tag>> AllTags()
{
return await _context.Tags
.Include(q => q.PostTags).ThenInclude(q => q.Post);
}

How can I return the Tags and number of posts they applied to?

Example =

Budget (3)

Answer 1

First create a view model class to represent the data you want to pass

public class PostCount
{
    public string Name { get; set; }
    public int Count { get; set; }
}

Now in your action method, you can query the Tags collection and and call the Count method on the PostTags property of each Tag object. In your LINQ expression, you can do a projection to create your view model object.

public async Task<IEnumerable<PostCount>> AllTags()
{
    var tags = await _context.Tags
                             .Select(a => new PostCount
                                          {
                                             Name = a.Name,
                                             Count = a.PostTags.Count()
                                          }
                                    ).ToListAsync();

    return tags;
}

The above will return the data you want when you access this action method.

If you are showing this in a view, you can return the list we created to your view. in your view, which is strongly typed to a list of PostCount class, you can render the tag name and it's post count

public async Task<IEnumerable<PostCount>> AllTags()
{
    var tags = await _context.Tags.Select(a => new PostCount
    {
        Name = a.Name,
        Count = a.PostTags.Count()
    }).ToListAsync();

    return View(tags);
}

and in your AllTags.cshtml view,

@model List<PostCount>
<h3>Tags</h3>
@foreach(var tag in Model)
{
  <p>@tag.Name  @tag.Count </p>
}

Also you probably need to have a primary key property in your PostTag class

public class PostTag
{
   public int Id  { set;get; }
   // other properties you have
}