sql server - EF6 - Generating unneeded nested queries -

i have following tables:

main_tbl: col1 | col2 | col3 ------------------    | b    | c d    | e    | f 

and:

ref_tbl: ref1 | ref2 | ref3 ------------------    | g1   | foo d    | g1   | bar q    | g2   | xyz 

i wish write following sql query:

select    m.col1      main_tbl m left join ref_tbl r on        r.ref1 = m.col1  ,       r.ref2 = 'g1'     m.col3 = 'c' 

i wrote following linq query:

from main in dbcontext.main_tbl join refr in dbcontext.ref_tbl on "g1" equals refr.ref2 refrlookup refr in refrlookup.defaultifempty() main.col1 == refr.col1 select main.col1 

and generated sql was:

select      [main_tbl].[col1]      (select      [main_tbl].[col1] [col1],     [main_tbl].[col2] [col2],     [main_tbl].[col3] [col3]     [main_tbl]) [extent1]     inner join (select      [ref_tbl].[ref1] [ref1],     [ref_tbl].[ref2] [ref2],     [ref_tbl].[ref3] [ref3]     [ref_tbl]) [extent2] on [extent1].[col1] = [extent2].[ref1]     ('g1' = [extent2].[description]) , ([extent2].[ref1] not null) , cast( [extent1].[col3] varchar) = 'c') ... 

looks nesting query within query, while want pull table. doing wrong?

i may wrong, looks don't same in linq query , sql query, on left joining clause.

i go this, if want similar sql query.

from main in dbcontext.main_tbl.where(x => x.col3 == "c") join refr in dbcontext.ref_tbl on new{n = "g1", c = main.col1} equals new{n = refr.ref2, c = refr.col1}    refrlookup r2 in refrlookup.defaultifempty() select main.col1 

by way, doesn't make sense left join on table not present in select clause : multiple identical col1 if there's more 1 related item in left joined table...