LINQ to SQL Get Latest Date from Object

Question

I have read a few items on this, including How to select only the records with the highest date in LINQ but I don't know how to apply it to my case which is slightly more complex.

I am trying to get all AdjusterProfileStatusItem but only select the most recent s.statusDate. Currently, the query just returns all dates for all records; whereas I just want the most recent date for all records.

(from u in db.Users
join a in db.Adjusters
on u.id equals a.userID
join s in db.AdminAdjusterStatus
on a.id equals s.adjusterID
where u.userType.ToLower() == "adjuster"
&& s.status.ToLower() == "approved"

&& s.statusDate.Max() // causes syntax error...

select new AdjusterProfileStatusItem
{
   user = u,
   adjuster = a
})

Edit:

I have also tried this which gives me a syntax error...

(from u in db.Users
join a in db.Adjusters
on u.id equals a.userID
join s in db.AdminAdjusterStatus
on a.id equals s.adjusterID
where u.userType.ToLower() == "adjuster"
&& s.status.ToLower() == "approved"
group new { u, a, s } by s.adjusterID into x
select new AdjusterProfileStatusItem
{
    user = u, // u does not exist in context
    adjuster = a, // a does not exist in context
    status = x.Max(y => y.statusDate) // anonymous type does not contain definition for 'statusDate'
})

can you use order by s.statusDate, and use take(n) to take one two or any number of them you want. — Vladimir Bozic, Jul 17 '13 at 13:51
@VladimirBozic I would like to take that simple approach but I think that would just take 1 record, whereas I want to take all records (grouped by the date and only take the latest `s.statusDate` date). — user1477388, Jul 17 '13 at 13:55
I am trying to do something like this but I can't figure out how to apply it http://stackoverflow.com/questions/15367615/find-max-and-min-datetime-in-linq-group — user1477388, Jul 17 '13 at 15:21

broguyman · Accepted Answer · 2013-07-17T19:30:11.267

I'm not sure how you feel about Lambda expressions but I would probably do this:

db.Users
  .Join(db.Adjusters,
  u => u.Id,
  a => a.UserId,
  (u, a) => new 
  {
       User = u,
       Adjuster = a
  })
  .Join(db.AdminAdjusterStatus,
  a => a.Adjuster.Id,
  s => s.AdjusterId,
  (a, s) => new 
  {
       User = a.User,
       Adjuster = a.Adjuster,
       AdminAdjusterStatus = s
  })
  .Where(x => x.User.userType == "adjuster"
      && x.AdminAdjusterStatus.status == "approved"
      && x.AdminAdjusterStatus.statusDate == db.AdminAdjusterStatus
                             .Where(y => y.AdjusterId == 
                                    x.AdminAdjusterStatus.AdjusterId)
                             .Max(z => z.statusDate))
  .Select(a => new AdjusterProfileStatusItem
  {
      user = a.User
      adjuster = a.Adjuster
  })

**EDIT!!!**


(from u in db.Users
join a in db.Adjusters
on u.id equals a.userID
join s in db.AdminAdjusterStatus
on a.id equals s.adjusterID
where u.userType.ToLower() == "adjuster"
&& s.status.ToLower() == "approved"

&& s.statusDate == GetMaxStatusDate(db.AdminAdjusterStatus.ToList(), s.AdjusterID)

select new AdjusterProfileStatusItem
{
   user = u,
   adjuster = a
})

private DateTime GetMaxStatusDate(List<AdminAdjusterStatus> statuses, int adjusterId)
{
     return (from a in statuses
             where a.AdjusterId == adjusterId
             group a by a.AdjusterId into values
             select values.Max(x => x.statusDate)).FirstOrDefault();
}



 OR 



(from u in db.Users
join a in db.Adjusters
on u.id equals a.userID
join s in db.AdminAdjusterStatus
on a.id equals s.adjusterID
where u.userType.ToLower() == "adjuster"
&& s.status.ToLower() == "approved"

&& s.statusDate == db.AdminAdjusterStatus
                   .Where(x => x.AdjusterId == s.AdjusterId)
                   .Select(y => y.statusDate)
                   .Max();

select new AdjusterProfileStatusItem
{
   user = u,
   adjuster = a
})

This may work but I was hoping to find a LINQ query for this. Lambda is a little to verbose for something like this, but thanks. I will accept this if no one else can translate this or something like it into LINQ. — user1477388, Jul 17 '13 at 16:36
I appreciate your feedback. I will try a strictly LINQ answer and post it as another answer if I am successful. — broguyman, Jul 17 '13 at 17:04
The edit that I made is the best LINQ answer that I could come up. I hope that it helps. — broguyman, Jul 17 '13 at 19:00

LINQ to SQL Get Latest Date from Object

1 Answers1