LINQ - 全面加入-Java 学习之路

153

我有一份人名和他们的名字列表，以及一个人的身份证和姓氏列表 . 有些人没有名字，有些人没有姓氏;我想在两个列表上进行完全外连接 .

所以以下列表：

ID  FirstName
--  ---------
 1  John
 2  Sue

ID  LastName
--  --------
 1  Doe
 3  Smith

应该产生：

ID  FirstName  LastName
--  ---------  --------
 1  John       Doe
 2  Sue
 3             Smith

我是LINQ的新手（如果我是跛脚的话，请原谅我）并找到了很多“LINQ Outer Joins”的解决方案，这些解决方案看起来非常相似，但实际上似乎是留下了外部联接 .

到目前为止，我的尝试是这样的：

private void OuterJoinTest()
{
    List<FirstName> firstNames = new List<FirstName>();
    firstNames.Add(new FirstName { ID = 1, Name = "John" });
    firstNames.Add(new FirstName { ID = 2, Name = "Sue" });

    List<LastName> lastNames = new List<LastName>();
    lastNames.Add(new LastName { ID = 1, Name = "Doe" });
    lastNames.Add(new LastName { ID = 3, Name = "Smith" });

    var outerJoin = from first in firstNames
        join last in lastNames
        on first.ID equals last.ID
        into temp
        from last in temp.DefaultIfEmpty()
        select new
        {
            id = first != null ? first.ID : last.ID,
            firstname = first != null ? first.Name : string.Empty,
            surname = last != null ? last.Name : string.Empty
        };
    }
}

public class FirstName
{
    public int ID;

    public string Name;
}

public class LastName
{
    public int ID;

    public string Name;
}

但这回归：

ID  FirstName  LastName
--  ---------  --------
 1  John       Doe
 2  Sue

我究竟做错了什么？

13 回答

这是一个扩展方法：

public static IEnumerable<KeyValuePair<TLeft, TRight>> FullOuterJoin<TLeft, TRight>(this IEnumerable<TLeft> leftItems, Func<TLeft, object> leftIdSelector, IEnumerable<TRight> rightItems, Func<TRight, object> rightIdSelector)
{
    var leftOuterJoin = from left in leftItems
        join right in rightItems on leftIdSelector(left) equals rightIdSelector(right) into temp
        from right in temp.DefaultIfEmpty()
        select new { left, right };

    var rightOuterJoin = from right in rightItems
        join left in leftItems on rightIdSelector(right) equals leftIdSelector(left) into temp
        from left in temp.DefaultIfEmpty()
        select new { left, right };

    var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);

    return fullOuterJoin.Select(x => new KeyValuePair<TLeft, TRight>(x.left, x.right));
}

回复于 2024-04-19T03:14:53+08:00

两个或多个表的完全外部联接：首先提取要加入的列 .

var DatesA = from A in db.T1 select A.Date; 
var DatesB = from B in db.T2 select B.Date; 
var DatesC = from C in db.T3 select C.Date;            

var Dates = DatesA.Union(DatesB).Union(DatesC);

然后在提取的列和主表之间使用左外连接 .

var Full_Outer_Join =

(from A in Dates
join B in db.T1
on A equals B.Date into AB 

from ab in AB.DefaultIfEmpty()
join C in db.T2
on A equals C.Date into ABC 

from abc in ABC.DefaultIfEmpty()
join D in db.T3
on A equals D.Date into ABCD

from abcd in ABCD.DefaultIfEmpty() 
select new { A, ab, abc, abcd })
.AsEnumerable();

回复于 2024-04-19T03:14:53+08:00

我喜欢sehe的答案，但它不使用延迟执行（输入序列是由ToLookup调用急切枚举的） . 因此，在查看LINQ-to-objects的.NET源代码后，我想出了这个：

public static class LinqExtensions
{
    public static IEnumerable<TResult> FullOuterJoin<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> left,
        IEnumerable<TRight> right,
        Func<TLeft, TKey> leftKeySelector,
        Func<TRight, TKey> rightKeySelector,
        Func<TLeft, TRight, TKey, TResult> resultSelector,
        IEqualityComparer<TKey> comparator = null,
        TLeft defaultLeft = default(TLeft),
        TRight defaultRight = default(TRight))
    {
        if (left == null) throw new ArgumentNullException("left");
        if (right == null) throw new ArgumentNullException("right");
        if (leftKeySelector == null) throw new ArgumentNullException("leftKeySelector");
        if (rightKeySelector == null) throw new ArgumentNullException("rightKeySelector");
        if (resultSelector == null) throw new ArgumentNullException("resultSelector");

        comparator = comparator ?? EqualityComparer<TKey>.Default;
        return FullOuterJoinIterator(left, right, leftKeySelector, rightKeySelector, resultSelector, comparator, defaultLeft, defaultRight);
    }

    internal static IEnumerable<TResult> FullOuterJoinIterator<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> left,
        IEnumerable<TRight> right,
        Func<TLeft, TKey> leftKeySelector,
        Func<TRight, TKey> rightKeySelector,
        Func<TLeft, TRight, TKey, TResult> resultSelector,
        IEqualityComparer<TKey> comparator,
        TLeft defaultLeft,
        TRight defaultRight)
    {
        var leftLookup = left.ToLookup(leftKeySelector, comparator);
        var rightLookup = right.ToLookup(rightKeySelector, comparator);
        var keys = leftLookup.Select(g => g.Key).Union(rightLookup.Select(g => g.Key), comparator);

        foreach (var key in keys)
            foreach (var leftValue in leftLookup[key].DefaultIfEmpty(defaultLeft))
                foreach (var rightValue in rightLookup[key].DefaultIfEmpty(defaultRight))
                    yield return resultSelector(leftValue, rightValue, key);
    }
}

