LINQtoEntities:如何在LINQ中实现这个复杂的T-SQL(多个联接、不同、不存在)?

我有一个复杂的查询,我正试图在linq to entities中重现,但我还没有出现——这有可能吗?

T-SQL查询如下所示:

select distinct
 C.id,
 L.id
from
 dp
 join L on L.fk = DP.id
 join M on ( M.l_code = L.l_code and M.dp_code = DP.dp_code )
 join C on C.c_code = M.c_code
where not exists ( select id from map where map.cid = c.id and map.lid = l.id )

表格如下:

DP:
 id (pk)
 dp_code (varchar)

L:
 id (pk)
 fk (fk to DP.ID)
 l_code (varchar)

M:
 id (pk)
 l_code (varchar, matches value in L.l_code)
 dp_code (varchar, matches value in DP.dp_code)
 c_code (varchar, matches the value in C.c_code)

C:
id (pk)
c_code (varchar)

MAP:
id (pk)
cid (fk to C.id)
lid (fk to L.id)

我的LINQ看起来像:

  IQueryable foo = from dp in ctx.DP
             from l in dl.L
             join m in ctx.M on l.l_code equals m.m_code
            // Q1: how can I add:  AND m.dp_code = dp.dp_code
            join c in ctx.C on m.c_code = c.c_code
            // this works, but why can't I do it as an AND in the join?
            where m.dp_code == dp.dp_code
            select new 
            {
              cid = c.id,
              cid = c.id
            }.Distinct();

所以,问题:

问题1:为什么我不能在联接中做两个条件?用户错误,或LINQ中的限制?

问题2:如何将不存在添加到查询中?我看过 this question ,但看不到如何实现不存在的子查询。