Latest date in Linq to sql

Question

Say I have a table named Storage with records this kind

Item_Id      |  Date            |   Price
1            |  01/01/2011      |   50
1            |  03/26/2012      |   25  
2            |  04/21/2012      |   20
3            |  08/02/2012      |   15
3            |  09/02/2012      |   13
4            |  10/02/2012      |   18

I would like to get the Item_Id with the latest Date associated to it (in this case it would be then "4") using linq to sql.

I'm using this expression

var lastDate= Storage
                    .GroupBy(s => s.Item_Id)
                    .Select(grp => new {
                      grp.Key,
                      LastDate = grp.OrderByDescending(
                             x => x.Date).First()
                    }).ToList();

and I'm getting a list with the latest date for each item_Id. So the next step would be to get the highest of all of them, but I'm stuck there.

Actually, eventually I'd need to skip some of the Item_Id's in the query but I guess I'll be able to add a where before the Select, something like Where(s => s.Item.Id {and here an expression that checks if it's inside the list I pass).

I'd appreciate any hint,

Thanks

see http://stackoverflow.com/questions/1101841/linq-how-to-perform-max-on-a-property-of-all-objects-in-a-collection-and-ret — Daniel Powell, Aug 01 '12 at 23:36

score 2 · Accepted Answer · answered Aug 01 '12 at 23:45

2

Does this work for what you need?

var highestDateID = Storage.OrderByDescending(x => x.Date).Select(x => x.Item_Id).FirstOrDefault();

answered Aug 01 '12 at 23:45

Simon Whitehead

63,300
9
114
138

score 1 · Answer 2 · answered Aug 01 '12 at 23:51

1

Why not

var lastDateId = Storage
  .GroupBy(s => s.Item_Id)   
  .Select(grp => new { Id = grp.Key, LastDate = grp.Max(x=>x.Date) })
  .OrderBy(o=>o.LastDate).Take(1).Id;

or even

var lastDateId = Storage.OrderByDescending(o=>o.LastDate).Take(1).Id;

and if you need a full record

var record = Storage
  .Where(w=>w.Id == Storage.OrderByDescending(o=>o.LastDate).Take(1).Id)
  .First();

answered Aug 01 '12 at 23:51

Darek

4,687
31
47

It doesn't allow me to access the Id in this way, I guess it's because I'm doing something wrong, though. – mitomed Aug 02 '12 at 09:49

Latest date in Linq to sql

2 Answers2