LINQ group results

Question

I've spent the whole day trying to figure out how to JOIN / group my query below, can someone help me or guide me to complete the desired output? -i enclosed some of the foreign key values for easy reference)

Below is my Table Grade
rowID testID  studentID     Grade
1     1       1(class2011)  50
2     1       1(class2011)  90
3     2       1(class2011)  100
4     2       2(class2012)  85


--Student table
StudentID   Classyear
1           2(class2011)
2           3(class2012)
3           1(class2010)

--Classyear Table
ClassYearID    Desc
1              2010
2              2011     
3              2012

My query below (to display the testID, failed(passing rate=80), passed, taken, Rate)

var h = list.GroupBy(a => a.testID)
        .Select(a => {
           int _failed = a.Count(g => g.Grade < 80);
           int _passed = a.Count(g => g.Grade >= 80);
           int _rate = (int)(_passed / (double)a.Count() * 100.0);

           return new {
             testID = a.Key,
             failed = _failed,
             passed = _passed,
             taken = a.Count(),
             rate = _rate,
          };     
        }); 

The result1
testID  failed  passed  taken   Rate
1       1       1       2       50%  
2       0       2       2       100%

Now here's my BIG problem: the students in my table grade above DOES NOT belong in the same classyear. I need to group this by testID and classyear. See below for the desired output:

testID  ClassyearID failed  passed  taken   Rate    
1       1(2010)     0       0       0       0
1       2(2011)     1       1       2       50%
1       3(2012)     0       0       0       0

2       1(2010)     0       0       0       0
2       2(2011)     0       1       1       100%
2       3(2012)     0       1       1       100%

You can immediately see the difference from result 1. stats for Test2 is divided for class 2011 and class 2012.

Hope I did explain clear enough my intentions. Maybe I will post later my crappy code trying to get the desired output. Thanks

Answer 1

Can't you just do this?:

var h = list.GroupBy(a =>new {a.testID,a.classyear})
        .Select(a => {
           int _failed = a.Count(g => g.Grade < 80);
           int _passed = a.Count(g => g.Grade >= 80);
           int _rate = (int)(_passed / (double)a.Count() * 100.0);

           return new {
             testID = a.Key.testID,
             classyear=a.Key.classyear,
             failed = _failed,
             passed = _passed,
             taken = a.Count(),
             rate = _rate,
          };     
        });