此实现具有以下重要属性：

延迟执行，在枚举输出序列之前不会枚举输入序列 .
仅枚举输入序列 .
保留输入序列的顺序，在某种意义上它将按照左序列的顺序产生元组，然后是右（对于左序列中不存在的键） .

这些属性非常重要，因为它们是FullOuterJoin的新手，但对LINQ经验丰富 .

回复于 2024-04-19T03:14:53+08:00

正如您所发现的，Linq没有“外连接”结构 . 您可以获得的最接近的是使用您所述查询的左外连接 . 为此，您可以添加在联接中未表示的姓氏列表的任何元素：

outerJoin = outerJoin.Concat(lastNames.Select(l=>new
                            {
                                id = l.ID,
                                firstname = String.Empty,
                                surname = l.Name
                            }).Where(l=>!outerJoin.Any(o=>o.id == l.id)));

回复于 2024-04-19T03:14:53+08:00

在两个输入上执行内存中的流枚举，并为每一行调用选择器 . 如果当前迭代中没有相关性， one of the selector arguments will be null .

例：

var result = left.FullOuterJoin(
         right, 
         x=>left.Key, 
         x=>right.Key, 
         (l,r) => new { LeftKey = l?.Key, RightKey=r?.Key });

需要IComparer作为相关类型，如果未提供，则使用Comparer.Default .
要求'OrderBy'应用于输入枚举

/// <summary>
/// Performs a full outer join on two <see cref="IEnumerable{T}" />.
/// </summary>
/// <typeparam name="TLeft"></typeparam>
/// <typeparam name="TValue"></typeparam>
/// <typeparam name="TRight"></typeparam>
/// <typeparam name="TResult"></typeparam>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="leftKeySelector"></param>
/// <param name="rightKeySelector"></param>
/// <param name="selector">Expression defining result type</param>
/// <param name="keyComparer">A comparer if there is no default for the type</param>
/// <returns></returns>
[System.Diagnostics.DebuggerStepThrough]
public static IEnumerable<TResult> FullOuterJoin<TLeft, TRight, TValue, TResult>(
    this IEnumerable<TLeft> left,
    IEnumerable<TRight> right,
    Func<TLeft, TValue> leftKeySelector,
    Func<TRight, TValue> rightKeySelector,
    Func<TLeft, TRight, TResult> selector,
    IComparer<TValue> keyComparer = null)
    where TLeft: class
    where TRight: class
    where TValue : IComparable
{

    keyComparer = keyComparer ?? Comparer<TValue>.Default;

    using (var enumLeft = left.OrderBy(leftKeySelector).GetEnumerator())
    using (var enumRight = right.OrderBy(rightKeySelector).GetEnumerator())
    {

        var hasLeft = enumLeft.MoveNext();
        var hasRight = enumRight.MoveNext();
        while (hasLeft || hasRight)
        {

            var currentLeft = enumLeft.Current;
            var valueLeft = hasLeft ? leftKeySelector(currentLeft) : default(TValue);

            var currentRight = enumRight.Current;
            var valueRight = hasRight ? rightKeySelector(currentRight) : default(TValue);

            int compare =
                !hasLeft ? 1
                : !hasRight ? -1
                : keyComparer.Compare(valueLeft, valueRight);

            switch (compare)
            {
                case 0:
                    // The selector matches. An inner join is achieved
                    yield return selector(currentLeft, currentRight);
                    hasLeft = enumLeft.MoveNext();
                    hasRight = enumRight.MoveNext();
                    break;
                case -1:
                    yield return selector(currentLeft, default(TRight));
                    hasLeft = enumLeft.MoveNext();
                    break;
                case 1:
                    yield return selector(default(TLeft), currentRight);
                    hasRight = enumRight.MoveNext();
                    break;
            }
        }

    }

}

