C# 如何使用 linq 扩展方法执行左外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/584820/
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
How do you perform a left outer join using linq extension methods
提问by LaserJesus
Assuming I have a left outer join as such:
假设我有一个左外连接：
from f in Foo
join b in Bar on f.Foo_Id equals b.Foo_Id into g
from result in g.DefaultIfEmpty()
select new { Foo = f, Bar = result }
How would I express the same task using extension methods? E.g.
我将如何使用扩展方法表达相同的任务？例如
Foo.GroupJoin(Bar, f => f.Foo_Id, b => b.Foo_Id, (f,b) => ???)
.Select(???)
采纳答案by Marc Gravell
For a (left outer) join of a table Barwith a table Fooon Foo.Foo_Id = Bar.Foo_Idin lambda notation:
对于一个表的（左外）连接，Bar用lambda 表示法Foo打开一个表Foo.Foo_Id = Bar.Foo_Id：
var qry = Foo.GroupJoin(
Bar,
foo => foo.Foo_Id,
bar => bar.Foo_Id,
(x,y) => new { Foo = x, Bars = y })
.SelectMany(
x => x.Bars.DefaultIfEmpty(),
(x,y) => new { Foo=x.Foo, Bar=y});
回答by hajirazin
You can create extension method like:
您可以创建扩展方法，如：
public static IEnumerable<TResult> LeftOuterJoin<TSource, TInner, TKey, TResult>(this IEnumerable<TSource> source, IEnumerable<TInner> other, Func<TSource, TKey> func, Func<TInner, TKey> innerkey, Func<TSource, TInner, TResult> res)
{
return from f in source
join b in other on func.Invoke(f) equals innerkey.Invoke(b) into g
from result in g.DefaultIfEmpty()
select res.Invoke(f, result);
}
回答by Ocelot20
Since this seems to be the de facto SO question for left outer joins using the method (extension) syntax, I thought I would add an alternative to the currently selected answer that (in my experience at least) has been more commonly what I'm after
由于这似乎是使用方法（扩展）语法的左外连接的事实上的 SO 问题，我想我会为当前选择的答案添加一个替代方案（至少以我的经验）更常见的是我是后
// Option 1: Expecting either 0 or 1 matches from the "Right"
// table (Bars in this case):
var qry = Foos.GroupJoin(
Bars,
foo => foo.Foo_Id,
bar => bar.Foo_Id,
(f,bs) => new { Foo = f, Bar = bs.SingleOrDefault() });
// Option 2: Expecting either 0 or more matches from the "Right" table
// (courtesy of currently selected answer):
var qry = Foos.GroupJoin(
Bars,
foo => foo.Foo_Id,
bar => bar.Foo_Id,
(f,bs) => new { Foo = f, Bars = bs })
.SelectMany(
fooBars => fooBars.Bars.DefaultIfEmpty(),
(x,y) => new { Foo = x.Foo, Bar = y });
To display the difference using a simple data set (assuming we're joining on the values themselves):
要使用简单的数据集显示差异（假设我们正在加入值本身）：
List<int> tableA = new List<int> { 1, 2, 3 };
List<int?> tableB = new List<int?> { 3, 4, 5 };
// Result using both Option 1 and 2. Option 1 would be a better choice
// if we didn't expect multiple matches in tableB.
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3 }
List<int> tableA = new List<int> { 1, 2, 3 };
List<int?> tableB = new List<int?> { 3, 3, 4 };
// Result using Option 1 would be that an exception gets thrown on
// SingleOrDefault(), but if we use FirstOrDefault() instead to illustrate:
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3 } // Misleading, we had multiple matches.
// Which 3 should get selected (not arbitrarily the first)?.
// Result using Option 2:
{ A = 1, B = null }
{ A = 2, B = null }
{ A = 3, B = 3 }
{ A = 3, B = 3 }
Option 2 is true to the typical left outer join definition, but as I mentioned earlier is often unnecessarily complex depending on the data set.
选项 2 适用于典型的左外连接定义，但正如我之前提到的，根据数据集的不同，通常不必要地复杂。
回答by Gediminas Zimkus
Group Join method is unnecessary to achieve joining of two data sets.
Group Join方法不需要实现两个数据集的join。
Inner Join:
内部联接：
var qry = Foos.SelectMany
(
foo => Bars.Where (bar => foo.Foo_id == bar.Foo_id),
(foo, bar) => new
{
Foo = foo,
Bar = bar
}
);
For Left Join just add DefaultIfEmpty()
对于左连接，只需添加 DefaultIfEmpty()
var qry = Foos.SelectMany
(
foo => Bars.Where (bar => foo.Foo_id == bar.Foo_id).DefaultIfEmpty(),
(foo, bar) => new
{
Foo = foo,
Bar = bar
}
);
EF and LINQ to SQL correctly transform to SQL. For LINQ to Objects it is beter to join using GroupJoin as it internally uses Lookup. But if you are querying DB then skipping of GroupJoin is AFAIK as performant.
EF 和 LINQ to SQL 正确转换为 SQL。 对于 LINQ to Objects，最好使用 GroupJoin 加入，因为它在内部使用 Lookup。但是，如果您正在查询数据库，则跳过 GroupJoin 是 AFAIK 作为高性能。
Personlay for me this way is more readable compared to GroupJoin().SelectMany()
与 GroupJoin().SelectMany() 相比，Personlay 以这种方式更具可读性
回答by Chris Moschini
Improving on Ocelot20's answer, if you have a table you're left outer joining with where you just want 0 or 1 rows out of it, but it could have multiple, you need to Order your joined table:
改进 Ocelot20 的答案，如果你有一个表，你离开了外部连接，你只想要 0 或 1 行，但它可能有多个，你需要订购你的连接表：
var qry = Foos.GroupJoin(
Bars.OrderByDescending(b => b.Id),
foo => foo.Foo_Id,
bar => bar.Foo_Id,
(f, bs) => new { Foo = f, Bar = bs.FirstOrDefault() });
Otherwise which row you get in the join is going to be random (or more specifically, whichever the db happens to find first).
否则，您在连接中获得的哪一行将是随机的（或者更具体地说，无论数据库碰巧首先找到哪一行）。
回答by Harley Waldstein
Turning Marc Gravell's answer into an extension method, I made the following.
将 Marc Gravell 的答案转化为扩展方法，我做了以下内容。
internal static IEnumerable<Tuple<TLeft, TRight>> LeftJoin<TLeft, TRight, TKey>(
this IEnumerable<TLeft> left,
IEnumerable<TRight> right,
Func<TLeft, TKey> selectKeyLeft,
Func<TRight, TKey> selectKeyRight,
TRight defaultRight = default(TRight),
IEqualityComparer<TKey> cmp = null)
{
return left.GroupJoin(
right,
selectKeyLeft,
selectKeyRight,
(x, y) => new Tuple<TLeft, IEnumerable<TRight>>(x, y),
cmp ?? EqualityComparer<TKey>.Default)
.SelectMany(
x => x.Item2.DefaultIfEmpty(defaultRight),
(x, y) => new Tuple<TLeft, TRight>(x.Item1, y));
}
回答by Bob Vale
Whilst the accepted answer works and is good for Linq to Objects it bugged me that the SQL query isn't just a straight Left Outer Join.
虽然接受的答案有效并且对 Linq to Objects 有好处，但它让我觉得 SQL 查询不仅仅是一个直接的左外连接。
The following code relies on the LinkKit Projectthat allows you to pass expressions and invoke them to your query.
以下代码依赖于允许您传递表达式并将它们调用到您的查询的LinkKit 项目。
static IQueryable<TResult> LeftOuterJoin<TSource,TInner, TKey, TResult>(
this IQueryable<TSource> source,
IQueryable<TInner> inner,
Expression<Func<TSource,TKey>> sourceKey,
Expression<Func<TInner,TKey>> innerKey,
Expression<Func<TSource, TInner, TResult>> result
) {
return from a in source.AsExpandable()
join b in inner on sourceKey.Invoke(a) equals innerKey.Invoke(b) into c
from d in c.DefaultIfEmpty()
select result.Invoke(a,d);
}
It can be used as follows
它可以如下使用
Table1.LeftOuterJoin(Table2, x => x.Key1, x => x.Key2, (x,y) => new { x,y});
回答by Dale Fraser
There is an easy solution to this
有一个简单的解决方案
Just use .HasValue in your Select
只需在您的选择中使用 .HasValue
.Select(s => new
{
FooName = s.Foo_Id.HasValue ? s.Foo.Name : "Default Value"
}
Very easy, no need for groupjoin or anything else
非常简单，不需要 groupjoin 或其他任何东西
