C# LINQ get max element in group by column of another collection-CodePudding

I have an collection of students and collection of universities. For each university i need to pick student with highest score. I want to group universities by name and select student that got highest score in that univesity. Like this:

Country | Name            | Student | Score
New York| NY University   | Bob     | 120
LA      | LA Univesity    | Tom     | 140

So far i got there

 return from university in Universities
                   join country in Countries on university.Id equals country.Id
                   orderby country.Name
                   group university by university.Name into g
                   select new
                   {
                       g.Key,
                       maxScore = g.Max(student => student.Score) <-- student.Score is only available in Students class
                   };

Problem is university only got access to students id i.e each Universty got only thos fields:

int Country.Id,
string Name,
int Student.Id

and problem is how do i get only 1 student with max score in that g group.

Student:

Id,
Name,
Score

CodePudding user response：

Here is an example:

var countries = new List<Country>
{
    new Country { CountryId = 1, Name = "Country 1" },
    new Country { CountryId = 2, Name = "Country 2" }
};

var universities = new List<University>
{
    new University { UniversityId = 1, CountryId = 1, Name = "University 1" },
    new University { UniversityId = 2, CountryId = 1, Name = "University 2" },
    new University { UniversityId = 3, CountryId = 2, Name = "University 3" },
    new University { UniversityId = 4, CountryId = 2, Name = "University 4" }
};

var students = new List<Student>
{
    new Student { StudentId = 1, UniversityId = 1, Name = "Student 1", Score = 50 },
    new Student { StudentId = 2, UniversityId = 1, Name = "Student 2", Score = 100 },
    new Student { StudentId = 3, UniversityId = 2, Name = "Student 3", Score = 100 },
    new Student { StudentId = 4, UniversityId = 2, Name = "Student 4", Score = 50 },
    new Student { StudentId = 5, UniversityId = 3, Name = "Student 5", Score = 100 },
    new Student { StudentId = 6, UniversityId = 3, Name = "Student 6", Score = 50 },
    new Student { StudentId = 7, UniversityId = 4, Name = "Student 7", Score = 50 },
    new Student { StudentId = 8, UniversityId = 4, Name = "Student 8", Score = 100 }
};

var maxScoresByUniversity = from country in countries
                            join university in universities on country.CountryId equals university.CountryId
                            join student in students on university.UniversityId equals student.UniversityId
                            group new { country, university, student } by university.Name into g
                            let r = g.MaxBy(x => x.student.Score)
                            select new
                            {
                                Country = r.country.Name,
                                Name = r.university.Name,
                                Student = r.student.Name,
                                Score = r.student.Score
                            };

foreach (var score in maxScoresByUniversity)
{
    Console.WriteLine($"{score.Country}, {score.Name}, {score.Student}, {score.Score}");
}

Note that it joins and groups all three collections. It then gets the record with the max Score from each group. This uses the MaxBy method that is only available in .NET 6 and later. If you're targeting an earlier framework, you'd need to implement that yourself or use more complex LINQ. I did ask that question in the comments for a reason.

CodePudding user response：

Assume that your entity classes are as below:

public class University
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int CountryId { get; set; }
    public virtual Country Country { get; set; }
    public ICollection<Student> Students { get; set; }
}

public class Country
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<University> Universities { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Score { get; set; }
    public int UniversityId { get; set; }
    
    public virtual University University { get; set; }
}

Updated

Without a relationship, you have to join between the entities.

To get the max score of the student by university you can work with .OrderByDescending() as:

var result = (from university in context.Universities
              join country in context.Countries on university.CountryId equals country.Id
              join student in context.Students on university.Id equals student.UniversityId
              orderby country.Name
              group new { university, student, country } by university.Id into g
              select new
              {
                  CountryName = g.First().country.Name,
                  UniversityName = g.First().university.Name,
                  Student = g.Select(x => x.student)
                      .OrderByDescending(x => x.Score)
                      .First()
                      .Name,
                  MaxScore = g.Select(x => x.student)
                      .OrderByDescending(x => x.Score)
                      .First()
                      .Score,
              }).ToList();

Demo @ .NET Fiddle