回复于 2024-04-19T03:14:53+08:00

Update 1: providing a truly generalized extension method FullOuterJoin
Update 2: optionally accepting a custom IEqualityComparer for the key type
Update 3 ：这个实现有recently become part of MoreLinq - 谢谢大家！

Edit 已添加 FullOuterGroupJoin （ideone） . 我重用了 GetOuter<> 实现，使得它的性能比它可能的要低一些，但是我现在已经高度优化了代码，而不是前沿优化的代码 .

在 http://ideone.com/O36nWc 上看到它

static void Main(string[] args)
{
    var ax = new[] { 
        new { id = 1, name = "John" },
        new { id = 2, name = "Sue" } };
    var bx = new[] { 
        new { id = 1, surname = "Doe" },
        new { id = 3, surname = "Smith" } };

    ax.FullOuterJoin(bx, a => a.id, b => b.id, (a, b, id) => new {a, b})
        .ToList().ForEach(Console.WriteLine);
}

打印输出：

{ a = { id = 1, name = John }, b = { id = 1, surname = Doe } }
{ a = { id = 2, name = Sue }, b =  }
{ a = , b = { id = 3, surname = Smith } }

您还可以提供默认值： http://ideone.com/kG4kqO

ax.FullOuterJoin(
            bx, a => a.id, b => b.id, 
            (a, b, id) => new { a.name, b.surname },
            new { id = -1, name    = "(no firstname)" },
            new { id = -2, surname = "(no surname)" }
        )

印刷：

{ name = John, surname = Doe }
{ name = Sue, surname = (no surname) }
{ name = (no firstname), surname = Smith }

使用的术语解释：

加入是从关系数据库设计中借用的术语：

A join 将重复 a 中的元素，因为 b 中的元素与相应的键重复（即：如果 b 为空则无效） . 数据库术语称之为 inner (equi)join .
outer join 包含 a 中的元素， b 中没有相应的元素 . （即：如果 b 为空，则结果均匀） . 这通常被称为 left join .
A full outer join 包括来自 a 的记录以及 b ，如果另一个中没有相应的元素 . （即使 a 为空时甚至是结果）

在RDBMS中通常看不到的是组连接[1]：

A group join ，与上述相同， but 而不是从 a 重复多个对应的 b 元素，它将记录与相应的键分组 . 当您希望基于公共密钥枚举'joined'记录时，这通常更方便 .

另见GroupJoin，其中也包含一些一般背景说明 .

[1]（我相信Oracle和MSSQL都有专有扩展）

完整代码

这是一个通用的“插入式”扩展类

internal static class MyExtensions
{
    internal static IEnumerable<TResult> FullOuterGroupJoin<TA, TB, TKey, TResult>(
        this IEnumerable<TA> a,
        IEnumerable<TB> b,
        Func<TA, TKey> selectKeyA, 
        Func<TB, TKey> selectKeyB,
        Func<IEnumerable<TA>, IEnumerable<TB>, TKey, TResult> projection,
        IEqualityComparer<TKey> cmp = null)
    {
        cmp = cmp?? EqualityComparer<TKey>.Default;
        var alookup = a.ToLookup(selectKeyA, cmp);
        var blookup = b.ToLookup(selectKeyB, cmp);

        var keys = new HashSet<TKey>(alookup.Select(p => p.Key), cmp);
        keys.UnionWith(blookup.Select(p => p.Key));

        var join = from key in keys
                   let xa = alookup[key]
                   let xb = blookup[key]
                   select projection(xa, xb, key);

        return join;
    }

    internal static IEnumerable<TResult> FullOuterJoin<TA, TB, TKey, TResult>(
        this IEnumerable<TA> a,
        IEnumerable<TB> b,
        Func<TA, TKey> selectKeyA, 
        Func<TB, TKey> selectKeyB,
        Func<TA, TB, TKey, TResult> projection,
        TA defaultA = default(TA), 
        TB defaultB = default(TB),
        IEqualityComparer<TKey> cmp = null)
    {
        cmp = cmp?? EqualityComparer<TKey>.Default;
        var alookup = a.ToLookup(selectKeyA, cmp);
        var blookup = b.ToLookup(selectKeyB, cmp);

        var keys = new HashSet<TKey>(alookup.Select(p => p.Key), cmp);
        keys.UnionWith(blookup.Select(p => p.Key));

        var join = from key in keys
                   from xa in alookup[key].DefaultIfEmpty(defaultA)
                   from xb in blookup[key].DefaultIfEmpty(defaultB)
                   select projection(xa, xb, key);

        return join;
    }
}

回复于 2024-04-19T03:14:53+08:00

我决定将此作为单独的答案添加，因为我不肯定它已经足够测试了 . 这是 FullOuterJoin 方法的重新实现，主要使用 LINQKit Invoke / Expand 的简化自定义版本 Expression ，以便它可以在实体框架中运行 . 没有太多解释，因为它与我以前的答案几乎相同 .

