C# LINQ to SQL - 具有多个连接条件的左外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1122942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LINQ to SQL - Left Outer Join with multiple join conditions
提问by dan
I have the following SQL, which I am trying to translate to LINQ:
我有以下 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
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)
我已经看到了左外连接（即into x from y in x.DefaultIfEmpty()等）的典型实现，但我不确定如何引入其他连接条件 ( AND f.otherid = 17)
EDIT
编辑
Why is the AND f.otherid = 17condition part of the JOIN instead of in the WHERE clause?
Because fmay 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.
为什么AND f.otherid = 17条件是 JOIN 的一部分而不是 WHERE 子句？因为f某些行可能不存在，我仍然希望包含这些行。如果条件应用于 WHERE 子句，在 JOIN 之后 - 那么我不会得到我想要的行为。
Unfortunately this:
不幸的是：
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
which is not quite what I'm after.
这不是我所追求的。
采纳答案by dahlbyk
You need to introduce your join condition before calling DefaultIfEmpty(). I would just use extension method syntax:
您需要在调用之前介绍您的加入条件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
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
回答by MAbraham1
Another valid option is to spread the joins across multiple LINQ clauses, as follows:
另一个有效的选择是将连接分布在多个 LINQ 子句中，如下所示：
public static IEnumerable<Announcementboard> GetSiteContent(string pageName, DateTime date)
{
IEnumerable<Announcementboard> content = null;
IEnumerable<Announcementboard> addMoreContent = null;
try
{
content = from c in DB.Announcementboards
// Can be displayed beginning on this date
where c.Displayondate > date.AddDays(-1)
// Doesn't Expire or Expires at future date
&& (c.Displaythrudate == null || c.Displaythrudate > date)
// Content is NOT draft, and IS published
&& c.Isdraft == "N" && c.Publishedon != null
orderby c.Sortorder ascending, c.Heading ascending
select c;
// Get the content specific to page names
if (!string.IsNullOrEmpty(pageName))
{
addMoreContent = from c in content
join p in DB.Announceonpages on c.Announcementid equals p.Announcementid
join s in DB.Apppagenames on p.Apppagenameid equals s.Apppagenameid
where s.Apppageref.ToLower() == pageName.ToLower()
select c;
}
// Add the specified content using UNION
content = content.Union(addMoreContent);
// Exclude the duplicates using DISTINCT
content = content.Distinct();
return content;
}
catch (MyLovelyException ex)
{
// Add your exception handling here
throw ex;
}
}
回答by ZenXavier
this works too, ...if you have multiple column joins
这也有效，...如果您有多个列连接
from p in context.Periods
join f in context.Facts
on new {
id = p.periodid,
p.otherid
} equals new {
f.id,
f.otherid
} into fg
from fgi in fg.DefaultIfEmpty()
where p.companyid == 100
select f.value
回答by onedaywhen
It seems to me there is value in considering some rewrites to your SQL code before attempting to translate it.
在我看来，在尝试翻译之前考虑对 SQL 代码进行一些重写是有价值的。
Personally, I'd write such a query as a union (although I'd avoid nulls entirely!):
就个人而言，我会将这样的查询写为联合（尽管我会完全避免空值！）：
SELECT f.value
FROM period as p JOIN facts AS f ON p.id = f.periodid
WHERE p.companyid = 100
AND f.otherid = 17
UNION
SELECT NULL AS value
FROM period as p
WHERE p.companyid = 100
AND NOT EXISTS (
SELECT *
FROM facts AS f
WHERE p.id = f.periodid
AND f.otherid = 17
);
So I guess I agree with the spirit of @MAbraham1's answer (though their code seems to be unrelated to the question).
所以我想我同意@MAbraham1 回答的精神（尽管他们的代码似乎与问题无关）。
However, it seems the query is expressly designed to produce a single column result comprising duplicate rows -- indeed duplicate nulls! It's hard not to come to the conclusion that this approach is flawed.
但是，该查询似乎是专门为生成包含重复行的单列结果而设计的——实际上是重复的空值！很难不得出这种方法有缺陷的结论。
回答by Prokurors
I know it's "a bit late" but just in case if anybody needs to do this in LINQ Method syntax(which is why I found this post initially), this would be how to do that:
我知道这“有点晚了”，但以防万一如果有人需要在LINQ 方法语法中执行此操作（这就是我最初发现这篇文章的原因），这将是如何做到这一点：
var results = context.Periods
.GroupJoin(
context.Facts,
period => period.id,
fk => fk.periodid,
(period, fact) => fact.Where(f => f.otherid == 17)
.Select(fact.Value)
.DefaultIfEmpty()
)
.Where(period.companyid==100)
.SelectMany(fact=>fact).ToList();
回答by Petr Felzmann
Can be written using composite join key. Also if there is need to select properties from both left and right sides the LINQ can be written as
可以使用复合连接键编写。此外，如果需要从左侧和右侧选择属性，则 LINQ 可以写为
var result = context.Periods
.Where(p => p.companyid == 100)
.GroupJoin(
context.Facts,
p => new {p.id, otherid = 17},
f => new {id = f.periodid, f.otherid},
(p, f) => new {p, f})
.SelectMany(
pf => pf.f.DefaultIfEmpty(),
(pf, f) => new MyJoinEntity
{
Id = pf.p.id,
Value = f.value,
// and so on...
});
