CODEWITHSUNDEEP
sqllinqleft-join
D'Arcy Rail-Ip
D'Arcy Rail-Ip

Reputation: 11955

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

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.

Upvotes: 3

Views: 9297

Answers (1)

D&#39;Arcy Rail-Ip
D&#39;Arcy Rail-Ip

Reputation: 11955

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()

Upvotes: 5

Related Questions