public static class Ext {
    private static Expression<Func<TP, TC, TResult>> CastSMBody<TP, TC, TResult>(LambdaExpression ex, TP unusedP, TC unusedC, TResult unusedRes) => (Expression<Func<TP, TC, TResult>>)ex;

    public static IQueryable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(
        this IQueryable<TLeft> leftItems,
        IQueryable<TRight> rightItems,
        Expression<Func<TLeft, TKey>> leftKeySelector,
        Expression<Func<TRight, TKey>> rightKeySelector,
        Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class {

        // (lrg,r) => resultSelector(lrg.left, r)
        var sampleAnonLR = new { left = (TLeft)null, rightg = (IEnumerable<TRight>)null };
        var parmP = Expression.Parameter(sampleAnonLR.GetType(), "lrg");
        var parmC = Expression.Parameter(typeof(TRight), "r");
        var argLeft = Expression.PropertyOrField(parmP, "left");
        var newleftrs = CastSMBody(Expression.Lambda(resultSelector.Apply(argLeft, parmC), new[] { parmP, parmC }), sampleAnonLR, (TRight)null, (TResult)null);

        return leftItems.GroupJoin(rightItems, leftKeySelector, rightKeySelector, (left, rightg) => new { left, rightg }).SelectMany(r => r.rightg.DefaultIfEmpty(), newleftrs);
    }

    public static IQueryable<TResult> RightOuterJoin<TLeft, TRight, TKey, TResult>(
        this IQueryable<TLeft> leftItems,
        IQueryable<TRight> rightItems,
        Expression<Func<TLeft, TKey>> leftKeySelector,
        Expression<Func<TRight, TKey>> rightKeySelector,
        Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class {

        // (lgr,l) => resultSelector(l, lgr.right)
        var sampleAnonLR = new { leftg = (IEnumerable<TLeft>)null, right = (TRight)null };
        var parmP = Expression.Parameter(sampleAnonLR.GetType(), "lgr");
        var parmC = Expression.Parameter(typeof(TLeft), "l");
        var argRight = Expression.PropertyOrField(parmP, "right");
        var newrightrs = CastSMBody(Expression.Lambda(resultSelector.Apply(parmC, argRight), new[] { parmP, parmC }), sampleAnonLR, (TLeft)null, (TResult)null);

        return rightItems.GroupJoin(leftItems, rightKeySelector, leftKeySelector, (right, leftg) => new { leftg, right })
                         .SelectMany(l => l.leftg.DefaultIfEmpty(), newrightrs);
    }

    private static Expression<Func<TParm, TResult>> CastSBody<TParm, TResult>(LambdaExpression ex, TParm unusedP, TResult unusedRes) => (Expression<Func<TParm, TResult>>)ex;

    public static IQueryable<TResult> RightAntiSemiJoin<TLeft, TRight, TKey, TResult>(
        this IQueryable<TLeft> leftItems,
        IQueryable<TRight> rightItems,
        Expression<Func<TLeft, TKey>> leftKeySelector,
        Expression<Func<TRight, TKey>> rightKeySelector,
        Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class {

        // newrightrs = lgr => resultSelector((TLeft)null, lgr.right)
        var sampleAnonLgR = new { leftg = (IEnumerable<TLeft>)null, right = (TRight)null };
        var parmLgR = Expression.Parameter(sampleAnonLgR.GetType(), "lgr");
        var argLeft = Expression.Constant(null, typeof(TLeft));
        var argRight = Expression.PropertyOrField(parmLgR, "right");
        var newrightrs = CastSBody(Expression.Lambda(resultSelector.Apply(argLeft, argRight), new[] { parmLgR }), sampleAnonLgR, (TResult)null);

        return rightItems.GroupJoin(leftItems, rightKeySelector, leftKeySelector, (right, leftg) => new { leftg, right }).Where(lgr => !lgr.leftg.Any()).Select(newrightrs);
    }

    public static IQueryable<TResult> FullOuterJoin<TLeft, TRight, TKey, TResult>(
        this IQueryable<TLeft> leftItems,
        IQueryable<TRight> rightItems,
        Expression<Func<TLeft, TKey>> leftKeySelector,
        Expression<Func<TRight, TKey>> rightKeySelector,
        Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class {

        return leftItems.LeftOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector).Concat(leftItems.RightAntiSemiJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector));
    }

    public static Expression Apply(this LambdaExpression e, params Expression[] args) {
        var b = e.Body;

        foreach (var pa in e.Parameters.Cast<ParameterExpression>().Zip(args, (p, a) => (p, a))) {
            b = b.Swap(pa.p, pa.a);
        }

        return b.PropagateNull();
    }

    public static Expression Swap(this Expression orig, Expression from, Expression to) => new SwapVisitor(from, to).Visit(orig);
    public class SwapVisitor : System.Linq.Expressions.ExpressionVisitor {
        public readonly Expression from;
        public readonly Expression to;

        public SwapVisitor(Expression _from, Expression _to) {
            from = _from;
            to = _to;
        }

        public override Expression Visit(Expression node) => node == from ? to : base.Visit(node);
    }

    public static Expression PropagateNull(this Expression orig) => new NullVisitor().Visit(orig);
    public class NullVisitor : System.Linq.Expressions.ExpressionVisitor {
        public override Expression Visit(Expression node) {
            if (node is MemberExpression nme && nme.Expression is ConstantExpression nce && nce.Value == null)
                return Expression.Constant(null, nce.Type.GetMember(nme.Member.Name).Single().GetMemberType());
            else
                return base.Visit(node);
        }
    }

    public static Type GetMemberType(this MemberInfo member) {
        switch (member) {
            case FieldInfo mfi:
                return mfi.FieldType;
            case PropertyInfo mpi:
                return mpi.PropertyType;
            case EventInfo mei:
                return mei.EventHandlerType;
            default:
                throw new ArgumentException("MemberInfo must be if type FieldInfo, PropertyInfo or EventInfo", nameof(member));
        }
    }
}

回复于 2024-04-19T03:14:53+08:00

1
我的方法更强，但在我更好地理解之前，我发现自己从@ MichaelSander的扩展中跳出来 . 我修改它以匹配here描述的内置Enumerable.Join（）方法的语法和返回类型 . 我在@ cadrell0 's comment under @JeffMercado' s解决方案中添加了"distinct"后缀 .
```
public static class MyExtensions {

    public static IEnumerable<TResult> FullJoinDistinct<TLeft, TRight, TKey, TResult> (
        this IEnumerable<TLeft> leftItems, 
        IEnumerable<TRight> rightItems, 
        Func<TLeft, TKey> leftKeySelector, 
        Func<TRight, TKey> rightKeySelector,
        Func<TLeft, TRight, TResult> resultSelector
    ) {

        var leftJoin = 
            from left in leftItems
            join right in rightItems 
              on leftKeySelector(left) equals rightKeySelector(right) into temp
            from right in temp.DefaultIfEmpty()
            select resultSelector(left, right);

        var rightJoin = 
            from right in rightItems
            join left in leftItems 
              on rightKeySelector(right) equals leftKeySelector(left) into temp
            from left in temp.DefaultIfEmpty()
            select resultSelector(left, right);

        return leftJoin.Union(rightJoin);
    }

}
```
在示例中，您将使用它：
```
var test = 
    firstNames
    .FullJoinDistinct(
        lastNames,
        f=> f.ID,
        j=> j.ID,
        (f,j)=> new {
            ID = f == null ? j.ID : f.ID, 
            leftName = f == null ? null : f.Name,
            rightName = j == null ? null : j.Name
        }
    );
```
在将来，随着我的了解，我有一种感觉，我会转移到@ sehe的逻辑，因为它很受欢迎 . 但即使这样，我也要小心，因为我认为至少有一个重载与现有语法相匹配很重要“.Join（）”方法如果可行，原因有两个：
- 方法的一致性有助于节省时间，避免错误并避免意外行为 .
- 如果将来有一个开箱即用的".FullJoin()"方法，我想它会尽量保持当前存在的".Join()"方法的语法 . 如果确实如此，那么如果要迁移到它，只需重命名函数而不更改参数或担心不同的返回类型会破坏代码 .
我仍然是泛型，扩展，Func语句和其他功能的新手，所以反馈当然是受欢迎的 .

EDIT: 我没有花很长时间才意识到我的代码存在问题 . 我在LINQPad中做了一个.Dump（）并查看了返回类型 . 它只是IEnumerable，所以我试着匹配它 . 但是当我在扩展程序上实际执行了.Where（）或.Select（）时，我收到了一个错误："'System Collections.IEnumerable' does not contain a definition for 'Select' and ..." . 所以最后我能够匹配.Join（）的输入语法，但不能匹配返回行为 .

EDIT: 将"TResult"添加到函数的返回类型 . 错过了在阅读微软文章时，当然这是有道理的 . 有了这个修复，现在似乎返回行为完全符合我的目标 .
回复于 2024-04-19T03:14:53+08:00
7
我真的很讨厌这些linq表达式，这就是SQL存在的原因：
```
select isnull(fn.id, ln.id) as id, fn.firstname, ln.lastname
   from firstnames fn
   full join lastnames ln on ln.id=fn.id
```
在数据库中将其创建为sql视图并将其作为实体导入 .

当然，左右连接的（不同）联合也会成为它，但它是愚蠢的 .
回复于 2024-04-19T03:14:53+08:00

我不知道这是否涵盖了所有情况，从逻辑上看似乎是正确的 . 我们的想法是采用左外连接和右外连接并将它们组合在一起（应该如此） .

var firstNames = new[]
{
    new { ID = 1, Name = "John" },
    new { ID = 2, Name = "Sue" },
};
var lastNames = new[]
{
    new { ID = 1, Name = "Doe" },
    new { ID = 3, Name = "Smith" },
};
var leftOuterJoin = from first in firstNames
                    join last in lastNames
                    on first.ID equals last.ID
                    into temp
                    from last in temp.DefaultIfEmpty(new { first.ID, Name = default(string) })
                    select new
                    {
                        first.ID,
                        FirstName = first.Name,
                        LastName = last.Name,
                    };
var rightOuterJoin = from last in lastNames
                     join first in firstNames
                     on last.ID equals first.ID
                     into temp
                     from first in temp.DefaultIfEmpty(new { last.ID, Name = default(string) })
                     select new
                     {
                         last.ID,
                         FirstName = first.Name,
                         LastName = last.Name,
                     };
var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);

这是写的，因为它在LINQ to Objects中 . 如果LINQ to SQL或其他，默认情况下 DefaultIfEmpty() 的重载可能无效 . 然后你必须使用条件运算符来有条件地获取值 .

即，

var leftOuterJoin = from first in firstNames
                    join last in lastNames
                    on first.ID equals last.ID
                    into temp
                    from last in temp.DefaultIfEmpty()
                    select new
                    {
                        first.ID,
                        FirstName = first.Name,
                        LastName = last != null ? last.Name : default(string),
                    };

回复于 2024-04-19T03:14:53+08:00

-3

我可能在6年前为一个应用程序编写了这个扩展类，并且在许多解决方案中一直使用它而没有问题 . 希望能帮助到你 .

public static class JoinExtensions
{
    public static IEnumerable<TResult> FullOuterJoin<TOuter, TInner, TKey, TResult>(
        this IEnumerable<TOuter> outer,
        IEnumerable<TInner> inner,
        Func<TOuter, TKey> outerKeySelector,
        Func<TInner, TKey> innerKeySelector,
        Func<TOuter, TInner, TResult> resultSelector)
        where TInner : class
        where TOuter : class
    {
        var innerLookup = inner.ToLookup(innerKeySelector);
        var outerLookup = outer.ToLookup(outerKeySelector);

        var innerJoinItems = inner
            .Where(innerItem => !outerLookup.Contains(innerKeySelector(innerItem)))
            .Select(innerItem => resultSelector(null, innerItem));

        return outer
            .SelectMany(outerItem =>
            {
                var innerItems = innerLookup[outerKeySelector(outerItem)];

                return innerItems.Any() ? innerItems : new TInner[] { null };
            }, resultSelector)
            .Concat(innerJoinItems);
    }


