How to get rows of Data from Multiple Tables using LinQ To Entities

Question

I am using dbcontext Code first to get a query base on this condition for the Classes (tables) below:

Creator != null && ArticleAttached != null && !IsCancelled

var ArticleStudentLiked = dbcontext.LearningActivites
  .Where(la => la.Creator != null && la.ArticleAttached != null && !la.IsCancelled)
  .Sum(la => la.ArticleAttached.StudentsLiked.Count);

  var NewsArticleComment = dbcontext.LearningActivites
  .Where(la => la.Creator != null && la.ArticleAttached != null && !la.IsCancelled)
  .Sum(la => la.ArticleAttached.Comments.Count);

The following Methods only return count for:

• ArticleStudentLiked
• ArticleComment

I need to get rows of record from the Queries into a single collection which I can pass to View to display line by line

like this :

Article Title, No. of Likes, No. Of Comments

How to use LinQ to Get these : Article Title,No. Of Like , No. of Comment

Classes:


 public class LearningActivity
 {
   public virtual ArticleCreator Creator { get; set; }        
   public virtual ArticleCreator EditedBy { get; set; }        
   public virtual Teacher CreatedByTeacher { get; set; }   
   public virtual Article ArticleAttached { get; set; }   
   public virtual Article ArticleAttachedByOther { get; set; } 
   public bool IsCancelled { get; set; }
 }


 public class Article 
 {
   public string ArticleTitle {get;set;}
   public virtual IList<Teacher> TeachersLiked { get; set; }
   public virtual IList<Student> StudentsLiked { get; set; }
   public virtual IList<ArticleComment> Comments { get; set; }
 }


 public class Student
 {
   public virtual IList<ArticleCommentStudent> Comments { get; set; }
 }

Thanks

Answer 1

Can you try this

// LikeCount is total of Teacher and Student Likes 
// and where clause can be added before Select
var result = dbcontext.Classes
            .Select(x=> new { ArticleTitle = x.ArticleTitle, 
                              LikeCount = x.TeachersLiked.Count() + x.StudentsLiked.Count(), 
                              CommentCount= x.Comments.Count }).First();