在 LINQ 中复制 SQL 查询

Question

我有一个 ASP.NET MVC 应用程序，我已经将其与 LINQ to SQL 一起使用了一段时间。我已经掌握了在 LINQ 中复制大多数查询的窍门，但有一个查询已经让我难住了好几天了。

我正在尝试选择满足条件的“进度”列表，并且我有一个组列表。

我的ERD如下：

“组”1<->许多“注册”许多<->1“学生”1<->许多“进度”

标准 SQL 是（除了在代码中，我将一组特定的组传递给函数）：

SELECT     dbo.[Group].GroupID, COUNT(*) AS returning
FROM         dbo.[Group] INNER JOIN  
dbo.Enrolment ON dbo.[Group].CourseID = dbo.Enrolment.GroupID INNER JOIN    
dbo.Student ON dbo.Enrolment.StudentID = dbo.Student.StudentID INNER JOIN  
dbo.Progression ON dbo.Student.StudentID = dbo.Progression.StudentID  
WHERE     (dbo.Progression.IsReturning = 0)  
GROUP BY dbo.[Group].GroupID 

现在是 Web 应用程序。 ASP 视图“进度”传递变量“组”，它是几个选定组的列表。我目前正在使用以下代码，该代码非常慢（加载页面需要 30 秒或更长的时间），

<%foreach (var tg in Model)  
      {%>  
        <% notreturning = 0; %>  


        <%foreach (Enrolment e in tg.Enrolments)  
                  {  
                   notreturning = notreturning + e.Student.Progressions.Where(p => !p.IsReturning).Count();  

                  }%>    
        <tr>  
            <td><% = notreturning %></td>
        </tr>       
      <%   
      } %>  

我也在计算其他一些内容，但在这个例子中我将坚持使用一个。现在显然这非常慢，因为它必须对组进行 foreach，然后对组中的每个注册进行 foreach，因此大约 10 个组乘以每个组 20 名学生。我确实想做类似以下的事情，消除第二个 foreach：

<%foreach (var tg in Model)  
      {%>  
        <% notreturning = 0; %>  

         <%var test = tg.Enrolments.Where(e => e.Student.Progressions.Where(p => !p.IsReturning)).Count(); %>

        <tr>  
            <td><% = notreturning %></td>
        </tr>       
      <%   
      } %>  

该代码不起作用，因为嵌套的 where 子句不返回 bool 数据类型，但我希望它能完成我在这里尝试做的事情。

我不确定我是否解释得很好，但如果有人有任何想法，我将非常感激，这已经困扰我好几天了！

Answer 1

SQL 的字面转换类似于：

from g in db.Groups
join e in db.Enrolments on g.CourseID equals e.GroupID
join s in db.Students in e.StudentID equals s.StudentID
join p in db.Progressions on s.StudentID equals p.StudentID  
where p.IsReturning == 0  
GROUP new {
   Group = g,
   Enrolment = e,
   Student = s,
   Progression = p
} by g.GroupID into grouped 
select new
{
   GroupId = grouped.Key,
   Returning = grouped.Count()
};

虽然 g.CourseID equals e.GroupID 看起来有点奇怪！

---

顺便说一句，如果您的最终目标是选择进度列表，那么我发现最简单的方法是使用进度作为第一个选择的内容而不是使用组来启动查询。

Answer 2

此 LINQ 查询将执行您在注释中表达的操作：

var groups =
    from g in db.Groups
    let returningStudents =
        from enrolment in g.Enrolments
        let student = enrolment.Student
        where student.Progressions.Any(p => p.IsReturning)
        select student
    select new GroupStudentReturnCountDto
    {
        Name = g.Name,
        StudentReturnCount = returningStudents.Count()
    };

此查询将非常高效，因为它让数据库进行计数，并且仅返回实际使用的数据。如果它仍然不够快，只需添加正确的数据库索引即可完成;-)