Inner query(sub query) using LINQ to Entities that returns IEnumerable Lists within Lists

Question

I am trying to get lists of Articles from table Article with list of categories that each article is listed onto. Say Article "A" has 3 Categories "1","2","3" and I have more than one articles with more than one categories. I want to get list of CategoryViewModel within List of ArticleViewModel. since it is IEnumerableList, When I use FirstOrDefault() I get the same category names under each article, but I get the right number of articles. How could I get Category names (List) within the Articles(List).

ArticleViewModel Class

    public class ArticleViewModel
    {
        public int ArticleID { get; set; }
        public int AuthorID { get; set; }
        public string AuthorName { get; set; }
        public string Username { get; set; }
        public string Slug { get; set; }
        public string Title { get; set; }
        public string ArticleContent { get; set; }
        public DateTime PostDate { get; set; }
        public Nullable UpdatedDate { get; set; }
        public int ArticleCategoryID { get; set; }
        public int CategoryID { get; set; }
        public IEnumerable CategoryNames { get; set; }
        public int TagID { get; set; }
        public string TagName { get; set; }
    }

CategoryViewModelClass

    public class CategoryViewModel
    {
        public int CategoryID { get; set; }
        public string CategoryName { get; set; }
    }

ArticleCategory

 public partial class ArticleCategory
    {
        public int ArticleCategoryID { get; set; }
        public int ArticleID { get; set; }
        public int CategoryID { get; set; }

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

QUERY

result = (from articles in db.Articles
                join articlecategories in db.ArticleCategories on articles.ArticleID equals articlecategories.ArticleID
                join cat in db.Categories on articlecategories.CategoryID equals cat.CategoryID
                join auth in db.Authors on articles.AuthorID equals auth.AuthorID
                select new ArticleViewModel
                {
                    AuthorName = auth.AuthorName,
                    Username = auth.UserName,

                    CategoryNames = (from a in db.Articles
                                    //from ca in db.ArticleCategories
                                    //from c in db.Categories group c by c.CategoryName into group1
                                    join ca in db.ArticleCategories on articlecategories.ArticleID equals ca.ArticleID
                                    join c in db.Categories on cat.CategoryID equals c.CategoryID into group1                                               
                                    select new CategoryViewModel
                                    {                                                  
                                        CategoryName = (from group2 in group1 select group2.CategoryName).FirstOrDefault()
                                    //   CategoryID = (from group2 in group1 select group2.CategoryID).FirstOrDefault()
                                    }),

                    Title = articles.Title,
                    Slug = articles.Slug,
                    ArticleContent = articles.ArticleContent,
                    PostDate = articles.PostDate
                }).GroupBy(a => a.Title).Select(a => a.FirstOrDefault()).OrderByDescending(article => article.PostDate).ToList().ToPagedList(pageIndex, pageSize);

Alok Babu Alok Babu · Accepted Answer · 2015-02-15T14:38:35

Okay, after a few days. I realised that was trying get things overdone. My bad ! The fix is simple; change the Category to IEnumerable<string> Category {get; set;} in the view model.

and change the LinQ to

result = (from articles in db.Articles
                              join articlecategories in db.ArticleCategories on articles.ArticleID equals articlecategories.ArticleID
                              join cat in db.Categories on articlecategories.CategoryID equals cat.CategoryID
                              join auth in db.Authors on articles.AuthorID equals auth.AuthorID
                              join tags in db.ArticleTags on articles.ArticleID equals tags.ArticleID

                              select new ArticleViewModel
                              {
                                  ArticleID=articles.ArticleID,
                                  AuthorName = auth.AuthorName,
                                  Username = auth.UserName,
                                  Title = articles.Title,
                                  Slug = articles.Slug,
                                  ArticleContent = articles.ArticleContent,
                                  PostDate = articles.PostDate,
                                  CategoryNames = from icat in articles.ArticleCategories select icat.Category.CategoryName,
                                  Tags = from itags in articles.ArticleTags select itags.Tag.TagName

                              }).GroupBy(a => a.Title).Select(a => a.FirstOrDefault()).ToPagedList(pageIndex, pageSize);

Inner query(sub query) using LINQ to Entities that returns IEnumerable Lists within Lists

1 Answers