We have list of students and grades. Students have ID, Surname, Name, ProgrammeID, while the grades class: StudentID, SubjectCode, Date and Value.

The main idea is to find all students that have at least one greatest grade (Grade class -> Value >= 10) in all subject that he attends to.

I should mention that all of this information (Student and Grade) are stored in separated List.

Student and Grade class looks like this:

class Student
{
    public string ID { get; set; }
    public string Surname { get; set; }
    public string Name { get; set; }
    public string ProgrammeID { get; set; }

    public Student(string id, string surname, string name, string programmeID)
    {
        this.ID = id;
        this.Surname = surname;
        this.Name = name;
        this.ProgrammeID = programmeID;
    }

    public override string ToString()
    {
        return String.Join("; ", ID, Surname, Name, ProgrammeID);
    }
}

class Grade
{
    public string StudentID { get; set; }
    public string SubjectCode { get; set; }
    public DateTime Date { get; set; }
    public int Value { get; set; }

    public Grade(string studentID, string subjectCode, DateTime date, int value)
    {
        this.StudentID = studentID;
        this.SubjectCode = subjectCode;
        this.Date = date;
        this.Value = value;
    }

    public override string ToString()
    {
        return String.Join("; ", StudentID, SubjectCode, Date, Value);
    }
}

Example: We have three attendees and three marks: (Only Student that has code - B13798 should be displayed, because the Student that has code - B13799, has value of 10 in only one of the provided two subjects)

atendees.Add(new Student("B13799", "Surname0", "Name0", "P175B123"));
atendees.Add(new Student("B13799", "Surname0", "Name0", "P175B122"));
atendees.Add(new Student("B13798", "Surname1", "Name1", "P175B123"));

marks.Add(new Grade("B13799", "P175B123", DateTime.Parse("2021-01-02 12:45:36"), 10));
marks.Add(new Grade("B13799", "P175B122", DateTime.Parse("2021-01-02 12:45:36"), 9));
marks.Add(new Grade("B13798", "P175B123", DateTime.Parse("2021-01-02 12:45:36"), 10));

CodePudding user response：

Try this

First group the marks to find the highest mark for each student and subject

var group1 = (from r in marks group r by new { r.StudentID, r.SubjectCode } into results 
select new { results.Key.StudentID, results.Key.SubjectCode, max = results.Max(x => x.Value)});

Then group these results by student

var group2 = (from r in group1 group r by r.StudentID into results select results);

Finally select just those students who have a 10 for every subject

var list = (from r in group2 where r.All(a=>a.max == 10) select r.Key).ToList()

CodePudding user response：

You could

• create one group of marks per student by grouping marks by StudentId
• for each such group (marksForStudent), create one group of marks per subject by grouping marksForStudent by SubjectCode
• for each student, verify whether each such group (marksForSubject) contains at least one mark that passes your given requirement
• filter which students are of interest based on this verification
• select the StudentId of the students of interest

It may be implemented like this:

List<Student> attendees;
List<Grade> marks;

// Populate lists

var threshold = 10;

var filteredAttendeeIds = marks
    .GroupBy(mark => mark.StudentID)
    .Where(marksForStudent => marksForStudent
        .GroupBy(mark => mark.SubjectCode)
        .All(marksForSubject => marksForSubject
            .Any(mark => mark.Value >= threshold)))
    .Select(marksForStudent => marksForStudent.Key);

The students can then be found by intersecting the filtered student IDs by the student list:

var filteredAttendees = attendees
    .IntersectBy(filteredAttendeeIds, attendee => attendee.ID);

Example fiddle here.

Note: This implementation assumes that for each Student.ProgrammeID in attendees, there is in fact an existing Grade object in marks.

Side note:
I would recommend replacing the Student's string ProgrammeID property with IEnumerable<string> ProgrammeIds, and collect all the student's programme IDs there. That way, you can define each student once, rather than defining the same student for each and every programme they attend.