我正试图在LINQ中复制以下SQL查询:
SELECT *
FROM Table1 AS D INNER JOIN Table2 AS DV ON D.Table1Id = DV.Table1Id
INNER JOIN Table3 AS VT ON DV.Table3Id = VT.Table3Id
INNER JOIN Table4 AS C ON DV.CurrencyId = C.CurrencyId
INNER JOIN Table5 AS FP ON DV.DVDate BETWEEN FP.StartDate AND FP.EndDate
INNER JOIN Table6 AS FX ON DV.CurrencyId = FX.FromCurrencyId AND FX.ToCurrencyId = 'USD' AND FX.FiscalPeriodId = FP.FiscalPeriodId
这就是我在LINQ:中所拥有的
from d in db.Table1
join dv in db.Table2 on d.Table1Id equals dv.Table1Id
join vt in db.Table3 on dv.Table3Id equals vt.Table3Id
join c in db.Table4 on dv.CurrencyId equals c.CurrencyId
join fp in db.Table5 on dv.DVDate >= fp.StartDate && dv.DVDate <= fp.EndDate //error on this line
join fx in db.Table6 on dv.CurrencyId equals fx.FromCurrencyId && fx.ToCurrencyId equals "USD" && fx.FiscalPeriodId equals fp.FiscalPeriodId //error also on this line
fp
和fx
的最后两个联接是问题所在,但我不清楚出了什么问题,它似乎不喜欢&&
,但没有and
关键字,就像有一个equals
取代了=
一样。
我已经从LINQ中删除了select
部分,因为它与问题无关,并且我希望避免花费更多时间混淆表和字段名称。
"联接子句执行等联接。换句话说,您只能根据两个键的相等性进行匹配。其它类型的比较,例如";大于";或";不等于";不支持。为了明确所有联接都是等联接,联接子句使用equals关键字而不是==运算符">
参考:https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/join-clause
您需要在where
子句中执行此操作。像这里:
https://stackoverflow.com/a/3547706/3058487
要使用组合键进行连接,您需要执行以下操作:
new { dv.CurrencyId, fp.FiscalPeriodId } equals new { CurrencyId = fx.ToCurrencyId, fx.FiscalPeriodId }
参考:https://learn.microsoft.com/en-us/dotnet/csharp/linq/join-by-using-composite-keys