我有以下SQL,我正尝试将其转换为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
我已经看到了左外部连接(即into x from y in x.DefaultIfEmpty()等)的典型实现,但是不确定如何引入其他连接条件(AND f.otherid = 17)
into x from y in x.DefaultIfEmpty()
AND f.otherid = 17
编辑
为什么AND f.otherid = 17条件是JOIN而不是WHERE子句的一部分?因为f某些行可能不存在,所以我仍然希望包含这些行。如果在JOIN之后在WHERE子句中应用了条件- 那么我没有得到想要的行为。
f
不幸的是:
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
似乎等效于此:
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()。我只会使用扩展方法语法:
DefaultIfEmpty()
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
或者您可以使用子查询:
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