CODEWITHSUNDEEP
c#linq
KJ3
KJ3

Reputation: 5298

Get Row with Min Date in LINQ?

I've got a table with a date/time in it and I'd like to find the entire row with the minimum date. See this example:

ID     First     Last     ADateTime             Processed
1      Joe       Smith    09/06/2013 04:00 AM   True
1      Joe       Smith    09/06/2013 02:00 AM   False
2      Jack      Jones            
3      John      Jack     09/05/2013 06:00 AM   True
3      John      Jack     09/26/2013 02:00 AM   False

What I would want from a query is to get the following:

ID     First     Last     ADateTime             Processed
1      Joe       Smith    09/06/2013 02:00 AM   False
2      Jack      Jones            
3      John      Jack     09/05/2013 06:00 AM   True

I have a LINQ statement that comes close:

        var uniqueGuys = from d in tests.AsEnumerable()
                         group d by d.ID into g
                         let f = g.FirstOrDefault()
                         where f != null
                         select new
                         {
                             f.ID,
                             f.First,
                             f.Last,
                             ADateTime = g.Min(c => c.DateTime),
                             f.Processed
                         };

This gives me each of the 3 IDs with the minimum date correctly, but the rest of the information is incorrect. For example, for ID 1, I get Joe Smith having a date of 09/06/201 2:00 AM, which is right, but it shows processed as True instead of the false that's attached to that row. It looks like it just grabs the minimum date/time but then the first record it can find for the other stuff. Is there a way to get a full row with the minimum ADateTime?

Upvotes: 2

Views: 4760

Answers (1)

Alex
Alex

Reputation: 13224

You can do orderby and then select the first.

as in:

var uniqueGuys = from d in from d in tests.AsEnumerable()
                 group d by d.ID into g
                 select g.OrderBy(p => p.DateTime).First();

Upvotes: 6

Related Questions