首頁猿問 Linq-完全外連接

Linq-完全外連接

C# .NET

守著一只汪 2019-06-14 15:25:51

Linq-完全外連接我有一個人的身份證和他們的名字的名單，和一個人的身份證和他們的姓氏的名單。有些人沒有名字，有些人沒有姓；我想在這兩個列表上做一個完整的外部連接。因此，以下清單：ID FirstName-- --------- 1 John 2 SueID LastName-- -------- 1 Doe 3 Smith應產(chǎn)生：ID FirstName LastName-- --------- -------- 1 John Doe 2 Sue 3 Smith我是LINQ的新手(所以，如果我是跛腳的話，請原諒我)，并且找到了很多“LINQ外部連接”的解決方案，這些解決方案看起來都很相似，但是看起來確實是外部聯(lián)接。到目前為止，我的嘗試都是這樣的：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我做錯什么了？

查看完整描述

3 回答

守候你守候我

TA貢獻1802條經(jīng)驗獲得超10個贊

我認為其中大多數(shù)都存在問題，包括公認的答案，因為它們在IQueryable上與Linq的工作不太好，這要么是因為執(zhí)行了太多的服務器往返和太多的數(shù)據(jù)返回，要么是因為執(zhí)行了太多的客戶端執(zhí)行。

對于IEnumerable，我不喜歡Sehe的答案或類似的內容，因為它占用了過多的內存(一個簡單的10000000雙列表測試在我的32 GB機器上運行Linqpad)。

另外，其他大多數(shù)并不實際實現(xiàn)正確的完全外部聯(lián)接，因為它們使用的是帶有右聯(lián)接的UNION，而不是與右反半連接的UNION，這不僅從結果中消除了重復的內部聯(lián)接行，而且消除了原來在左或右數(shù)據(jù)中存在的任何正確的重復。

下面是我的擴展，它可以處理所有這些問題，生成SQL，就像在Linq中直接實現(xiàn)聯(lián)接一樣，在服務器上執(zhí)行，并且比在EnDigable上的其他擴展更快，內存更少：

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

右反半連接之間的區(qū)別主要與linq在對象或源中的區(qū)別有關，但在最終答案中在server(Sql)端產(chǎn)生了差異，從而刪除了不必要的內容。JOIN.

手編碼Expression若要處理合并Expression<Func<>>可以使用LinqKit對lambda進行改進，但是如果語言/編譯器為此添加了一些幫助，那就太好了。這個FullOuterJoinDistinct和RightOuterJoin函數(shù)是為了完整性而包含的，但我沒有重新實現(xiàn)。FullOuterGroupJoin現(xiàn)在還沒有。

我寫另一個版本一個完全的外部連接IEnumerable對于鍵是可排序的情況下，這是大約50%的速度比組合左外部連接和右反半連接，至少在小集合。它只在排序一次之后就會遍歷每個集合。

反對回復 2019-06-14