对于表 Bar
与 Foo.Foo_Id = Bar.Foo_Id
上的表 Foo
的(左外)联接,采用 lambda 表示法:
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});
由于这似乎是使用方法(扩展)语法的左外连接的事实上的 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 });
使用简单的数据集显示差异(假设我们正在加入值本身):
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 }
选项 2 适用于典型的左外连接定义,但正如我之前提到的,根据数据集的不同,它通常过于复杂。
Single
检查。但是,SingleOrDefault
是演示此 IMO 的更“正确”的方式。
Group Join 方法对于实现两个数据集的连接是不必要的。
内部联接:
var qry = Foos.SelectMany
(
foo => Bars.Where (bar => foo.Foo_id == bar.Foo_id),
(foo, bar) => new
{
Foo = foo,
Bar = bar
}
);
对于左连接,只需添加 DefaultIfEmpty()
var qry = Foos.SelectMany
(
foo => Bars.Where (bar => foo.Foo_id == bar.Foo_id).DefaultIfEmpty(),
(foo, bar) => new
{
Foo = foo,
Bar = bar
}
);
EF 和 LINQ to SQL 正确转换为 SQL。对于 LINQ to Objects,最好使用 GroupJoin 加入,因为它在内部使用 Lookup。但是,如果您正在查询数据库,那么跳过 GroupJoin 是 AFAIK 的表现。
与 GroupJoin().SelectMany() 相比,这种方式对我来说 Personlay 更具可读性
您可以创建扩展方法,例如:
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);
}
改进 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() });
否则,您在联接中获得的哪一行将是随机的(或者更具体地说,无论哪个数据库碰巧先找到)。
虽然接受的答案有效并且对 Linq to Objects 有好处,但它让我感到烦恼的是 SQL 查询不仅仅是一个直接的左外连接。
以下代码依赖于 LinqKit Project,它允许您传递表达式并将它们调用到您的查询中。
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);
}
可以如下使用
Table1.LeftOuterJoin(Table2, x => x.Key1, x => x.Key2, (x,y) => new { x,y});
将 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));
}
Marc Gravell 的回答变成了支持 IQueryable<T>
接口的扩展方法,在 this answer 中给出,并增加了对 C# 8.0 NRT 的支持,如下所示:
#nullable enable
using LinqKit;
using LinqKit.Core;
using System.Linq.Expressions;
...
/// <summary>
/// Left join queryable. Linq to SQL compatible. IMPORTANT: any Includes must be put on the source collections before calling this method.
/// </summary>
public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
this IQueryable<TOuter> outer,
IQueryable<TInner> inner,
Expression<Func<TOuter, TKey>> outerKeySelector,
Expression<Func<TInner, TKey>> innerKeySelector,
Expression<Func<TOuter, TInner?, TResult>> resultSelector)
{
return outer
.AsExpandable()
.GroupJoin(
inner,
outerKeySelector,
innerKeySelector,
(outerItem, innerItems) => new { outerItem, innerItems })
.SelectMany(
joinResult => joinResult.innerItems.DefaultIfEmpty(),
(joinResult, innerItem) =>
resultSelector.Invoke(joinResult.outerItem, innerItem));
}
不定期副业成功案例分享
GroupJoin
执行左外连接,仅需要SelectMany
部分,具体取决于您要选择的内容。only
右侧列全部为空的行(当匹配不满足时SQL Server外连接中的情况)?