Replicating an SQL query in LINQ

I have an ASP.NET MVC application which I have being using with LINQ to SQL for a while now. I have got the hang of replicating most queries in LINQ but there is one that has had me stumped for several days 开发者_StackOverflow社区now.

I am trying to select a list of "Progressions" where a condition is met, and I have a list of groups.

My ERD is as follows:

"Group" 1<->Many "Enrolments" Many<->1 "Students" 1<->Many "Progressions"

And the standard SQL would be (Except in the code I have a specific set of groups passed to the function):

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 

Now for the Web App. The ASP view "Progression" gets passed the varibale "groups" which is a list of a few selected groups. I am currently using the follwing code, which is very slow (30 secs or more to load page)

<%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>       
      <%   
      } %>  

I am counting some other stuff too but for this example I'll stick to one. Now obviously this is quite slow because it has to do a foreach for groups, then for each enrolment in the group, so around 10 groups times 20 students in each. I deally I want to do something like the following which eliminates the second 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>       
      <%   
      } %>  

That code doesn't work as the nested where clause doesn't return a bool data type, but I hope it get accross what I'm trying to do here.

I'm not sure if I've explained this very well but if anyone has any ideas I would be very grateful, this has been bugging me for days!

A literal conversion of your SQL is something like:

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

although g.CourseID equals e.GroupID looks a bit odd!

As an aside, if your end goal is to select a list of Progressions, then I find it easiest to start the query with the Progressions as the first thing being selected rather than with the Groups.

This LINQ query would do what you expressed in the comments:

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

This query would be very efficient, because it lets the database do the counting and it returns only the data that is actually used. If it still isn't fast enough, just add the right databases indexes and you're done ;-)