c# - visual - mssql to linq




LINQ-完全外部加入 (8)

更新1:提供真正的通用擴展方法FullOuterJoin
更新2:可選地接受關鍵字類型的自定義IEqualityComparer
更新3 :這個實現最近成為MoreLinq一部分 - 謝謝你們!

編輯添加了FullOuterGroupJoinideone )。 我重用了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 } }

您也可以提供默認值: ideone : ideone

    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 }

所用術語的解釋:

加入是從關係數據庫設計借用的術語:

  • 一個連接會重複元素的次數,因為b中的元素具有相應的鍵值 (即:如果b是空的,則沒有任何元素)。 數據庫術語稱這個inner (equi)join
  • 外連接包括b 沒有對應元素b 元素 。 (即:如果b為空,則結果甚至)。 這通常被稱為left join
  • 如果另一個中沒有相應的元素 ,則完整的外部聯接包括a b記錄。 (即,如果a是空的,甚至會產生結果)

RDBMS中通常不會看到的是組加入[1]

  • 一個組連接 ,和上面描述的一樣, 但是不是為了多個對應的b而從a中重複元素, 而是用相應的鍵對記錄進行分組 。 當您想通過基於共同密鑰的“加入”記錄進行枚舉時,這通常會更方便。

另請參閱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;
    }
}

我有一個人的ID和他們的名字列表,以及一個人的ID和他們的姓氏列表。 有些人沒有名字,有的沒有姓; 我想在這兩個列表上完成一個完整的外連接。

所以下面的列表:

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

我究竟做錯了什麼?


I like sehe's answer, but it does not use deferred execution (the input sequences are eagerly enumerated by the calls to ToLookup). So after looking at the .NET sources for LINQ-to-objects , I came up with this:

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);
    }
}

This implementation has the following important properties:

  • Deferred execution, input sequences will not be enumerated before the output sequence is enumerated.
  • Only enumerates the input sequences once each.
  • Preserves order of input sequences, in the sense that it will yield tuples in the order of the left sequence and then the right (for the keys not present in left sequence).

These properties are important, because they are what someone new to FullOuterJoin but experienced with LINQ will expect.


I've written this extensions class for an app perhaps 6 years ago, and have been using it ever since in many solutions without issues. 希望能幫助到你。

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)
                    ));

    }

}

對兩個輸入執行內存中的流式枚舉並為每一行調用選擇器。 如果在當前迭代中沒有相關性,則其中一個選擇器參數將為空

例:

   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;
                }
            }
    
        }
    
    }
    

我在猜測@ sehe的方法更強大,但直到我對它有更好的理解之前,我發現自己已經跨越了@ MichaelSander的擴展。 我對其進行了修改以匹配here所述的內置Enumerable.Join()方法的語法和返回類型。 我在@ JeffMercado的解決方案中對@ cadrell0的評論附加了“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()”方法的語法匹配的重載非常重要,原因有二:

  1. 方法的一致性有助於節省時間,避免錯誤並避免意外行為。
  2. 如果將來有一個開箱即用的“.FullJoin()”方法,我會想像它會嘗試保留現有的“.Join()”方法的語法(如果可以的話)。 如果是這樣,那麼如果你想遷移到它,你可以簡單地重命名你的函數而不用改變參數或者擔心不同的返回類型會破壞你的代碼。

我仍然對泛型,擴展,Func語句和其他功能有所了解,所以反饋肯定是受歡迎的。

編輯:沒有花我很長時間才意識到我的代碼存在問題。 我在LINQPad中做了一個.Dump()並查看了返回類型。 它只是IEnumerable,所以我試圖匹配它。 但是當我在我的擴展中實際上做了一個.Where()或.Select()時,我得到一個錯誤:''System Collections.IEnumerable'不包含'Select'和...'的定義。 所以最終我能夠匹配.Join()的輸入語法,但不能返回行為。

編輯:添加“TResult”到函數的返回類型。 錯過了閱讀微軟文章時,當然這是有道理的。 有了這個修復程序,現在看起來返回行為畢竟符合我的目標。


我決定添加這個作為一個單獨的答案,因為我不積極,它已經過足夠的測試。 這是FullOuterJoin方法的重新實現,它使用本質上簡化的LINQKit Invoke / Expand for 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));
        }
    }
}

正如你發現的那樣,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)));

這是一個擴展方法:

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));
}






full-outer-join