Help needed on SQL query to Linq Conversion

Answer 1

Why have a CASE in your joining criteria? What do you expect the optimizer to do with that exactly?

Here's a literal translation.

from svcMap in db.svcMaps
where svcMap.ToBill == 1
let svc = svcMap.Svc
where (svcMap.Desired == 0 && svcMap.Present <> svc.Disabled)
  || (svcMap.Desired <> 0 && svcMap.Desired <> svc.Disabled)
let optionId =
  svcMap.Desired <> 0 && svcMap.Desired <> svc.Disabled ? svcMap.Desired :
  svcMap.Present <> svc.Disabled ? svc.Present :
  0
from option in db.Options
where option.SvcOptionId == optionId
select new {svc.SvcName, option.SvcOptionName, svcMap.Price }