LINQ; How to perform left outer join with multiple conditions?

Question

I'm trying to imitate:

DB1 AS A LEFT OUTER JOIN
DB2 AS B 
ON A.[Currency Code] = B.[Currency Code] 
AND A.[Document Date] >= B.[Starting Date] 
AND A.[Document Date] <= B.[Ending Date]

This is what I have now:

from ledgers in ledgerEntries
join currency in currencyExchange
    on ledgers.CurrencyCode equals currency.CurrencyCode
        into c
from currencies in c.DefaultIfEmpty()
where
    ledgers.DocumentDate >= currencies.StartingDate
    && ledgers.DocumentDate <= currencies.EndingDate

I've read about creating an anonymous type and setting them equal to each other, but this doesn't work when using less than and greater than for comparing the dates.

score 5 · Accepted Answer · edited May 23 '17 at 12:16

It seems this answer was what I was looking for.

from ledgers in ledgerEntries
join currency in currencyExchange
    on ledgers.CurrencyCode equals currency.CurrencyCode
        into c
from currencies in c.Where(currency => currency.StartingDate <= ledgers.DocumentDate 
&& currency.EndingDate <= ledgers.DocumentDate).DefaultIfEmpty()

Combined with this answer, I was able to simplify to:

from ledgers in ledgerEntries
from currencies in currencyExchange.Where(
    currency => currency.CurrencyCode == ledgers.CurrencyCode
    && currency.StartingDate <= ledgers.DocumentDate 
    && currency.EndingDate <= ledgers.DocumentDate).DefaultIfEmpty()

LINQ; How to perform left outer join with multiple conditions?

1 Answers1