Entity Framework LEFT JOIN with CASE and COALESCE

148 views Asked by At

I have the following clause in my legacy code that we need to move over to our new EF solution. It is used for doing currency conversions.

SELECT TOP 25 job_id, j.salary_format_id, j.currency_id, j.offer_date, j.accepted_date, j.reported_date, j.start_date,

CASE     
    WHEN salary_format_id = 1 AND salary > 0 THEN salary * 1
    WHEN salary_format_id = 2 AND salary > 0 THEN salary * 4
    WHEN salary_format_id = 3 AND salary > 0 THEN salary * 12
    WHEN salary_format_id = 4 AND salary > 0 THEN salary * 52
    WHEN salary_format_id = 5 AND salary > 0 THEN salary * 5 * 52
    WHEN salary_format_id = 6 AND salary > 0 THEN salary * 40 * 52    
END * (c2.rate / c1.rate) AS salary

FROM job j
LEFT JOIN exchange_rate c1 ON CAST(
(
CASE 
    WHEN COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date) < '1999-01-01' THEN '1999-01-01'
    ELSE COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date)
END
) AS Date) = c1.date AND c1.currency_id = j.currency_id
LEFT JOIN exchange_rate c2 ON CAST(
(
    CASE 
      WHEN COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date) < '1999-01-01' THEN '1999-01-01'
      ELSE COALESCE(j.offer_date, j.accepted_date, j.reported_date, j.start_date)
    END
) AS Date) = c2.date AND c2.currency_id = @currency_id

My intent here is to convert currency based on the date (we have the rates stored in the 'exchnage_rate' table. It uses the COALESCE to prioritize which date column to use in order to get the proper rate.

I have the following line (reduced for simplicity):

var baseQuery = this.dbContext.Jobs;
...

How do I convert the following SQL into Entity Framework code here?

0

There are 0 answers