Groupby and Compare the results in Linq

Question

I have the following recordset for a given School (i.e., ABC in this example) , I need to groupby on Class and Activity and take the first row . Then I need to match the 'Activity' of grouped records and check for 'Groups' data (ID 1,4 return true because 'Activity' is 'Dance and Groups is 'B'). If they are same I need to return true. In this example , I get four records with IDs 1,2,4,5 and since 'Activity' of ID 1,4 are matching and Groups is 'B' , Activity of 2,4 are matching and Groups is same 'B' I need to return true.

ID  School  Class  Activity  Groups OtherData
1   ABC      A       Dance     B     Junk1
2   ABC      A       Sing      B     Junk2
3   ABC      A       Sing      B     Junk3
4   ABC      B       Dance     B     Junk4
5   ABC      B       Sing      B     Junk5

I am trying to achieve the same using linq ,

List<School> schoolList = //from db all 5 records

var result = schoolList.Group(res => {res.School,res.Class}).Select(x=>x.First()).ToList();

Further , I am not sure how to check the "Groups" are same for the above grouped records .

Example 2:

ID  School  Class  Activity  Groups OtherData
1   ABC      A       Dance     B     Junk1
2   ABC      A       Sing      C     Junk2
3   ABC      A       Sing      C     Junk3
4   ABC      B       Dance     B     Junk4
5   ABC      B       Sing      B     Junk5

Here , the result should be false because IDs 2,5 after matching with Activity they have different Groups C and B.

Do I need to use 2 different result sets and join on 'Activity'?

Answer 1

After grouping by Class and Activity

var rowsDistinctByClassAndActivity = schoolList
    .GroupBy(school => new { school.Class, school.Activity })
    .Select(gr => gr.First());

, you could obtain your result by first grouping by Activity and then checking if all Groups values in each grouping are identical by counting the distinct occurrences of Groups for each grouping:

var result = rowsDistinctByClassAndActivity
    .GroupBy(row => row.Activity)
    .All(gr => gr.Select(row => row.Groups).Distinct().Count() == 1);

The two calculations may be combined:

var result = schoolList
    .GroupBy(school => new { school.Class, school.Activity })
    .Select(gr => gr.First())
    .GroupBy(row => row.Activity)
    .All(gr => gr.Select(row => row.Groups).Distinct().Count() == 1);

---

A more optimized alternative to counting the distinct occurrences is to check that there are not more than one distinct occurrence of Groups. To obtain this, the last line in the previous code could be replaced by:

    .All(gr => !gr.Select(row => row.Groups).Distinct().Skip(1).Any());

I'd say this is an unnecessary change to make, though, unless you expect that there may be a lot of different Groups values present in many of the groupings.

---

Example fiddle here.