C#LINQ逐列獲取另一個集合的最大元素

我有一個集合students和集合universities。對于每所大學，我需要選擇得分最高的學生。我想按名稱對大學進行分組，并選擇在該大學中得分最高的學生。像這樣：

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

到目前為止，我到了那里

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

問題是大學只能訪問學生 id，即每個人都Universty只有這些欄位：

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

問題是我如何在該g組中只獲得 1 名得分最高的學生。

學生：

Id,
Name,
Score

uj5u.com熱心網友回復：

這是一個例子：

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

請注意，它連接并分組了所有三個集合。Score然后它從每個組中獲取最大值的記錄。這使用MaxBy僅在 .NET 6 及更高版本中可用的方法。如果您的目標是較早的框架，則需要自己實作或使用更復雜的 LINQ。我確實在評論中問了這個問題是有原因的。

uj5u.com熱心網友回復：

假設您的物體類如下：

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

更新

如果沒有關系，您必須在物體之間加入。

要獲得大學學生的最高分數，您可以使用.OrderByDescending()以下方式：

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

演示@.NET Fiddle

標籤：C＃林克加入通过...分组

上一篇：在編譯時獲取非靜態方法的MethodInfo

下一篇：在Revit中獲取CAD匯入的視圖名稱