LINQ to SQL的 - 左外连接有多个连接条件 [英] LINQ to SQL - Left Outer Join with multiple join conditions

问题描述

我有以下SQL，这我试图转换为LINQ：

I have the following SQL, which I am trying to translate to LINQ:

SELECT f.value
FROM period as p 
LEFT OUTER JOIN facts AS f ON p.id = f.periodid AND f.otherid = 17
WHERE p.companyid = 100

我见过的左外的典型的实现联接（即成x.DefaultIfEmpty（）等，从Y X），但我不知道该如何介绍其他连接条件（键，f.otherid = 17 ）

I have seen the typical implementation of the left outer join (ie. into x from y in x.DefaultIfEmpty() etc.) but am unsure how to introduce the other join condition (AND f.otherid = 17)

修改

为什么键，f.otherid = 17 情况的JOIN，而不是在WHERE子句中的一部分？
因为˚F可能不存在某些行，我仍然要包括这些行。如果WHERE子句中应用的情况下，在连接后 - 当时我没有得到我想要的行为

Why is the AND f.otherid = 17 condition part of the JOIN instead of in the WHERE clause? Because f may not exist for some rows and I still want these rows to be included. If the condition is applied in the WHERE clause, after the JOIN - then I don't get the behaviour I want.

不幸的：

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in fg.DefaultIfEmpty()
where p.companyid == 100 && fgi.otherid == 17
select f.value

似乎是等效于这样的：

seems to be equivalent to this:

SELECT f.value
FROM period as p 
LEFT OUTER JOIN facts AS f ON p.id = f.periodid 
WHERE p.companyid = 100 AND f.otherid = 17

这是不太我后。

推荐答案

您需要调用的 DefaultIfEmpty（） 。我只想用扩展方法的语法：

You need to introduce your join condition before calling DefaultIfEmpty(). I would just use extension method syntax:

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty()
where p.companyid == 100
select f.value

或者你可以使用子查询：

Or you could use a subquery:

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in (from f in fg
             where f.otherid == 17
             select f).DefaultIfEmpty()
where p.companyid == 100
select f.value

这篇关于LINQ to SQL的 - 左外连接有多个连接条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！