Get max value of x field only from objects satisfying 'true' for y field

Say I've got a database of Person objects with the following fields:

class Person
{
     public DateTime BirthDate { get; set; }
     public string CountryOfBirth { get; set; }
}

I want to use a query (LINQ or some other C# code) that searches the birthDates of Persons born only in (e.g.) USA, and returns the Person with the max birthDate. If there are more than one Persons born on this date, it should return all of them.

What is the way to do it?

Jon Skeet
people
quotationmark

Well, it sounds like you want to:

  • Filter by country
  • Group by birth date
  • Order the groups in descending order
  • Take the first group

So (assuming the property names have been fixed to follow conventions):

var query = db.People
              .Where(p => p.CountryOfBirth == "USA")
              .GroupBy(p => p.BirthDate)
              .OrderByDescending(p => p.Key)
              .FirstOrDefault();

This will return null if there are no groups because there are no people born in the USA. To return an empty sequence instead, you could flatten the first result if there is one:

var query = db.People
              .Where(p => p.CountryOfBirth == "USA")
              .GroupBy(p => p.BirthDate)
              .OrderByDescending(p => p.Key)
              .Take(1)
              .SelectMany(p => p);

people

See more on this question at Stackoverflow