LINQ to Entites: how can I implement this complex t-sql in LINQ (multiple joins, DISTINCT, NOT EXISTS) ?

Question

I have a complex query that I'm trying to reproduce in LINQ to Entities, but I'm not there yet - is it possible?

The t-sql query looks like:

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 )

Tables look like:

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)

My LINQ looks like:

  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();

So, questions:

Q1: Why can't I do two conditions in the join? User error, or limitations in LINQ?

Q2: How can I add the NOT EXISTS to the query? I've looked at this question, but can't see how to implement the NOT EXISTS subquery.

Answer 1

1. You can, but it's usually wrong to do a join at all. Still, if you must, you use anonymous types: on new { l: l.l_code, d: dp.code } equals new { l: m_code, d: m.dp_code }

2. where !(from m in map where whatever select m).Any(). But as with (1), it's better to use associations.