LINQ query in Entity framework 6

Question

I have Articles in my database. Every Article has Category property. I want to dynamically generate menu which is based on categories and articles like that:

-CategoryName1  
|-Article1  
|-Article2  
-CategoryName2  
|-Article3  
|-Article4

etc...

I want to create simple viewModel with this structure to dynamicaly create menu on my View.

There are condition also like Article.IsDeleted and Category.IsDeleted must be false. The second condition is that the generated model shouldn't contain categories which doesn't have at least 1 related article.

But I dont know how to do this, maybe by LINQ?

I thought that this could be my viewModel (or you could propose better, more optimal?)

public class SideMenuViewModel
{
    public SideMenuViewModel()
    {
        this.MenuLinks = new List<CategoryLink>();
    }

    public IList<CategoryLink> MenuLinks { get; set; }
}

public class ArticleLink
{
    public string ArticleName { get; set; }

    public string ArticleUrl { get; set; }
}

public class CategoryLink
{
    public CategoryLink()
    {
        this.ArticleLinks = new List<ArticleLink>();
    }

    public IList<ArticleLink> ArticleLinks { get; set; }

    public string CategoryName { get; set; }

    public string CategoryUrl { get; set; }
}

Action:

    [ChildActionOnly]
    public ActionResult SidebarMenu()
    {
        SideMenuViewModel model = new SideMenuViewModel();

        var categories = db.Articles
            .Where(x => !x.IsDeleted && !x.Category.IsDeleted && x.Category.Website.Id == WebsiteHelper.CurrentWebsiteId).Include(x => x.Category)
            .Select(x => new CategoryLink() { CategoryName = x.Category.Name, CategoryUrl = x.Category.Name })
            .GroupBy(x => x.CategoryName)
            .Select(x => x.First());

        foreach(CategoryLink item in categories)
        {

        }
        //Get the menuItems collection from somewhere
        return PartialView("_SidebarMenu", model);
    }

If there are better ways than LINQ, please write them too :)

Answer 1

In this case it is easier to use Category.Articles:

from c in db.Categories.Where(c => !c.IsDeleted)
let activeArticles = c.Articles.Where(a => !a.IsDeleted)
where activeArticles.Count() > 1
select new CategoryLink
       {
          c.CategoryName,
          c.CategoryUrl,
          ArticleLinks = activeArticles.Select(a1 => new ArticleLink
                                                    {
                                                        a1.ArticleName,
                                                        a1.ArticleUrl
                                                    })
       }