how to write the following sql query in linq

Question

select 
    coalesce(x.value,a.default_value) as value 
from table1 a 
left outer join 
    (
       select 
          b.configid,
          b.value 
       from table2 b 
       join table3 c on b.dataid=c.id 
       where c.id=0
    )x on a.id=x.configid 
where a.key='abc'

Answer 1

For translating SQL to LINQ,

1. Translate subselects as separate variables
2. Translate each clause in LINQ clause order, leaving monadic operators (DISTINCT, TOP, etc) as functions applied to the whole LINQ query.
3. Use table aliases as range variables. Use column aliases as anonymous type field names.
4. Use anonymous types (new { }) for multiple columns
5. Left Join is simulated by using a join variable and doing another from from the join variable followed by .DefaultIfEmpty().
6. Replace coalesce with the conditional operator and a null test.