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?