title: Convert SQL Query (with Correlated Subquery) to LINQ in C#
body: <p>I'm looking for some assistance converting the following sql query to LINQ to entities in C#.   </p>
  
  <pre><code>SELECT f.FundId, u.UnitValue
  FROM Fund f
  INNER JOIN FundUnit u ON f.FundId= u.FundId
  WHERE u.EffectiveDate = (SELECT MAX(effectivedate) 
                           FROM FundUnit k 
                           WHERE u.FundId = k.FundId)
  AND f.Active = 1
  ORDER BY f.FundId
  </code></pre>
  
  <p>The query reads all active funds in the database along with their latest unit value.  The Fund table which contains a record for each fund.  The FundUnit table contains a unit value record for each fund per date.  Unit values for previous dates are also left in the table to maintain a history.  </p>
  
  <p>Not all funds receive a new unit value each day hence the latest effective date for all funds is not necessarily the same.  Therefore the max(effectivedate) function needs to be applied to the units table needs to be applied per fund - hence the correlated subquery. </p>
  
  <p>Thanks</p>