How to return a child collection with joins linq query

Question

I'm trying to figure out how to get a sub-collection, within a linq query. Basically I have courses, which have multiple schedules, but when I try the query below, it returns multiple objects, for each course, because the schedules are not a collection.

 public JsonResult Required(int programId, int departmentId)
 {
        var courses = (from r in context.ProgramRequiredCourses
                       join c in context.IAUClasses on r.ClassId equals c.id
                       join s in context.IAUClassSchedules on c.id equals s.classId
                       join d in context.IAUDepartments on c.mainDepartmentId equals d.id                          
                       where r.ProgramId == programId && d.id == departmentId
                       select  new RequiredCourse{
                            Department = d,
                            Course = c,
                            Schedule = s
                       }).AsEnumerable().Distinct(new DistinctCourseComparer());
        return Json(courses, JsonRequestBehavior.AllowGet);
  }

**Supporting code **

I know the Schedule in the RequiredCourse class should be changed to an IEnumerable, but not sure how to go about populating it.

public class RequiredCourse
{
    public IAUClass Course { get; set; }
    public IAUClassSchedule Schedule { get; set; }
    public IAUDepartment Department { get; set; }
}

public class DistinctCourseComparer : IEqualityComparer<RequiredCourse>
{
    public bool Equals(RequiredCourse x, RequiredCourse y)
    {            
        return x.Course.id == y.Course.id &&
               x.Course.className == y.Course.className;
    }

    public int GetHashCode(RequiredCourse obj)
    {
        return obj.Course.id.GetHashCode() ^
               obj.Course.className.GetHashCode();
    }
}

Your query would return multiple distinct RequiredCourse. So basically you want to return multiple distinct Schedules instead? — Hatjhie
No I need multiple distinct courses that each have a collection of schedules. Makes sense? The RequiredCourse is just an object to hold the results of each course, so it should have a course object, a department object and a collection of schedule objects, for each course. — Carl Weis

Cory Nelson Cory Nelson · Accepted Answer · 2014-09-25T03:26:16

It sounds like you're asking for a group join:

from r in context.ProgramRequiredCourses
join c in context.IAUClasses on r.ClassId equals c.id
join s in context.IAUClassSchedules on c.id equals s.classId into classSchedules
join d in context.IAUDepartments on c.mainDepartmentId equals d.id
where r.ProgramId == programId && d.id == departmentId
select new
{
    Department = d,
    Course = c,
    Schedules = classSchedules
}

Take special note of the into classSchedules part of the query. Instead of outputting a new row for each schedule, it will group all the schedules together, per class, into the IEnumerable<IAUClassSchedule> classSchedules

How to return a child collection with joins linq query

1 Answers