Question: Applying group by in a LINQ Query, the information required in further join is lost

Question

Applying group by in a LINQ Query, the information required in further join is lost

Answers 2
Added at 2016-12-31 12:12
Tags
Question

I have the following models:

public class Course
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Course_TutionCenter
{
    public int Id { get; set; }
    public int CourseId { get; set; }
    public int TutionCenterId { get; set; }

    [ForeignKey("CourseId")]
    public virtual Course Course { get; set; }

    [ForeignKey("TutionCenterId")]
    public virtual TutionCenter TutionCenter { get; set; }
}

public class CourseStudent
{
    public int Id { get; set; }
    public int Course_TutionCenter_Id { get; set; }

    [ForeignKey("Course_TutionCenter_Id")]
    public virtual Course_TutionCenter Course_TutionCenter { get; set; }
}

Each Course is taught in many Course_TutionCenter and each Course can have many CourseStudent which are distributed over several Course_TutionCenter.

I need the following data in a single LINQ query:

CourseId, Course Name, Number of Tution Centers Offering This Course, Number of Students taking this Course over all Tution Centers

I have not able to fetch this data using a single LINQ query, because in applying group by over Course in the join of (Course and Course_TutionCenter), the informtation to get the count of Total students over all Tution Centers is lost, because we don't have the IDs of several Tution Centers after applying group by over Course.Id in the join of Course and Course_TutionCenter

Can this be done using single LINQ query?

Answers to

Applying group by in a LINQ Query, the information required in further join is lost

nr: #1 dodano: 2016-12-31 12:12

I think that something like the following does what you are looking for:

var result = from course in DbCourses
             left join courseTutionCenter in DbCourseTutionCenter
             on course.Id equals courseTutionCenter.CourseId 
             left join courseStudent in DbCourseStudent
             on courseStudent.Course_TutionCenter_Id equals courseTutionCenter.TutionCenterId  
             group by new { course.Id, course.Name } into gr
             select new
             {
                 CourseId = gr.Key.Id,
                 CourseName = gr.Key.Name,
                 TutionCenters = gr.GroupBy(x=>x.courseStudent.Course_TutionCenter_Id).Count(),
                 Students = gr.GroupBy(x=>x.courseStudent.Id).Count()
             };
nr: #2 dodano: 2016-12-31 12:12

I would write the LINQ query in a most natural way describing the intended result and let ORM handle the SQL generation for me (using joins or whatever is needed).

In your case IMO no GroupBy is needed, start with Course table (already contains the grouped information) and just take the aggregates needed from the other tables using the navigation properties for filtering:

var query = db.Course.Select(c => new
{
    CourseId = c.Id,
    CourseName = c.Name,
    NumberOfTutionCenters = db.Course_TutionCenter
        .Count(tc => tc.CourseId == c.Id),
    NumberOfStudents = db.CourseStudent
        .Count(s => s.Course_TutionCenter.CourseId == c.Id),
});
Source Show
◀ Wstecz