What's wrong with the joins in this LINQ query?

Question

I'm trying to replicate the following SQL query in LINQ:

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

This is what I have in 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

The last two joins to fp and fx are the problem but it's not clear to me what's wrong, it doesn't seem to like && but there's no and keyword like there is an equals that replaces =.

I've removed the select portion from LINQ as it's not relevant to the problem and I'd like to avoid spending more time obfuscating table and field names.

Instead of joining on time ranges join with a query that returns records within this time range — Tim Schmelter, Jan 07 '21 at 17:38
To exemplify the above comment: `join fp in db.Table5.Where(x => x.StartDate >= ... && ...) on ...` — Camilo Terevinto, Jan 07 '21 at 17:40
To do a join on multiple columns you do `new { dv.CurrencyId, fp.FiscalPeriodId } equals new { CurrencyId = fx.ToCurrencyId, fx.FiscalPeriodId }` and I'd move the `FX.ToCurrencyId = 'USD'` to the where clause but you could include it as well if you wanted. — juharr, Jan 07 '21 at 17:47

Paulo · Accepted Answer · 2021-01-07T17:53:56.640

"A join clause performs an equijoin. In other words, you can only base matches on the equality of two keys. Other types of comparisons such as "greater than" or "not equals" are not supported. To make clear that all joins are equijoins, the join clause uses the equals keyword instead of the == operator. "

reference: https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/join-clause

you need to do this in the where clause. Like here:

https://stackoverflow.com/a/3547706/3058487

To do a join using composite keys, you need to do something like here:

new { dv.CurrencyId, fp.FiscalPeriodId } equals new { CurrencyId = fx.ToCurrencyId, fx.FiscalPeriodId }

Reference: https://learn.microsoft.com/en-us/dotnet/csharp/linq/join-by-using-composite-keys

What's wrong with the joins in this LINQ query?

1 Answers1