转换SQL查询（有相关子查询）的LINQ C＃ [英] Convert SQL Query (with Correlated Subquery) to LINQ in C#

问题描述

我在找一些援助下面的SQL查询转换为LINQ到实体C＃。

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

查询沿读取数据库中的所有主动型基金的最新的单位价值。该基金表，其中包含每个基金的记录。该FundUnit表包含每个日期每个基金单位值记录。以前日期的单位值也留在表中保持的历史。

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.

不是所有的资金收到一个新的单位值的每一天，因此最新的有效日期的所有资金是不一定相同。因此，MAX（EFFECTIVEDATE）功能需要被应用到表需要每个基金要应用的单位 - 因此相关子查询。

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.

感谢

推荐答案

为了回答这个问题，我会必须承担你的实体的名称。

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

选择f.FundId，u.UnitValue从基金
F INNER JOIN FundUnitûON f.FundId =
ū .FundId WHERE u.EffectiveDate =
（SELECT MAX（EFFECTIVEDATE）
FROM FundUnitķ
其中u.FundId = k.FundId）AND f.Active = 1
ORDER用f .FundId

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

我会假设一个基金实体和实体FundUnit，就像例子。

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.

这篇关于转换SQL查询（有相关子查询）的LINQ C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！