    public static IEnumerable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>(
        this IEnumerable<TOuter> outer,
        IEnumerable<TInner> inner,
        Func<TOuter, TKey> outerKeySelector,
        Func<TInner, TKey> innerKeySelector,
        Func<TOuter, TInner, TResult> resultSelector)
    {
        return outer.GroupJoin(
            inner,
            outerKeySelector,
            innerKeySelector,
            (o, i) =>
                new { o = o, i = i.DefaultIfEmpty() })
                .SelectMany(m => m.i.Select(inn =>
                    resultSelector(m.o, inn)
                    ));

    }



    public static IEnumerable<TResult> RightJoin<TOuter, TInner, TKey, TResult>(
        this IEnumerable<TOuter> outer,
        IEnumerable<TInner> inner,
        Func<TOuter, TKey> outerKeySelector,
        Func<TInner, TKey> innerKeySelector,
        Func<TOuter, TInner, TResult> resultSelector)
    {
        return inner.GroupJoin(
            outer,
            innerKeySelector,
            outerKeySelector,
            (i, o) =>
                new { i = i, o = o.DefaultIfEmpty() })
                .SelectMany(m => m.o.Select(outt =>
                    resultSelector(outt, m.i)
                    ));

    }

}

回复于 2024-04-19T03:14:53+08:00

我认为大多数这些问题都存在问题，包括已接受的答案，因为它们不能很好地与Linq相比IQueryable，因为服务器往返次数太多，数据返回太多，或客户端执行太多 .

对于IEnumerable我不喜欢Sehe的答案或类似因为它有过多的内存使用（一个简单的10000000两列表测试在我的32GB机器上运行Linqpad内存不足） .

此外，其他大多数实际上并没有实现正确的Full Outer Join，因为他们使用具有Right Join的Union而不是带有Right Anti Semi Join的Concat，这不仅消除了结果中重复的内部连接行，而且最初在左侧或右侧数据中存在的任何适当的重复项 .

所以这里是我的扩展，处理所有这些问题，生成SQL直接在Linq中实现连接，在服务器上执行，并且比Enumerables上的其他更快，内存更少：

public static class Ext {
    public static IEnumerable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> leftItems,
        IEnumerable<TRight> rightItems,
        Func<TLeft, TKey> leftKeySelector,
        Func<TRight, TKey> rightKeySelector,
        Func<TLeft, TRight, TResult> resultSelector) {

        return from left in leftItems
               join right in rightItems on leftKeySelector(left) equals rightKeySelector(right) into temp
               from right in temp.DefaultIfEmpty()
               select resultSelector(left, right);
    }

