Q:

How do I translate this Sql Statement to Linq

How do I write this query using linq?    
select oa.*
from ord_account_error oe
join
  (select max(ord_account_error_id) [ord_account_error_id]
  from ord_account_error
  group by ord_account_id) e on oe.ord_account_error_id =   e.ord_account_error_id
 join ord_account oa on oe.ord_account_id = oa.ord_account_id
 where oe.error like '%Account in wrong status%'

What the query does is return all rows from ord_account where the latest related record in ord_account_error contains "Account in wrong status".
I don't actually need the data from ord_account_error.  It just used to select the correct rows from ord_account, so it returns the Entities I need to work with.

A:

My best guess:
 var qry = from oe in ord_account_error
            .Where(a=>a.error.Contains("Account in wrong status")) 
        join e in ord_account_error
                    .GroupBy(b=>b.ord_account_error_id)
                    .Select(grp=>grp.Max(c=>c.ord_account_error_id))
            on oe.ord_account_error_id equals  e.ord_account_error_id
        join oa in ord_account on oe.ord_account_id equals oa.ord_account_id
        select oa;

