c# - LINQ to SQL Get Latest Date from Object -

i have read few items on this, including how select records highest date in linq don't know how apply case more complex.

i trying adjusterprofilestatusitem select recent s.statusdate. currently, query returns dates records; whereas want recent date records.

(from u in db.users join in db.adjusters on u.id equals a.userid join s in db.adminadjusterstatus on a.id equals s.adjusterid u.usertype.tolower() == "adjuster" && s.status.tolower() == "approved"  && s.statusdate.max() // causes syntax error...  select new adjusterprofilestatusitem {    user = u,    adjuster = }) 

edit:

i have tried gives me syntax error...

(from u in db.users join in db.adjusters on u.id equals a.userid join s in db.adminadjusterstatus on a.id equals s.adjusterid u.usertype.tolower() == "adjuster" && s.status.tolower() == "approved" group new { u, a, s } s.adjusterid x select new adjusterprofilestatusitem {     user = u, // u not exist in context     adjuster = a, // not exist in context     status = x.max(y => y.statusdate) // anonymous type not contain definition 'statusdate' }) 

i'm not sure how feel lambda expressions this:

db.users   .join(db.adjusters,   u => u.id,   => a.userid,   (u, a) => new    {        user = u,        adjuster =   })   .join(db.adminadjusterstatus,   => 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 in db.adjusters on u.id equals a.userid join s in db.adminadjusterstatus on a.id equals s.adjusterid u.usertype.tolower() == "adjuster" && s.status.tolower() == "approved"  && s.statusdate == getmaxstatusdate(db.adminadjusterstatus.tolist(), s.adjusterid)  select new adjusterprofilestatusitem {    user = u,    adjuster = })  private datetime getmaxstatusdate(list<adminadjusterstatus> statuses, int adjusterid) {      return (from in statuses              a.adjusterid == adjusterid              group a.adjusterid values              select values.max(x => x.statusdate)).firstordefault(); }     or     (from u in db.users join in db.adjusters on u.id equals a.userid join s in db.adminadjusterstatus on a.id equals s.adjusterid 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 = })