Convert SQL Query (with Correlated Subquery) to LINQ in C#

Question

I'm looking for some assistance converting the following sql query to LINQ to entities in C#.

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

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.

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.

Thanks

Answer 1

In order to answer the question, I will have to assume the name of your entities.

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

I will suppose a Fund entity and a FundUnit entity, just like the example.

var query = from f in Fund 
            from u in FundUnit
            where f.FundId == u.FundId
            && u.EffectiveDate == (from k in FundUnit
                                   where u.FundId = k.FundId
                                   select EffectiveDate).Max()
            && f.Active == 1
            select new { Id = f.FundId, EffectiveDate = u.EffectiveDate } // Add any fields you need
            order by f.FundId

I did all this from memory and didn't tested it, there might be some minor problems. If I have time, I'll test it.