    public static IEnumerable<TResult> RightOuterJoin<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> leftItems,
        IEnumerable<TRight> rightItems,
        Func<TLeft, TKey> leftKeySelector,
        Func<TRight, TKey> rightKeySelector,
        Func<TLeft, TRight, TResult> resultSelector) {

        return from right in rightItems
               join left in leftItems on rightKeySelector(right) equals leftKeySelector(left) into temp
               from left in temp.DefaultIfEmpty()
               select resultSelector(left, right);
    }

    public static IEnumerable<TResult> FullOuterJoinDistinct<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> leftItems,
        IEnumerable<TRight> rightItems,
        Func<TLeft, TKey> leftKeySelector,
        Func<TRight, TKey> rightKeySelector,
        Func<TLeft, TRight, TResult> resultSelector) {

        return leftItems.LeftOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector).Union(leftItems.RightOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector));
    }

    public static IEnumerable<TResult> RightAntiSemiJoin<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> leftItems,
        IEnumerable<TRight> rightItems,
        Func<TLeft, TKey> leftKeySelector,
        Func<TRight, TKey> rightKeySelector,
        Func<TLeft, TRight, TResult> resultSelector) where TLeft : class {

        var hashLK = new HashSet<TKey>(from l in leftItems select leftKeySelector(l));
        return rightItems.Where(r => !hashLK.Contains(rightKeySelector(r))).Select(r => resultSelector((TLeft)null,r));
    }

    public static IEnumerable<TResult> FullOuterJoin<TLeft, TRight, TKey, TResult>(
        this IEnumerable<TLeft> leftItems,
        IEnumerable<TRight> rightItems,
        Func<TLeft, TKey> leftKeySelector,
        Func<TRight, TKey> rightKeySelector,
        Func<TLeft, TRight, TResult> resultSelector)  where TLeft : class {

        return leftItems.LeftOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector).Concat(leftItems.RightAntiSemiJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector));
    }

    private static Expression<Func<TP, TC, TResult>> CastSMBody<TP, TC, TResult>(LambdaExpression ex, TP unusedP, TC unusedC, TResult unusedRes) => (Expression<Func<TP, TC, TResult>>)ex;

    public static IQueryable<TResult> LeftOuterJoin<TLeft, TRight, TKey, TResult>(
        this IQueryable<TLeft> leftItems,
        IQueryable<TRight> rightItems,
        Expression<Func<TLeft, TKey>> leftKeySelector,
        Expression<Func<TRight, TKey>> rightKeySelector,
        Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class {

        var sampleAnonLR = new { left = (TLeft)null, rightg = (IEnumerable<TRight>)null };
        var parmP = Expression.Parameter(sampleAnonLR.GetType(), "p");
        var parmC = Expression.Parameter(typeof(TRight), "c");
        var argLeft = Expression.PropertyOrField(parmP, "left");
        var newleftrs = CastSMBody(Expression.Lambda(Expression.Invoke(resultSelector, argLeft, parmC), new[] { parmP, parmC }), sampleAnonLR, (TRight)null, (TResult)null);

        return leftItems.AsQueryable().GroupJoin(rightItems, leftKeySelector, rightKeySelector, (left, rightg) => new { left, rightg }).SelectMany(r => r.rightg.DefaultIfEmpty(), newleftrs);
    }

    public static IQueryable<TResult> RightOuterJoin<TLeft, TRight, TKey, TResult>(
        this IQueryable<TLeft> leftItems,
        IQueryable<TRight> rightItems,
        Expression<Func<TLeft, TKey>> leftKeySelector,
        Expression<Func<TRight, TKey>> rightKeySelector,
        Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class {

        var sampleAnonLR = new { leftg = (IEnumerable<TLeft>)null, right = (TRight)null };
        var parmP = Expression.Parameter(sampleAnonLR.GetType(), "p");
        var parmC = Expression.Parameter(typeof(TLeft), "c");
        var argRight = Expression.PropertyOrField(parmP, "right");
        var newrightrs = CastSMBody(Expression.Lambda(Expression.Invoke(resultSelector, parmC, argRight), new[] { parmP, parmC }), sampleAnonLR, (TLeft)null, (TResult)null);

        return rightItems.GroupJoin(leftItems, rightKeySelector, leftKeySelector, (right, leftg) => new { leftg, right }).SelectMany(l => l.leftg.DefaultIfEmpty(), newrightrs);
    }

    public static IQueryable<TResult> FullOuterJoinDistinct<TLeft, TRight, TKey, TResult>(
        this IQueryable<TLeft> leftItems,
        IQueryable<TRight> rightItems,
        Expression<Func<TLeft, TKey>> leftKeySelector,
        Expression<Func<TRight, TKey>> rightKeySelector,
        Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class {

        return leftItems.LeftOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector).Union(leftItems.RightOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector));
    }

    private static Expression<Func<TP, TResult>> CastSBody<TP, TResult>(LambdaExpression ex, TP unusedP, TResult unusedRes) => (Expression<Func<TP, TResult>>)ex;

    public static IQueryable<TResult> RightAntiSemiJoin<TLeft, TRight, TKey, TResult>(
        this IQueryable<TLeft> leftItems,
        IQueryable<TRight> rightItems,
        Expression<Func<TLeft, TKey>> leftKeySelector,
        Expression<Func<TRight, TKey>> rightKeySelector,
        Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class {

        var sampleAnonLgR = new { leftg = (IEnumerable<TLeft>)null, right = (TRight)null };
        var parmLgR = Expression.Parameter(sampleAnonLgR.GetType(), "lgr");
        var argLeft = Expression.Constant(null, typeof(TLeft));
        var argRight = Expression.PropertyOrField(parmLgR, "right");
        var newrightrs = CastSBody(Expression.Lambda(Expression.Invoke(resultSelector, argLeft, argRight), new[] { parmLgR }), sampleAnonLgR, (TResult)null);

        return rightItems.GroupJoin(leftItems, rightKeySelector, leftKeySelector, (right, leftg) => new { leftg, right }).Where(lgr => !lgr.leftg.Any()).Select(newrightrs);
    }

    public static IQueryable<TResult> FullOuterJoin<TLeft, TRight, TKey, TResult>(
        this IQueryable<TLeft> leftItems,
        IQueryable<TRight> rightItems,
        Expression<Func<TLeft, TKey>> leftKeySelector,
        Expression<Func<TRight, TKey>> rightKeySelector,
        Expression<Func<TLeft, TRight, TResult>> resultSelector) where TLeft : class where TRight : class where TResult : class {

        return leftItems.LeftOuterJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector).Concat(leftItems.RightAntiSemiJoin(rightItems, leftKeySelector, rightKeySelector, resultSelector));
    }
}

