在LINQ查询中应用group by，将丢失进一步连接所需的信息

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:

我在一个LINQ查询中需要以下数据:

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

我没有能够获取这些数据使用一个LINQ查询,因为在应用组了课程(课程和Course_TutionCenter)的加入,informtation得到的总学生数在所有Tution中心丢失,因为我们没有几个Tution中心在应用组的id了课程。Id在join Course和Course _tutioncenter中

Can this be done using single LINQ query?

这可以通过使用单个LINQ查询完成吗?

2 个解决方案

#1

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

#2

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

我将以最自然的方式编写LINQ查询来描述预期的结果，并让ORM为我处理SQL生成(使用连接或任何需要的东西)。

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:

在您的情况下，IMO不需要GroupBy，从课程表(已经包含分组信息)开始，使用导航属性从其他表中获取所需的聚合来进行过滤:

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

#1