Thursday 21 January 2016

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

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);

No comments:

Post a Comment

c++ - Does curly brackets matter for empty constructor?

Those brackets declare an empty, inline constructor. In that case, with them, the constructor does exist, it merely does nothing more than t...