Replicating an SQL query in LINQ

Question

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 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!

Can you define what you want to show in the interface (in a functional way). It isn't quite clear to me. — Steven
– Steven, Commented Mar 22, 2011 at 11:13
Basically I'm just trying to create a table with a row for each of the groups in the selected list, and for each group to count the number students who have ticked the "IsReturning" field. So in pseudo code: Count students in this group where IsReturning = true. Repeat for each group. — Chris
– Chris, Commented Mar 22, 2011 at 13:22

Stuart · Accepted Answer · 2011-03-22 10:25:41Z

2

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.

answered Mar 22, 2011 at 10:25

Stuart

66.9k7 gold badges116 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Over a year ago

Thanks, that code selects what I need. The problem I am having now is that it returns an IEnumerable Anonymous Type which I cannot pass to my View (See here). I think the easiet way is going to be to create a View in the SQL Server database, and import it as a class into the MVC application, which is rather untidy just for one query!

Chris Over a year ago

Also, well spotted: g.CourseId was a typo it should have been g.groupID!

Stuart Over a year ago

If you don't want to create the view then create a class... e.g. class SpecialResult { public Group Group {get;set;} public int Returning {get;set;} } - you can then new this in the select in the query

Steven · Accepted Answer · 2011-03-22 20:04:24Z

2

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

edited Mar 22, 2011 at 20:04

answered Mar 22, 2011 at 13:40

Steven

174k27 gold badges355 silver badges453 bronze badges

2 Comments

Chris Over a year ago

Thanks this code also does what I need and is fast, but returns an IEnumerable Anonymous Type which I can't pass to the view (See comment on other answer)

Steven Over a year ago

Don't return an anonymous type than. Create a custom type and return that. Don't create a database view, that is silly and will become a maintenance nightmare soon.

Collectives™ on Stack Overflow

Replicating an SQL query in LINQ

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related