I'm new to C#.NET. I'm trying to group the result of my GET request by the article, when I try to use groupby, I get an error.

This is my original code without grouping.

//GET api/Articles 
[HttpGet] 
public async Task<ActionResult<IEnumerable<AuthorTagArticle>>> GetArticles() 
{
    return await _context.AuthorArticles
                         .Join(_context.TagArticles,
                               article => article.ArticleId,
                               tag => tag.ArticleId,
                               (article, tag) => new AuthorTagArticle
                                                     {
                                                         Article =  article.Article,
                                                         Author = article.Author,
                                                         Tag = tag.Tag
                                                     })
                         .ToListAsync(); 
}

This is the error I get:

The result:

I would to do something like this....

{
"article": {
    "articleId": 1,
    "articleTitle": "Article1",
    "articleContent": null,
    "createdOn": "0001-01-01T00:00:00"
},
"author": [{
    "userId": 1,
    "userName": "User 1",
    "userRole": "STAFF"
},{
    "userId": 2,
    "userName": "User 2",
    "userRole": "STAFF"
},{
    "userId": 3,
    "userName": "User 3",
    "userRole": "STAFF"
}]
"tag": [{
    "tagId": 2,
    "tagName": "Breaking News",
    "tagColorCode": "#FF0000"
},{
    "tagId": 7,
    "tagName": "Economics",
    "tagColorCode": "#7100FF"
}]
},{
"article": {
    "articleId": 2,
   ….

Database Diagram Image

Model classes:

public class AuthorTagArticle
{ 
    public Article Article { get; set; }
    public User Author { get; set; }
    public Tag Tag { get; set; }
}

public class AuthorArticle
{
    [Key]
    [Column(Order=1)]
    public int ArticleId { get; set; }

    [Key]
    [Column(Order = 2)]
    public int AuthorId { get; set; }

    public virtual Article Article { get; set; }
    public virtual User Author { get; set; }
}

public class TagArticle
{
    [Key]
    [Column(Order = 1)]
    public int ArticleId { get; set; }

    [Key]
    [Column(Order = 2)]
    public int TagId { get; set; }

    public virtual Article Article { get; set; }
    public virtual Tag Tag { get; set; }
}

public class Article
{
    public int ArticleId { get; set; }
    public string ArticleTitle { get; set; }
    public string ArticleContent { get; set; }
    public DateTime CreatedOn { get; set; }

    [JsonIgnore] 
    public virtual ICollection<AuthorArticle> AuthorArticles { get; set; }
    [JsonIgnore]
    public virtual ICollection<TagArticle> TagArticles { get; set; }
}

public class Tag
{
    public int TagId { get; set; }
    public string TagName { get; set; }
    public string TagColorCode { get; set; }
    [JsonIgnore]
    public virtual ICollection<TagArticle> TagArticles { get; set; }
}

public class User
{        
    public int userId { get; set; }
    public string userName { get; set; }
    public string userRole { get; set; }

    [JsonIgnore]
    public virtual ICollection<AuthorArticle> AuthorArticles { get; set; }
}

public class NewsContext : DbContext
{
    public NewsContext(DbContextOptions<NewsContext> options) : base(options) {}

    public DbSet<Article> Articles { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<AuthorArticle> AuthorArticles { get; set; }
    public DbSet<TagArticle> TagArticles { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {....}
 }

CodePudding user response：

As I suspected, you have all the many-to-many navigations you need to simply .Select or .Include everything, by starting your query from _context.Articles.

await _context.Articles
    .Where(...)
    .Select(article => new AuthorTagArticle
        {
            Article = article,
            Author = article.AuthorArticles.Select(a => a.Article).ToList(),
            Tag = article.TagArticle.Select(t => t.Tag).ToList()
        })
    .ToListAsync();

This would be slightly simpler if you used the many-to-many relationship (aka skip navigation), added in EF Core 5. Since you'd have a navigation property for the Author and Tag collections that you could assign directly.