Join on top of a GroupJoin

Question

I'm trying to join an extra table on top of my group join. The code below works fine, but the commented part (which is of course invalid) hopefully gives you the idea that I want to join Category on ArticleCategories. This mirrors the DB so ArticleCategory has two foreign keys to Article (ArticleId) and to Category (CategoryId) (unpluralized).

var sql = from a in db.Articles
    join ac in db.ArticleCategories on a.ArticleId equals ac.ArticleId into acGrouped
    //join c in db.Categories on acGrouped.CategoryId = c.CategoryId (I want to join within the group)
    select new 
    {
        a,
        acGrouped
    };

I want access to the CategoryName from the Category table when I loop through the results adding them to models. On the first article:

sql.First().a will give what I need from the first article

sql.First().acGrouped will give me a collection of ArticleCategories from the first article

but how to I get the Category per each ArticleCategories? Something like this: sql.First().acGrouped.First().Categories.CategoryName or sql.First().acGrouped.First().CategoryName where "CategoryName" is only in Categories which would give me the first category name of the first article. (assuming the collection isn't empty)

This would be trivial with flat results, but I was hoping to output the results per each article.

Answer 1

If I'm understanding your post correctly, you want to be able to list all Categories related to an Article where you have a junction table ArticleCategory that contains the relationships. The following should work:

var articles = db.Articles;
var categories = db.Categories;
var articleCategories = db.ArticleCategories;

var query = from ac in articleCategories
    join a in articles on ac.ArticleId equals a.ArticleId
    join c in categories on ac.CategoryId equals c.CategoryId
    group c by a.ArticleName into g
    select new { ArticleName = g.Key, Categories = g.ToList() };

Here is a Fiddle showing the results: https://dotnetfiddle.net/VYBIfY

Update

If you' like to get more properties off of Article for example ArticleId, change the group by key to a new object that has the properties you want, like so:

var query = from ac in articleCategories
    join a in articles on ac.ArticleId equals a.ArticleId
    join c in categories on ac.CategoryId equals c.CategoryId
    group c by new { a.ArticleId, a.ArticleName } into g
    select new { Article = g.Key, Categories = g.ToList() };

Answer 2

You should be joining your article categories to the categories before you group join it to articles, so that the data is already joined by the time you've grouped it in your group join..