Why doesn't this LINQ query return the correct dates?

Question

The following LINQ query reads the delimited file and returns the most recent record for each recordId. The problem is that the most recent record does not always return. What am I doing wrong? What do I need to change to ensure that the most recent date always returns? Is there a better way than using .Max ()?

I also added some sample data so you can see this problem. When viewing sample data, the lines marked with an asterisk (*) are the lines that I want to return (the most recent date). The lines marked with an X are incorrect, in my opinion, returned.

In cases where the same recordId appears several times (# 162337, for example) and has multiple dates, I want one record to be returned with the most recent date.

var recipients = File.ReadAllLines(path) .Select (record => record.Split('|')) .Select (tokens => new { FirstName = tokens[2], LastName = tokens[4], recordId = Convert.ToInt32(tokens[13]), date = Convert.ToDateTime(tokens[17]) } ) .GroupBy (m => m.recordId) .OrderByDescending (m => m.Max (x => x.date ) ) .Select (m => m.First () ) .OrderBy (m => m.recordId ) .Dump(); FirstName LastName recordId date fname lname 137308 2/15/1991 0:00 fname lname 138011 6/16/1983 0:00 * fname lname 138011 11/9/1981 0:00 x fname lname 158680 9/4/1986 0:00 fname lname 161775 4/23/1991 0:00 fname lname 162337 12/1/1998 0:00 * fname lname 162337 12/1/1998 0:00 * fname lname 162337 9/1/1994 0:00 x fname lname 162337 9/1/1994 0:00 x fname lname 163254 2/12/1969 0:00 fname lname 173816 9/26/1997 0:00 fname lname 178063 1/16/1980 0:00 * fname lname 178063 3/3/1976 0:00 x fname lname 180725 7/1/2007 0:00 * fname lname 180725 1/14/1992 0:00 x fname lname 181153 5/1/2001 0:00 

Answer 1

You order the entire sequence of groups by the maximum date in each group. You need to place an order in each individual group so that only the item with the maximum date is selected.

 var recipients = File.ReadAllLines(path) .Select(record => record.Split('|')) .Select(tokens => new { FirstName = tokens[2], LastName = tokens[4], recordId = Convert.ToInt32(tokens[13]), date = Convert.ToDateTime(tokens[17]) }) .GroupBy(m => m.recordId, (k, g) => g.OrderByDescending(m => m.date).First()) .OrderBy(m => m.recordId); 

If performance is important, and each group can potentially contain many elements, you can see a slight improvement if you use Aggregate to determine the maximum record in a group, and not for OrderByDescending / First :

 // ... .GroupBy(m => m.recordId, (k, g) => g.Aggregate((a, m) => (m.date > a.date) ? m : a)) // ... 

Answer 2

Is this line possible:

 .OrderByDescending (m => m.Max (x => x.date ) ) 

sorts groups by their maximum date, not by items in each group?

This cut off code segment gives the results you are looking for (although you will have to work with file processing, obviously)

  List<Customer> Customers = new List<Customer>() { new Customer(){ RecordId = 12, Birthday = new DateTime(1970, 1, 1)}, new Customer(){ RecordId = 12, Birthday = new DateTime(1982, 3, 22)}, new Customer(){ RecordId = 12, Birthday = new DateTime(1990, 1, 1)}, new Customer(){ RecordId = 14, Birthday = new DateTime(1960, 1, 1)}, new Customer(){ RecordId = 14, Birthday = new DateTime(1990, 5, 15)}, }; var groups = Customers.GroupBy(c => c.RecordId); IEnumerable<Customer> itemsFromGroupWithMaxDate = groups.Select(g => g.OrderByDescending(c => c.Birthday).First()); foreach(Customer C in itemsFromGroupWithMaxDate) Console.WriteLine(String.Format("{0} {1}", C.RecordId, C.Birthday)); 

Or even better:

 IEnumerable<Customer> itemsFromGroupWithMaxDate = Customers.GroupBy(c => c.RecordId).Select(g => g.OrderByDescending(c => c.Birthday).First()); 

Blind hit on your code, I believe this might work:

 var recipients = File.ReadAllLines(path) .Select (record => record.Split('|')) .Select (tokens => new { FirstName = tokens[2], LastName = tokens[4], recordId = Convert.ToInt32(tokens[13]), date = Convert.ToDateTime(tokens[17]) } ) .GroupBy (m => m.recordId) .Select(m => OrderByDescending(x => x.date).First()) .OrderBy (m => m.recordId ) .Dump();