LINQ to Entites：如何在LINQ中实现这个复杂的t-sql（多个连接，DISTINCT，NOT EXISTS）？

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

我有一个复杂的查询,我试图在LINQ to Entities中重现,但我还没有 - 有可能吗?

The t-sql query looks like:

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 )

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:

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

So, questions:

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

Q1:为什么我不能在加入中做两个条件?用户错误或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.

Q2:如何将NOT EXISTS添加到查询中?我看过这个问题,但看不到如何实现NOT EXISTS子查询。

1 个解决方案

#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 }

   你可以,但根本不做连接通常是错误的。但是,如果必须,你使用匿名类型:on new {l:l.l_code,d:dp.code}等于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.

   在哪里!(从地图中的m到任何选择m).Any()。但与(1)一样,最好使用关联。