Right Anti-Semi-Join之间的区别主要是Linq to Objects或源代码，但在最终答案的服务器（SQL）方面有所不同，删除了不必要的 JOIN .

使用LinqKit可以改进 Expression 处理将 Expression<Func<>> 合并为lambda的手动编码，但如果语言/编译器为此添加了一些帮助，那将会很好 . 包含 FullOuterJoinDistinct 和 RightOuterJoin 函数是为了完整性，但我还没有重新实现 FullOuterGroupJoin .

对于可以订购密钥的情况，我为 IEnumerable 编写了another version的完整外连接，这比将左外连接与右反半连接组合快约50％，至少在小集合上 . 它只排序一次后通过每个集合 .

回复于 2024-04-19T03:14:53+08:00

160

我的干净解决方案是关键在两个枚举中都是唯一的：

private static IEnumerable<TResult> FullOuterJoin<Ta, Tb, TKey, TResult>(
            IEnumerable<Ta> a, IEnumerable<Tb> b,
            Func<Ta, TKey> key_a, Func<Tb, TKey> key_b,
            Func<Ta, Tb, TResult> selector)
        {
            var alookup = a.ToLookup(key_a);
            var blookup = b.ToLookup(key_b);
            var keys = new HashSet<TKey>(alookup.Select(p => p.Key));
            keys.UnionWith(blookup.Select(p => p.Key));
            return keys.Select(key => selector(alookup[key].FirstOrDefault(), blookup[key].FirstOrDefault()));
        }

所以

var ax = new[] {
        new { id = 1, first_name = "ali" },
        new { id = 2, first_name = "mohammad" } };
    var bx = new[] {
        new { id = 1, last_name = "rezaei" },
        new { id = 3, last_name = "kazemi" } };

    var list = FullOuterJoin(ax, bx, a => a.id, b => b.id, (a, b) => "f: " + a?.first_name + " l: " + b?.last_name).ToArray();

输出：

f: ali l: rezaei
f: mohammad l:
f:  l: kazemi

回复于 2024-04-19T03:14:53+08:00

LINQ - 全面加入

13 回答

使用的术语解释：

完整代码

相关问题